Materialize: Support pg_catalog

[umanwizard]

Certain psql macros generate queries that rely on pg_catalog existing; see for example #2156

[cuongdo]

Also, various Postgres-compatible tools such as BI software (e.g. #2206) and ORMs require either us supporting pg_catalog or writing an adapter that removes the need for that.

[rjnn]

@justinj I'd be curious as to your opinion on how much work this is.

[justinj]

IME there's a very long tail of random features supported here. Personally I think the right move is to introduce some carefully-thought-out infrastructure for adding new system tables (unsure if Materialize already supports these) that can easily be added to as tools complain, since doing _everything_ is going to be a ton of work for very little incremental benefit.

[rjnn]

To elaborate on Justin's excellent comment, the way to go about pg_catalog support is to start with a couple _end points_ that we know require pg_catalog, and aim to get support for those libraries.

Priority in picking library should start with enumerating:

1. Libraries that are open source, and have very good test suites. It helps us to have a clear burndown list, and have a concrete win at the end.
2. Libraries that don't need additional functionality that we don't provide. (E.g. if a library primarily is used to do GIS stuff it's not very useful as we don't have GIS support yet)
3. Libraries that our users have asked for/would find useful.
4. Libraries that are "adjacent" to other libraries.

I think Metabase is a great first start here - users want it, lots of the work perhaps overlaps with other JDBC-based tools? I think getdbt.com is a good one, but is gated on CTEs - but it has excellent tests. Postgrest would be useful as well as a third one. cc @awang to ultimately decide on the end points here.

[rjnn]

\l, \d, and the other macros that @umanwizard mentioned are obvious ones as well.

[benesch]

Game plan:

1. Mirror show data as views (#913).

   • Start with just one SHOW command, possibly SHOW DATABASES.
   • Add mz_catalog.mz_databases as a built-in table.
   • Teach coordinator to automatically insert into the databases table whenever a new database is created or dropped.
   • Teach sql planner to rewrite SHOW DATABASES to SELECT * FROM mz_databases.
   • Repeat for other SHOW commands.

2. Start adding some tests of the JDBC metadata API to the Java smoke test. Add builtin pg_catalog views that are transformations over mz_catalog tables. A good place to start is probably getSchemas.

3. Implement enough of pg_catalog for Metabase (#3727) by repeating 2.

At some point:

• Add basic support for arrays. Many pg_catalog tables rely on arrays (not lists).

[benesch]

1. Make \l work (https://github.com/MaterializeInc/materialize/issues/2156#issuecomment-696918120)

[cirego]

Do we have an open issue for making \d <table_name> work? For example, this works in Postgres:

postgres=# create table foo (one INT, two INT);
postgres=# \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | foo  | table | postgres
(1 row)
postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | foo  | table | postgres
(1 row)
postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 one    | integer |           |          | 
 two    | integer |           |          | 

However, in Materialize, I get (where customer is a materialized view):

materialize=> \d customer
ERROR:  Expected end of statement, found identifier
LINE 6: WHERE c.relname OPERATOR(pg_catalog.~) '^(customer)$'