pg_catalog support and the standard psql client

[mjgarton]

I noticed that pg_catalog support exists and is improving.

Is there any plan to make it work well with the standard psql client? For example, being able to display table definitions via \dt does not currently work.

If this is a goal but simply missing at the moment, I'd be happy to try and help work towards that goal.

[sunng87]

hello @mjgarton , I think our next milestone with pgwire ecosystem is the pg_catalog support, in other words, to support all those complex queries psql, dbeaver, pgadmin and various BI tools sent to our server upon connection. Recently I discovered a promising project from @ybrs, https://github.com/ybrs/pg_catalog which already get most of the tools working with pgwire. My plan is to join force with @ybrs, also invite active contributors of pgwire ecosystem, to work together on this topic. Also I planned to setup a mailing list for pgwire in case that some key developer are not watching all the repositories.

But I'm just too busy with our own startup and I haven't got time to reply @ybrs' mail... I'm going to travel back to China next week and expect to invite you to join the mailing list we can plan it.

[mjgarton]

See also #112

[sunng87]

\dt should be supported via #114 . But filter is still not work. It seems be to be with sqlparser:

datafusion=> \dt club*
ERROR:  This feature is not implemented: Unsupported ast node in sqltorel: Collate { expr: Value(ValueWithSpan { value: SingleQuotedString("^(club.*)$"), span: Span(Location(9,40)..Location(9,52)) }), collation: ObjectName([Identifier(Ident { value: "pg_catalog", quote_style: None, span: Span(Location(9,61)..Location(9,71)) }), Identifier(Ident { value: "default", quote_style: None, span: Span(Location(9,72)..Location(9,79)) })]) }

[iPeluwa]

hello @mjgarton , I think our next milestone with pgwire ecosystem is the pg_catalog support, in other words, to support all those complex queries psql, dbeaver, pgadmin and various BI tools sent to our server upon connection. Recently I discovered a promising project from @ybrs, https://github.com/ybrs/pg_catalog which already get most of the tools working with pgwire. My plan is to join force with @ybrs, also invite active contributors of pgwire ecosystem, to work together on this topic. Also I planned to setup a mailing list for pgwire in case that some key developer are not watching all the repositories.

But I'm just too busy with our own startup and I haven't got time to reply @ybrs' mail... I'm going to travel back to China next week and expect to invite you to join the mailing list we can plan it.

Started looking into this myself and forked the repo, Would love to get up to speed on the plan otherwise I have some ideas on what to implement already.

[sunng87]

@iPeluwa if you look at my recent patch, I build a oid cache for PgCatalogSchemaProvider so queries return consistent oid. This is required for \dt support. If you have time, my suggestion is to expand this mechanism to pg_type and pg_description tables.

There are 64 tables totally in pg_catalog as of 18beta1, which you can query with select relname, reltype, relkind from pg_catalog.pg_class where relnamespace = 11 and relkind = 'r';. I'm still not sure if we want to implement all of them as TableProvider. In https://github.com/ybrs/pg_catalog, @ybrs uses exported data which is a good idea. I'm also thinking of a hybrid approach: for static data, like pg_type we can use exported data, for dynamic like pg_class, we should continue with current TableProvider` implementation.

Let me know what do you think.

[iPeluwa]

Hi @sunng87,

Thank you for the update and for sharing your recent work on the OID cache for PgCatalogSchemaProvider—great progress on enabling \dt support! I appreciate the suggestion to expand this mechanism to pg_type and pg_description tables. I agree that a hybrid approach makes sense: using exported data for static tables like pg_type and sticking with the current TableProvider implementation for dynamic ones like pg_class seems like a solid strategy.

I’ve taken a look at @ybrs’s pg_catalog project, and it looks promising for getting tools like psql, DBeaver, and pgAdmin working smoothly with pgwire. I’m excited about the potential to collaborate with @ybrs and other contributors in the pgwire ecosystem to push this forward.

Regarding the 64 tables in pg_catalog (from PostgreSQL 18beta1), I think prioritizing key tables for common client interactions would be a good starting point, rather than implementing all of them upfront. We could identify which tables are most critical for tools like psql and BI clients based on their typical connection queries. What are your thoughts on which tables to prioritize next after pg_type and pg_description?

[iPeluwa]

@sunng87 ybrs/pg_catalog#148 (comment)

[sunng87]

@iPeluwa Saw you have already submitted a complete version for pg_catalog. If you are still going to implement them in this repo, using the TableProvider approach, I would suggest to start from 1. dynamic tables that provides information related to SessionContext, especially the table/column information; 2. tables queried by popular database tools like dbeaver, pgcli or vscode.

We had an analysis in GreptimeDB repo about queries sent from dbeaver: GreptimeTeam/greptimedb#5271 (comment)

[sunng87]

We will also be blocked by this issue apache/datafusion#13489 . The postgres ecosystem uses a lot of such type of query select a, b, * from t. @ybrs has a rewrite mechanism for this and I believe it's an approachable solution until we push the support to datafusion.

[iPeluwa]

I have taken a look, been little occupied but I have some time to get this done now

[mjgarton]

I noticed that in recent versions \dt now works! Next I tried \d mytable and noticed that this does not work.

The query looks something like this:

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.~) '^(mytable)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

I believe there are two reasons that this fails:

1. COLLATE
2. OPERATOR(pg_catalog.~)

I plan to look into these when I have time.

[mjgarton]

I wonder whether supporting the OPERATOR regex be as simple as this mjgarton/datafusion@fde6e87

It seems to do roughly the right thing when I gave it a very quick test. I should look a bit deeper before I create a PR.

[sunng87]

@mjgarton let's see if this can be accepted by upstream. note that you cannot use hard-coded pg_catalog in datafusion

[sunng87]

@mjgarton @iPeluwa in #133 I just adopted @ybrs approach who exported all pg_catalog tables to load them into datafusion. I think with this patch we can much closer to a compatible pg_catalog at data level.