sql: pg_attribute does not report stored columns on indexes

[knz]

Needed for #88061

Describe the problem

CockroachDB does not include stored columns in pg_attribute

To Reproduce

Initialize with:

create table mytable(x int, y int);
create index myidx on mytable(x) include(y);

Then run \d myidx using psql, on both PostgreSQL and CockroachDb.

In pg, the (correct) output is:

         Index "public.myidx"
 Column |  Type   | Key? | Definition
--------+---------+------+------------
 x      | integer | yes  | x
 y      | integer | no   | y

In CockroachDB:

  Column |  Type  | Key? | Definition
---------+--------+------+-------------
  x      | bigint | yes  | x

Notice how y is not listed.

Jira issue: CRDB-19691

Epic: CRDB-23454

[rafiss]

I'm hitting a different error when I try this:

defaultdb=> \d myidx
ERROR:  at or near ".": syntax error
DETAIL:  source SQL:
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(myidx)$' COLLATE pg_catalog.default
                                                                     ^

Due to #57255 and #50734

[knz]

Sorry I am using a simplified version of \d and I was assuming psql would send something equivalent.

Here's the problem query (replace the 3 occurrences of XXXXX by the index OID):

 WITH obj AS (
   SELECT c.oid, c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
          c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity,
          false AS relhasoids, c.relispartition, c.reltablespace,
          CASE
          WHEN c.reloftype = 0 THEN ''
          ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END AS reloftype,
          c.relpersistence, c.relreplident, am.amname
     FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am    ON (c.relam = am.oid)
    WHERE c.oid = XXXXXXXX), cols AS (
 SELECT a.attname,
        pg_catalog.format_type(a.atttypid, a.atttypmod) AS typname,
 CASE WHEN a.attnum <= (
  SELECT i.indnkeyatts
    FROM pg_catalog.pg_index i
   WHERE i.indexrelid = XXXXXXXX) THEN 'yes' ELSE 'no' END AS is_key,
         pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef
    FROM pg_catalog.pg_attribute a
   WHERE a.attrelid = XXXXXX AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum)
SELECT attname AS "Column",
       typname AS "Type",
       is_key AS "Key?",
       indexdef AS "Definition"
  FROM cols

[rafiss]

Another thing I noticed while investigating this is that PG supports select 'myidx'::regclass::oid;, but CRDB does not.

[rafiss]

The problem here is with the pg_attribute table. This should return two rows:

root@localhost:26257/defaultdb> select attnum, attname from pg_attribute where attrelid = XXXXX; -- replace with index OID
  attnum | attname
---------+----------
       1 | x
(1 row)

Also, @knz : what is the purpose of the obj CTE in your example? It doesn't get referenced anywhere.

[knz]

what is the purpose of the obj CTE in your example?

I was mechanically translating what psql does. I'm not sure. I'll look at what the purpose was in psql.