sql: column indnkeyatts in pg_index does not report key columns properly in primary key

[knz]

Needed for #88061.

Describe the problem

CockroachDB does not include primary key columns in the indnkeyatts attribute of pg_index.

To Reproduce

Initialize with:

create table mytable(x int, y int);

Then use the following query (replace the 3 occurrences of XXXX by mytables' primary 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 = XXXXXXX), 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 = XXXXXXX) 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 = XXXXXXX 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

(observe, the column rowid is not marked as Key because indnkeyatts is zero)

cc @rafiss for triage.

Jira issue: CRDB-19690

Epic: CRDB-23454

[rafiss]

Is something missing from the the description? The steps show \d myidx yet myidx was not created.

My guess here is that this problem is specific to the hidden rowid column. If the table is created with an explicit PK, does it still have issues?

[knz]

yes, this description was bogus. My apologies. Corrected.

[rafiss]

I think the statement "indnkeyatts is zero" is not accurate.

root@localhost:26257/defaultdb> create table mytable(x int, y int);
CREATE TABLE

root@localhost:26257/defaultdb> select i.indexrelid, i.indrelid, c.relname from pg_index i join pg_class c on c.oid = i.indrelid where c.relname = 'mytable';
  indexrelid | indrelid | relname
-------------+----------+----------
  2083071188 |      104 | mytable
(1 row)

root@localhost:26257/defaultdb> SELECT i.indnkeyatts                                                                                                                FROM pg_catalog.pg_index i                                                                                                                                     WHERE i.indexrelid = 2083071188;
  indnkeyatts
---------------
            1
(1 row)

The problem seems to actually be that the query has some assumptions on a.attnum that do not hold. Specifically this part:

 CASE WHEN a.attnum <= (
  SELECT i.indnkeyatts
    FROM pg_catalog.pg_index i
   WHERE i.indexrelid = 2083071188) THEN 'yes' ELSE 'no' END AS is_key,

If we change that a bit, we can see:

root@localhost:26257/defaultdb> SELECT
  a.attname,
  a.attnum,
  pg_catalog.format_type(a.atttypid, a.atttypmod) AS typname,
  CASE
  WHEN a.attnum
  <= (
      SELECT
        i.indnkeyatts
      FROM
        pg_catalog.pg_index AS i
      WHERE
        i.indexrelid = 2083071188
    )
  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 AS a
WHERE
  a.attrelid = 2083071188 AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY
  a.attnum;
  attname | attnum | typname | is_key | indexdef
----------+--------+---------+--------+-----------
  rowid   |      3 | bigint  | no     | y
(1 row)

The attnum of rowid is 3, but the query only marks it as a key if attnum <= indnkeyatts

[rafiss]

A more reliable query would use pg_index.indkey

indkey int2vector (references pg_attribute.attnum)

This is an array of indnatts values that indicate which table columns this index indexes. For example, a value of 1 3 would mean that the first and the third table columns make up the index entries. Key columns come before non-key (included) columns. A zero in this array indicates that the corresponding index attribute is an expression over the table columns, rather than a simple column reference.

[rafiss]

What I said above is true, but now I do think it's a bug that in the primary index the attnum of rowid is 3. Specifically:

root@localhost:26257/defaultdb> create table mytable(x int, y int);
CREATE TABLE

root@localhost:26257/defaultdb> select i.indexrelid, i.indrelid, c.relname from pg_index i join pg_class c on c.oid = i.indrelid where c.relname = 'mytable';
  indexrelid | indrelid | relname
-------------+----------+----------
  2083071188 |      104 | mytable
(1 row)

# This is fine.
root@localhost:26257/defaultdb> select attname, attnum from pg_attribute where attrelid = 104;
  attname | attnum
----------+---------
  x       |      1
  y       |      2
  rowid   |      3
(3 rows)

# This is buggy.
root@localhost:26257/defaultdb> select attname, attnum from pg_attribute where attrelid = 2083071188;
  attname | attnum
----------+---------
  rowid   |      3
(1 row)

[rafiss]

Here is a repro that we can compare directly to Postgres to see the bug:

Postgres behavior:

postgres=# create table mytable(x int, y int primary key);
CREATE TABLE

postgres=# select i.indexrelid, c.relname from pg_index i join pg_class c on c.oid = i.indrelid where c.relname = 'mytable';;
 indexrelid | relname
------------+---------
      42597 | mytable
(1 row)

postgres=# select attname, attnum from pg_attribute where attrelid = 42597; -- this ID is from the previous query.
 attname | attnum
---------+--------
 y       |      1
(1 row)

CockroachDB behavior (buggy):

root@localhost:26257/defaultdb> create table mytable(x int, y int primary key);
CREATE TABLE

root@localhost:26257/defaultdb> select i.indexrelid,  c.relname from pg_index i join pg_class c on c.oid = i.indrelid where c.relname = 'mytable';
  indexrelid | relname
-------------+----------
   478257910 | mytable
(1 row)

root@localhost:26257/defaultdb> select attname, attnum from pg_attribute where attrelid = 478257910; -- this ID is from the previous query.
  attname | attnum
----------+---------
  y       |      2
(1 row)