Update on pg_catalog Support in Doltgres

July 2, 2024

5 min read

It's our goal to make Doltgres, a Postgres-flavored version of Dolt, a drop-in replacement for Postgres. A few weeks ago Zach wrote a blog about our design dilemma for implementing the pg_catalog schema: should we diverge from Postgres and make it read-only? After some internal debate and feedback from Redditors, we have since decided to follow Cockroach DB's footsteps and make pg_catalog read-only. Manually editing system tables seems not very common and bad practice anyway.

Since then, we've been making progress implementing pg_catalog features, starting with the ones we found by testing popular SQL workbenches and ORMs. This blog gives an update of our progress.

What is the `pg_catalog` schema?

In Postgres, metadata about the tables, functions, and other schema elements are exposed via the system catalogs, accessed through the pg_catalog schema in each database. Here are a few examples:

pg_class: contains information about the relations (tables, indexes, etc.) in the database
pg_enum: contains enum label and value definitions
pg_proc: contains function and procedure definitions

Additionally, there are tables that expose and configure the server's behavior, like users and their permission to access particular tables, such as the pg_authid table.

Here's an example of querying these tables.

db1=# select oid, relname from pg_class;
    oid  |                    relname
  -------+------------------------------------------------
   61501 | test
   61504 | test_pkey
   61506 | test2
   61509 | test2_pkey
    2619 | pg_statistic
    1247 | pg_type
   62229 | test
   62232 | test_pkey
   62235 | test
   62238 | test_pkey
    2836 | pg_toast_1255
    2837 | pg_toast_1255_index

The pg_catalog schema is used in many workbenches, including the Dolt Workbench, making it a vital feature to Doltgres's usability as a database.

Current `pg_catalog` status

We have been making steady progress implementing pg_catalog. Here's an overview of our progress as of Doltgres v0.10.0. See the appendix for a more detailed status of pg_catalog tables and views.

Implemented an interface for exposing pg_catalog system tables as virtual tables.
Added schemas for all pg_catalog tables and views so that they will at least parse with no rows.
Implemented a temporary OID system using an on-demand hashing strategy using the name of the schema element.
Populated partial columns and rows for some tables (pg_attribute, pg_class, pg_constraint, pg_database, pg_index, pg_sequence, pg_type) and views (pg_indexes, pg_tables, pg_views) we know some popular SQL workbenches utilize.

Conclusion

You'll notice there's a lot of red below, but we're just getting started with Doltgres and pg_catalog support. Let us know if you have any pg_catalog features you want us to prioritize by filing an issue or reaching out on Discord. Stay tuned!

Appendix

Here's a list of all pg_catalog tables and views and their current status as of Doltgres v0.10.0. We will update this list in our docs as we continue to make progress.

These tables show:

✅ Full support for the feature.
🟠 Partial support for the feature.
❌ No support for the feature.

Tables

Table	Parses	Populated
pg_aggregate	✅	❌
pg_am	✅	❌
pg_amop	✅	❌
pg_amproc	✅	❌
pg_attrdef	✅	❌
pg_attribute	✅	🟠
pg_auth_members	✅	❌
pg_authid	✅	❌
pg_cast	✅	❌
pg_class	✅	🟠
pg_collation	✅	❌
pg_constraint	✅	🟠
pg_conversion	✅	❌
pg_database	✅	🟠
pg_db_role_setting	✅	❌
pg_default_acl	✅	❌
pg_depend	✅	❌
pg_description	✅	❌
pg_enum	✅	❌
pg_event_trigger	✅	❌
pg_extension	✅	❌
pg_foreign_data_wrapper	✅	❌
pg_foreign_server	✅	❌
pg_foreign_table	✅	❌
pg_index	✅	🟠
pg_inherits	✅	❌
pg_init_privs	✅	❌
pg_language	✅	❌
pg_largeobject	✅	❌
pg_largeobject_metadata	✅	❌
pg_namespace	✅	🟠
pg_opclass	✅	❌
pg_opfamily	✅	❌
pg_parameter_acl	✅	❌
pg_partitioned_table	✅	❌
pg_policy	✅	❌
pg_proc	✅	❌
pg_publication	✅	❌
pg_publication_namespace	✅	❌
pg_publication_rel	✅	❌
pg_range	✅	❌
pg_replication_origin	✅	❌
pg_rewrite	✅	❌
pg_seclabel	✅	❌
pg_sequence	✅	✅
pg_shdepend	✅	❌
pg_shdescription	✅	❌
pg_shseclabel	✅	❌
pg_statistic	✅	❌
pg_statistic_ext	✅	❌
pg_statistic_ext_data	✅	❌
pg_subscription	✅	❌
pg_subscription_rel	✅	❌
pg_tablespace	✅	❌
pg_transform	✅	❌
pg_trigger	✅	❌
pg_ts_config	✅	❌
pg_ts_config_map	✅	❌
pg_ts_dict	✅	❌
pg_ts_parser	✅	❌
pg_ts_template	✅	❌
pg_type	✅	🟠
pg_user_mapping	✅	❌

