Feature Request: Support system columns

[dpxcc]

System columns, such as ctid, are currently not supported by pg_duckdb: https://www.postgresql.org/docs/current/ddl-system-columns.html

SET duckdb.force_execution = ON;
CREATE TABLE t (a int);
SELECT ctid, a FROM t;

throws

WARNING:  (PGDuckDB/CreatePlan) Prepared query returned an error: 'Binder Error: Referenced column "ctid" not found in FROM clause!
Candidate bindings: "t.a"
LINE 1: SELECT ctid, a FROM pgduckdb.public.t

but it could be an error if we added any DuckDB-only functions to the query

[JelteF]

I think it should be possible to support these, but I don't think it would be trivial to do so. Why do you think this support would be useful? As always: PRs are welcome.

[dpxcc]

Thanks for looking into it!

I think system columns can be useful in certain cases. For example, people sometimes uses ctid as a temporary primary key. It's the only plausible way to delete duplicate rows without unique id: https://stackoverflow.com/a/26773018

As for implementation, are these system columns already populated in the HeapTuple prepared by HeapReader? If so, would it be possible to have PostgresTable::SetTableInfo() add these additional system columns to make them accessible? I haven’t looked into the specifics yet, so I could be off base here

[zhang-wenchao]

This bug was reported by datagrip, a very popular client.

com.intellij.database.remote.jdbc.SQLExceptionWithProperties: ERROR: (PGDuckDB/CreatePlan) Prepared query returned an error: 'Binder Error: Referenced column "ctid" not found in FROM clause! Candidate bindings: "created_at", "org_id", "actor_id", "actor_login", "body"  LINE 1: ..., org_login, event_type, payload, body, public, created_at, ctid FROM pgduckdb.web3.event t LIMIT 101 ^ at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:341) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:326) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:302) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:297) in RemoteStatementImpl.execute(RemoteStatementImpl.java:79)