User-defined composite types are not introspectable

[plcplc]

Describe the problem

Composite types defined via CREATE TYPE are only mentioned in pg_catalog.pg_type.

I would have expected to be able to find:

• A row in pg_catalog.pg_type for the type, having typtype = 'c' and linking to pg_class via typrelid
• A row in pg_catalog.pg_class for the type, having relkind = 'c'
• Rows in pg_catalog.pg_attribute for each column/field of the type, linking to the row in pg_class via attrelid

To Reproduce

(See below)

Expected behavior

postgres=# CREATE TYPE person_name AS
  (
    first_name text,
    last_name text
  );
CREATE TYPE

postgres=# select oid::regtype,typname,typtype,typrelid::regclass from pg_type where oid = 'person_name'::regtype;
     oid     |   typname   | typtype |  typrelid
-------------+-------------+---------+-------------
 person_name | person_name | c       | person_name
(1 row)

postgres=# select oid::regclass,relname,relkind from pg_class where oid = 'person_name'::regclass;
     oid     |   relname   | relkind
-------------+-------------+---------
 person_name | person_name | c
(1 row)

postgres=# select attrelid::regclass, attname, atttypid::regtype, attnum from pg_attribute where attrelid = 'person_name'::regclass;  attrelid   |  attname   | atttypid | attnum
-------------+------------+----------+--------
 person_name | first_name | text     |      1
 person_name | last_name  | text     |      2
(2 rows)

Environment:

defaultdb=> select version();
                                       version
--------------------------------------------------------------------------------------
 CockroachDB CCL v23.1.10 (x86_64-pc-linux-gnu, built 2023/09/14 14:09:38, go1.19.10)
(1 row)

Additional context

Jira issue: CRDB-37102

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: pg_)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rafiss]

This seems like a duplicate of #109675. We have fixed that issue in #111179, but that fix is only available in 24.1.0-alpha versions right now.

I tried the example you provided, and it does pass on the v24.1.0-alpha.3. The metadata also appears in the tables you described:

root@localhost:26257/defaultdb> select oid, typname, typtype, typrelid from pg_catalog.pg_type where typname = 'person_name';
   oid   |   typname   | typtype | typrelid
---------+-------------+---------+-----------
  100108 | person_name | c       |   100108
(1 row)

Time: 13ms total (execution 12ms / network 1ms)

root@localhost:26257/defaultdb> select oid, relname, relkind, reltype from pg_catalog.pg_class where relname = 'person_name';
   oid   |   relname   | relkind | reltype
---------+-------------+---------+----------
  100108 | person_name | c       |  100108

Time: 14ms total (execution 14ms / network 0ms)

root@localhost:26257/defaultdb> select attrelid, attname from pg_catalog.pg_attribute where attrelid = 100108;
  attrelid |  attname
-----------+-------------
    100108 | first_name
    100108 | last_name
(2 rows)

Time: 2ms total (execution 1ms / network 0ms)

[plcplc]

Best news I could hope for! Thanks :-)

Sorry I didn't manage to find that issue earlier.