Blog

PRODUCTS

KEYWORDS

Update on pg_catalog Support in Doltgres

Taylor Bantle
Taylor Bantle
9 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#

TableParsesPopulated
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#

ViewParsesPopulated
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