Can't get current value of sequence

[kampde]

Version

1.13.0

What happened?

When trying to obtain the last value used in a sequence by using SELECT last_value FROM my_sequence the generate step fails complaining that the relation my_sequence does not exist. That kind of query is documented in the CREATE SEQUENCE documentation for postgres:

Although you cannot update a sequence directly, you can use a query like:

SELECT * FROM name;

to examine the parameters and current state of a sequence. In particular, the last_value field of the sequence shows the last value allocated by any session. (Of course, this value might be obsolete by the time it's printed, if other sessions are actively doing nextval calls.)

Using SELECT currval('my_sequence') does not work for me because it returns the last value for that sequence in the current session (see documentation).

The issue seems to be that the sqlc parser identifies my_sequence as a table in the SELECT last_value FROM my_sequence but does not find such table in the schema (internally it seems that sequences are special one-row tables).

Relevant log output

sqlc generate failed.
# package db
query.sql:11:1: relation "my_sequence" does not exist

Database schema

CREATE SEQUENCE public.my_sequence
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

SQL queries

-- name: GetLastValue :one
SELECT last_value FROM my_sequence;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "postgresql",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/75805a902ff7fa9eaa6241d9fb4cf48f4cd809184d2ff39d4adec0a3412b69e8

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[andrewmbenton]

This also doesn't currently (v1.19.1) work for automatically-generated sequences on SERIAL columns in PostgreSQL: https://play.sqlc.dev/p/7f83f624864ebdf01d7a007466b3b815f9d7afdc6227392b0f8800032c8808f1

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground