Enable a `pgcompat` mode for alternative PG compatible engines

[khepin]

Was recently working on trying to get ducklake working on top of Spanner via https://docs.cloud.google.com/spanner/docs/pgadapter and hit an issue where the ATTACH introspection ends up breaking this particular path.

In this case, the issue comes from ATTACH looking at pg_class, pg_attribute, .... Some of the PG compatible databases are "wire compatible" but not necessarily compatible on all such internals.

I've found #373 that points to a similar problem but fortunately for them had an easy resolution.

pgcompat

Instead of building a completely separate extension I was thinking this code could register 2 extensions:

config.storage_extensions["postgres"] = make_uniq<PostgresStorageExtension>();
config.storage_extensions["pgcompat"] = make_uniq<PostgresStorageExtension>(/*compat=*/true);

Where pgcompat would:

• query information_schema instead of pg_catalog
• set pg_use_ctid_scan=false (spanner / cockroach unsupported)
• set pg_use_binary_copy=false
• set pg_use_text_protocol=true

or additional param

Alternatively, since some of those can already be configured on the connection via

SET pg_use_text_protocol = true;

This could just be 1 additional param like

SET pg_use_information_schema_introspection = true;

I'm happy to look into making a PR for either option or refine the options if this is something that makes sense for the extension.

[staticlibs]

Hi, thanks for raising the issue! I think this is a good idea to support as many Postgres-protocol DBs as possible. Just implementation/configuration wise I see it the following way:

• have a separate option for every compat feature, like pg_use_information_schema_introspection above
• have auto-detection logic for known DBs that will set all compat options automatically
• allow users to set any combination of compat options manually

I suspect that such pgcompat won't be one-size-fits-all, and different options are needed to different DBs.

There are large changes are pending for duckdb-postgres related to new queries generation logic, so this compat detection is going to be done after that, intended for 2.0.

Specifically about the information_schema, if you would like to contribute the support for pg_use_information_schema_introspection (to be published for 1.5.4 core_nightly) - the PR will be welcome!

[khepin]

@staticlibs I have a followup question on the topic but for DuckLake in case you know about that side too.

DuckLake outputs some SQL that ends up being incompatible with Spanner in my case. I think it was using CTEs in UPDATE or INSERT statements in that case. Are changes to make that more broadly compatible welcome on the DuckLake side too?
Otherwise I think the current workaround would be to handle all the writes to ducklake's tables myself and only use the duckdb extension on the read side.

[staticlibs]

@khepin

I suggest to file a compat PR to DuckLake. I do not think that a compat SQL change (assuming it is not breaking anything) will meet an opposition there, though I cannot say anything about timely replies on it.

About the question on "enums, composite types and indexes" in #493 - I would think the best will be to have an option, like pg_read_enums_strategy = 'name_of_enums_loading_method'. And do the DB-specific loading behind this option. The idea is to allow it to be overridden by user, even when it is set as a result of auto-detection.

I also filed an 1.5 backport for it (#495) to be included into 1.5.5 update.