{"action":"created","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/33296","repository_url":"https://api.github.com/repos/cockroachdb/cockroach","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/33296/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/33296/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/33296/events","html_url":"https://github.com/cockroachdb/cockroach/issues/33296","id":392980806,"node_id":"MDU6SXNzdWUzOTI5ODA4MDY=","number":33296,"title":"sql: support pg_catalog \"system attributes\" xmin, xmax etc","user":{"login":"knz","id":642886,"node_id":"MDQ6VXNlcjY0Mjg4Ng==","avatar_url":"https://avatars3.githubusercontent.com/u/642886?v=4","gravatar_id":"","url":"https://api.github.com/users/knz","html_url":"https://github.com/knz","followers_url":"https://api.github.com/users/knz/followers","following_url":"https://api.github.com/users/knz/following{/other_user}","gists_url":"https://api.github.com/users/knz/gists{/gist_id}","starred_url":"https://api.github.com/users/knz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/knz/subscriptions","organizations_url":"https://api.github.com/users/knz/orgs","repos_url":"https://api.github.com/users/knz/repos","events_url":"https://api.github.com/users/knz/events{/privacy}","received_events_url":"https://api.github.com/users/knz/received_events","type":"User","site_admin":false},"labels":[{"id":464498682,"node_id":"MDU6TGFiZWw0NjQ0OTg2ODI=","url":"https://api.github.com/repos/cockroachdb/cockroach/labels/A-sql-pgcompat","name":"A-sql-pgcompat","color":"22aa88","default":false},{"id":78928347,"node_id":"MDU6TGFiZWw3ODkyODM0Nw==","url":"https://api.github.com/repos/cockroachdb/cockroach/labels/C-enhancement","name":"C-enhancement","color":"c5def5","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-12-20T10:26:23Z","updated_at":"2018-12-21T01:17:03Z","closed_at":null,"author_association":"MEMBER","body":"It appears that PostgreSQL tables, especially tables in the pg_catalog, all support \"system attributes\".\r\n\r\nA 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).\r\n\r\nSee definitions in pg sources `src/backend/catalog/heap.c`.\r\n\r\nFor example it is possible to do `select X.xmin from X` for every pg_catalog table X.\r\n\r\nOf course it is neither reasonable nor correct to simply add these attributes as regular columns:\r\n- not reasonable because they would cause a lot of data to be instantiated in-memory when the vtables are queried\r\n- not correct because they should be hidden from introspection\r\n\r\nThe following attributes seem to be defined:\r\n\r\n```\r\nstatic FormData_pg_attribute a1 = {\r\n        0, {\"ctid\"}, TIDOID, 0, sizeof(ItemPointerData),\r\n        SelfItemPointerAttributeNumber, 0, -1, -1,\r\n        false, 'p', 's', true, false, false, true, 0\r\n};\r\n\r\nstatic FormData_pg_attribute a2 = {\r\n        0, {\"oid\"}, OIDOID, 0, sizeof(Oid),\r\n        ObjectIdAttributeNumber, 0, -1, -1,\r\n        true, 'p', 'i', true, false, false, true, 0\r\n};\r\n\r\nstatic FormData_pg_attribute a3 = {\r\n        0, {\"xmin\"}, XIDOID, 0, sizeof(TransactionId),\r\n        MinTransactionIdAttributeNumber, 0, -1, -1,\r\n        true, 'p', 'i', true, false, false, true, 0\r\n};\r\n\r\nstatic FormData_pg_attribute a4 = {\r\n        0, {\"cmin\"}, CIDOID, 0, sizeof(CommandId),\r\n        MinCommandIdAttributeNumber, 0, -1, -1,\r\n        true, 'p', 'i', true, false, false, true, 0\r\n};\r\n\r\nstatic FormData_pg_attribute a5 = {\r\n        0, {\"xmax\"}, XIDOID, 0, sizeof(TransactionId),\r\n        MaxTransactionIdAttributeNumber, 0, -1, -1,\r\n        true, 'p', 'i', true, false, false, true, 0\r\n};\r\n\r\nstatic FormData_pg_attribute a6 = {\r\n        0, {\"cmax\"}, CIDOID, 0, sizeof(CommandId),\r\n        MaxCommandIdAttributeNumber, 0, -1, -1,\r\n        true, 'p', 'i', true, false, false, true, 0\r\n};\r\n\r\n/*\r\n * We decided to call this attribute \"tableoid\" rather than say\r\n * \"classoid\" on the basis that in the future there may be more than one\r\n * table of a particular class/type. In any case table is still the word\r\n * used in SQL.\r\n */\r\nstatic FormData_pg_attribute a7 = {\r\n        0, {\"tableoid\"}, OIDOID, 0, sizeof(Oid),\r\n        TableOidAttributeNumber, 0, -1, -1,\r\n        true, 'p', 'i', true, false, false, true, 0\r\n};\r\n\r\nstatic const Form_pg_attribute SysAtt[] = {&a1, &a2, &a3, &a4, &a5, &a6, &a7};\r\n```\r\n\r\nFound while working on  #31737"},"comment":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/comments/449200458","html_url":"https://github.com/cockroachdb/cockroach/issues/33296#issuecomment-449200458","issue_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/33296","id":449200458,"node_id":"MDEyOklzc3VlQ29tbWVudDQ0OTIwMDQ1OA==","user":{"login":"knz","id":642886,"node_id":"MDQ6VXNlcjY0Mjg4Ng==","avatar_url":"https://avatars3.githubusercontent.com/u/642886?v=4","gravatar_id":"","url":"https://api.github.com/users/knz","html_url":"https://github.com/knz","followers_url":"https://api.github.com/users/knz/followers","following_url":"https://api.github.com/users/knz/following{/other_user}","gists_url":"https://api.github.com/users/knz/gists{/gist_id}","starred_url":"https://api.github.com/users/knz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/knz/subscriptions","organizations_url":"https://api.github.com/users/knz/orgs","repos_url":"https://api.github.com/users/knz/repos","events_url":"https://api.github.com/users/knz/events{/privacy}","received_events_url":"https://api.github.com/users/knz/received_events","type":"User","site_admin":false},"created_at":"2018-12-21T01:17:03Z","updated_at":"2018-12-21T01:17:03Z","author_association":"MEMBER","body":"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.\n-- \nSent from my Android device with K-9 Mail. Please excuse my brevity."}}
{"actor":{"display_login":"knz"}}