Views

View	Parses	Populated
pg_available_extension_versions	✅	❌
pg_available_extensions	✅	❌
pg_backend_memory_contexts	✅	❌
pg_config	✅	❌
pg_cursors	✅	❌
pg_file_settings	✅	❌
pg_group	✅	❌
pg_hba_file_rules	✅	❌
pg_ident_file_mappings	✅	❌
pg_indexes	✅	🟠
pg_locks	✅	❌
pg_matviews	✅	❌
pg_policies	✅	❌
pg_prepared_statements	✅	❌
pg_prepared_xacts	✅	❌
pg_publication_tables	✅	❌
pg_replication_origin_status	✅	❌
pg_replication_slots	✅	❌
pg_roles	✅	❌
pg_rules	✅	❌
pg_seclabels	✅	❌
pg_sequences	✅	❌
pg_settings	✅	❌
pg_shadow	✅	❌
pg_shmem_allocations	✅	❌
pg_stat_activity	✅	❌
pg_stat_all_indexes	✅	❌
pg_stat_all_tables	✅	❌
pg_stat_archiver	✅	❌
pg_stat_bgwriter	✅	❌
pg_stat_database	✅	❌
pg_stat_database_conflicts	✅	❌
pg_stat_gssapi	✅	❌
pg_stat_io	✅	❌
pg_stat_progress_analyze	✅	❌
pg_stat_progress_basebackup	✅	❌
pg_stat_progress_cluster	✅	❌
pg_stat_progress_copy	✅	❌
pg_stat_progress_create_index	✅	❌
pg_stat_progress_vacuum	✅	❌
pg_stat_recovery_prefetch	✅	❌
pg_stat_replication	✅	❌
pg_stat_replication_slots	✅	❌
pg_stat_slru	✅	❌
pg_stat_ssl	✅	❌
pg_stat_subscription	✅	❌
pg_stat_subscription_stats	✅	❌
pg_stat_sys_indexes	✅	❌
pg_stat_sys_tables	✅	❌
pg_stat_user_functions	✅	❌
pg_stat_user_indexes	✅	❌
pg_stat_user_tables	✅	❌
pg_stat_wal	✅	❌
pg_stat_wal_receiver	✅	❌
pg_stat_xact_all_tables	✅	❌
pg_stat_xact_sys_tables	✅	❌
pg_stat_xact_user_functions	✅	❌
pg_stat_xact_user_tables	✅	❌
pg_statio_all_indexes	✅	❌
pg_statio_all_sequences	✅	❌
pg_statio_all_tables	✅	❌
pg_statio_sys_indexes	✅	❌
pg_statio_sys_sequences	✅	❌
pg_statio_sys_tables	✅	❌
pg_statio_user_indexes	✅	❌
pg_statio_user_sequences	✅	❌
pg_statio_user_tables	✅	❌
pg_stats	✅	❌
pg_stats_ext	✅	❌
pg_stats_ext_exprs	✅	❌
pg_tables	✅	🟠
pg_timezone_abbrevs	✅	❌
pg_timezone_names	✅	❌
pg_user	✅	❌
pg_user_mappings	✅	❌
pg_views	✅	🟠

Blog

What is the pg_catalog schema?

Current pg_catalog status