Postgres system columns not working

[werjo-gridfuse]

Version

1.27.0

What happened?

sqlc currently errors out with column "…" does not exist when referencing a PostgreSQL system column (tableoid, xmin, cmin, xmax, cmax or ctid) in an SQL query.

There was already a pull request that should fix this. And there are tests to make sure this is working. See here 1745

In this thread, there was a comment, that its not working with 1.25. Sadly its also not working with 1.27. And if you change the version to 1.23 in the playground, its also not working.

Relevant log output

query.sql:2:8: column "xmin" does not exist

Database schema

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

SQL queries

SELECT xmin, cmin, xmax, cmax, ctid FROM authors;

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/db9040344439ff1fcf601ba3e93486fca3e1b2c0644fb46e4563bbf81c675116

What operating system are you using?

No response

What database engines are you using?

No response

What type of code are you generating?

No response

[swallowstalker]

after spending some time looking into the problem, I tested it using sql.database.managed set to true, and it's working. turns out that the pg config Analyzer is doing the job of getting pg system columns, and with default config of sql.database.managed set to false, then the pg config Analyzer is not initialized since there is no db to look for system columns. the solution (maybe) is to set list of hardcoded system columns if pg config Analyzer is not set, what do you think?

related docs

example config file

version: "2"
servers:
  - engine: postgresql
    uri: "postgres://user:pass@localhost:5432/dbname?sslmode=disable"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "querytest"
        out: "go"
        sql_package: "pgx/v5"
    database:
      managed: true

[werjo-gridfuse]

I do not know enough about the code architecture of sqlc to suggest something. The managed database feature is not working on my use cases, because the code is generated via a pipeline that has no access to a running database instance.

It is possible to configure something but it would be better if this feature is fully supported by sqlc. I think it's part of the syntax of postgres, so adding the system columns hardcoded seems absolutely fine.