[Meta] DBeaver / Datagrip - PG connector compatibility tracker

[proddata]

This is a meta ticket tracking current state of missing tables, function features for CrateDB to work with the standard PostgreSQL connectors in DBeaver and Datagrip. Both those tools can be used with either a CrateDB connector or using the CrateDB JDBC driver.

---

This list is a WIP and might not be complete

Missing general functionality

• [x] #12107
• [x] #12104
• [x] #12100
• [ ] #12105
• [ ] #12106
• [x] #11757
• [ ] cross join unnest(xxx) with ordinality u(u, k)
• [ ] Error: SELECT ARRAY(SELECT target FROM pg_extension_update_paths(extname) WHERE source = extversion AND path IS NOT NULL) AS available_updates FROM pg_catalog.pg_extension JOIN pg_namespace ON pg_extension.extnamespace = pg_namespace.oid;
• [x] #12512
• [ ] oprnegate::regoper regoper data type missing
• [ ] opcintype::regtype::varchar regtype data type missing
• [ ] O.amopopr::regoperator::varchar regoperator data type missing
• [ ] P.amproc::regprocedure::varchar regprocedure data type missing
• [x] #12648
• [x] #14528

Missing functions

• [ ] #12086 - pg_catalog.txid_current() - PR #12108
• [ ] #12085 - pg_catalog.pg_is_in_recovery() - PR #12108
• [ ] pg_catalog.pg_get_viewdef(t.oid, true)
• [ ] pg_catalog.pg_relation_size(i.indexrelid)
• [ ] pg_tablespace_location(t.oid) - PR #12108
• [x] #12425
• [ ] pg_get_ruledef(r.oid)

Missing pg_catalog tables

• [ ] pg_catalog.pg_aggregate
• [ ] pg_catalog.pg_amop
• [ ] pg_catalog.pg_amproc
• [ ] pg_catalog.pg_available_extensions
• [ ] pg_catalog.pg_auth_members
• [ ] pg_catalog.pg_collation
• [ ] pg_catalog.pg_conversion
• [ ] pg_catalog.pg_event_trigger
• [ ] pg_catalog.pg_extension
• [ ] pg_catalog.pg_foreign_data_wrapper
• [ ] pg_catalog.pg_foreign_server
• [ ] pg_catalog.pg_foreign_table
• [ ] pg_catalog.pg_language
• [ ] pg_catalog.pg_opclass
• [ ] pg_catalog.pg_opfamily
• [ ] pg_catalog.pg_operator
• [ ] pg_catalog.pg_policies
• [ ] pg_catalog.pg_sequence
• [ ] pg_catalog.pg_shdescription - DataGrip
• [ ] pg_catalog.pg_stat_activity
• [ ] pg_catalog.pg_timezone_names
• [ ] pg_catalog.pg_timezone_abbrevs
• [ ] pg_catalog.pg_trigger
• [ ] pg_catalog.pg_user_mapping
• [ ] https://github.com/crate/crate/pull/14953
• [ ] pg_catalog.pg_rewrite
• [ ] pg_catalog.pg_inherits

[hlcianfagna]

Regarding the missing pg_catalog tables, trying to create mocks currently results on "Cannot create relation in read-only schema", perhaps allowing that could be a low-cost way of enabling workarounds?

[hlcianfagna]

Regarding the missing pg_catalog tables, trying to create mocks currently results on "Cannot create relation in read-only schema", perhaps allowing that could be a low-cost way of enabling workarounds?

This can be worked around by creating the mocks in another schema and setting search_path to look in that other schema before pg_catalog