sql: support pg_catalog "system attributes" xmin, xmax etc

[knz]

It appears that PostgreSQL tables, especially tables in the pg_catalog, all support "system attributes".

A system attribute is a virtual column that is selectable in every table, even though it does not show up in its schema description (eg psql \d / SHOW CREATE).

See definitions in pg sources src/backend/catalog/heap.c.

For example it is possible to do select X.xmin from X for every pg_catalog table X.

Of course it is neither reasonable nor correct to simply add these attributes as regular columns:

• not reasonable because they would cause a lot of data to be instantiated in-memory when the vtables are queried
• not correct because they should be hidden from introspection

The following attributes seem to be defined:

• [x] tableoid: the oid of the containing table
• [ ] ctid: the "physical location" of a row within its table
• [ ] xmin: the txn id of the inserting txn
• [ ] xmax: the txn id of the deleting txn or 0
• [ ] cmin: the command id of the inserting command
• [ ] cmax: the command id of the deleting command or 0

Found while working on #31737

Jira issue: CRDB-4698

[jordanlewis]

I made a hack for this a long time ago to see if IntelliJ would work if we at least returned something for xmin, and indeed if my recollection is correct it did fix the problem. However this hack is a very hacky hack and wouldn't work anymore with the optimizer.

diff --git a/pkg/sql/select_name_resolution.go b/pkg/sql/select_name_resolution.go
index 21c877e977..519d720a8d 100644
--- a/pkg/sql/select_name_resolution.go
+++ b/pkg/sql/select_name_resolution.go
@@ -115,6 +115,9 @@ func (v *nameResolutionVisitor) VisitPre(expr tree.Expr) (recurse bool, newNode
                return v.VisitPre(vn)

        case *tree.ColumnItem:
+               if t.ColumnName == "xmin" {
+                       return true, tree.NewDInt(tree.DInt(0))
+               }
                srcIdx, colIdx, err := v.sources.findColumn(t)
                if err != nil {
                        v.err = err

[knz]

Actually the proper implementation is not far from where you did this. It should indeed be in that function or the equivalent code in opt/build/scope.go. however it must kick in as fallback if the column name does not otherwise exist, not upfront. Also we probably want to recognize the other system attributes too. Also the type of the value should be oid not int. But you got the gist right. -- Sent from my Android device with K-9 Mail. Please excuse my brevity.

[jordanlewis]

Here's the doc for the system attributes: https://www.postgresql.org/docs/12/ddl-system-columns.html

[michae2]

xmin is also used by the .NET Entity Framework as a concurrency token.

[knz]

It's unlikely we can implement xmin/xmax in a way that's both compatible with pg and also correct wrt our own concurrency management. For those columns, I would rather go the way of docs for substitute approaches.

[michae2]

Note for posterity: see also https://github.com/cockroachdb/cockroach/issues/7945 and https://github.com/cockroachdb/cockroach/issues/79591

[giangpham712]

lack of support for xmin column is affecting some test in efcore.pg