I saw someone ask once for a schema diagram of the system catalogs.
Things like that have occasionally been produced manually, but they are
not regularly updated. That made me wonder, why can't we add primary
and foreign keys to system catalogs and then have existing tools produce
such a schema diagram automatically?
Since we have ADD PRIMARY KEY USING INDEX, we can declare a primary key
for an existing index. So this doesn't have to affect the low-level
early bootstrapping. The attached patch adds those commands manually.
Another option might be to have the catalog generation Perl tooling
create those declarations automatically from some marker in the catalog
files. That would also allow declaring unique constraints for the
unique indexes that don't end up being the primary key.
I'm not dealing here with how we might do foreign keys between system
catalogs, but I think the idea would be to have some way to declare a
foreign key "on paper" without any actual triggers.
Any thoughts on this direction?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From c9b1ff561dc7db045511a4da3cc87ecaac9c520b Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Sat, 3 Oct 2020 08:27:36 +0200
Subject: [PATCH] Add primary keys to system catalogs
For those system catalogs that have a unique index, make a primary key
constraint, using ALTER TABLE ... PRIMARY KEY USING INDEX.
This can be helpful for GUI tools that look for a primary key, and it
might in the future allow declaring foreign keys, for making schema
diagrams.
System catalogs without a primary key so far: pg_depend, pg_shdepend
don't have a unique key; pg_seclabel, pg_shseclabel use a nondefault
operator class.
---
src/backend/catalog/system_views.sql | 60 +++++++++++++++++++
.../expected/alter_system_table.out | 5 +-
.../unsafe_tests/sql/alter_system_table.sql | 5 +-
src/test/regress/expected/misc_sanity.out | 14 +++++
src/test/regress/sql/misc_sanity.sql | 9 +++
5 files changed, 87 insertions(+), 6 deletions(-)
diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index ed4f3f142d..a1cdaed571 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -14,6 +14,66 @@
* string literal (including a function body!) or a multiline comment.
*/
+ALTER TABLE pg_aggregate ADD PRIMARY KEY USING INDEX pg_aggregate_fnoid_index;
+ALTER TABLE pg_am ADD PRIMARY KEY USING INDEX pg_am_oid_index;
+ALTER TABLE pg_amop ADD PRIMARY KEY USING INDEX pg_amop_oid_index;
+ALTER TABLE pg_amproc ADD PRIMARY KEY USING INDEX pg_amproc_oid_index;
+ALTER TABLE pg_attrdef ADD PRIMARY KEY USING INDEX pg_attrdef_oid_index;
+ALTER TABLE pg_attribute ADD PRIMARY KEY USING INDEX
pg_attribute_relid_attnum_index;
+ALTER TABLE pg_authid ADD PRIMARY KEY USING INDEX pg_authid_oid_index;
+ALTER TABLE pg_auth_members ADD PRIMARY KEY USING INDEX
pg_auth_members_role_member_index;
+ALTER TABLE pg_cast ADD PRIMARY KEY USING INDEX pg_cast_oid_index;
+ALTER TABLE pg_class ADD PRIMARY KEY USING INDEX pg_class_oid_index;
+ALTER TABLE pg_collation ADD PRIMARY KEY USING INDEX pg_collation_oid_index;
+ALTER TABLE pg_constraint ADD PRIMARY KEY USING INDEX pg_constraint_oid_index;
+ALTER TABLE pg_conversion ADD PRIMARY KEY USING INDEX pg_conversion_oid_index;
+ALTER TABLE pg_database ADD PRIMARY KEY USING INDEX pg_database_oid_index;
+ALTER TABLE pg_db_role_setting ADD PRIMARY KEY USING INDEX
pg_db_role_setting_databaseid_rol_index;
+ALTER TABLE pg_default_acl ADD PRIMARY KEY USING INDEX
pg_default_acl_oid_index;
+ALTER TABLE pg_description ADD PRIMARY KEY USING INDEX
pg_description_o_c_o_index;
+ALTER TABLE pg_enum ADD PRIMARY KEY USING INDEX pg_enum_oid_index;
+ALTER TABLE pg_event_trigger ADD PRIMARY KEY USING INDEX
pg_event_trigger_oid_index;
+ALTER TABLE pg_extension ADD PRIMARY KEY USING INDEX pg_extension_oid_index;
+ALTER TABLE pg_foreign_data_wrapper ADD PRIMARY KEY USING INDEX
pg_foreign_data_wrapper_oid_index;
+ALTER TABLE pg_foreign_server ADD PRIMARY KEY USING INDEX
pg_foreign_server_oid_index;
+ALTER TABLE pg_foreign_table ADD PRIMARY KEY USING INDEX
pg_foreign_table_relid_index;
+ALTER TABLE pg_index ADD PRIMARY KEY USING INDEX pg_index_indexrelid_index;
+ALTER TABLE pg_inherits ADD PRIMARY KEY USING INDEX
pg_inherits_relid_seqno_index;
+ALTER TABLE pg_init_privs ADD PRIMARY KEY USING INDEX
pg_init_privs_o_c_o_index;
+ALTER TABLE pg_language ADD PRIMARY KEY USING INDEX pg_language_oid_index;
+ALTER TABLE pg_largeobject ADD PRIMARY KEY USING INDEX
pg_largeobject_loid_pn_index;
+ALTER TABLE pg_largeobject_metadata ADD PRIMARY KEY USING INDEX
pg_largeobject_metadata_oid_index;
+ALTER TABLE pg_namespace ADD PRIMARY KEY USING INDEX pg_namespace_oid_index;
+ALTER TABLE pg_opclass ADD PRIMARY KEY USING INDEX pg_opclass_oid_index;
+ALTER TABLE pg_operator ADD PRIMARY KEY USING INDEX pg_operator_oid_index;
+ALTER TABLE pg_opfamily ADD PRIMARY KEY USING INDEX pg_opfamily_oid_index;
+ALTER TABLE pg_partitioned_table ADD PRIMARY KEY USING INDEX
pg_partitioned_table_partrelid_index;
+ALTER TABLE pg_policy ADD PRIMARY KEY USING INDEX pg_policy_oid_index;
+ALTER TABLE pg_proc ADD PRIMARY KEY USING INDEX pg_proc_oid_index;
+ALTER TABLE pg_publication ADD PRIMARY KEY USING INDEX
pg_publication_oid_index;
+ALTER TABLE pg_publication_rel ADD PRIMARY KEY USING INDEX
pg_publication_rel_oid_index;
+ALTER TABLE pg_range ADD PRIMARY KEY USING INDEX pg_range_rngtypid_index;
+ALTER TABLE pg_replication_origin ADD PRIMARY KEY USING INDEX
pg_replication_origin_roiident_index;
+ALTER TABLE pg_rewrite ADD PRIMARY KEY USING INDEX pg_rewrite_oid_index;
+ALTER TABLE pg_sequence ADD PRIMARY KEY USING INDEX pg_sequence_seqrelid_index;
+ALTER TABLE pg_shdescription ADD PRIMARY KEY USING INDEX
pg_shdescription_o_c_index;
+ALTER TABLE pg_statistic ADD PRIMARY KEY USING INDEX
pg_statistic_relid_att_inh_index;
+ALTER TABLE pg_statistic_ext ADD PRIMARY KEY USING INDEX
pg_statistic_ext_oid_index;
+ALTER TABLE pg_statistic_ext_data ADD PRIMARY KEY USING INDEX
pg_statistic_ext_data_stxoid_index;
+ALTER TABLE pg_subscription ADD PRIMARY KEY USING INDEX
pg_subscription_oid_index;
+ALTER TABLE pg_subscription_rel ADD PRIMARY KEY USING INDEX
pg_subscription_rel_srrelid_srsubid_index;
+ALTER TABLE pg_tablespace ADD PRIMARY KEY USING INDEX pg_tablespace_oid_index;
+ALTER TABLE pg_transform ADD PRIMARY KEY USING INDEX pg_transform_oid_index;
+ALTER TABLE pg_trigger ADD PRIMARY KEY USING INDEX pg_trigger_oid_index;
+ALTER TABLE pg_ts_config ADD PRIMARY KEY USING INDEX pg_ts_config_oid_index;
+ALTER TABLE pg_ts_config_map ADD PRIMARY KEY USING INDEX
pg_ts_config_map_index;
+ALTER TABLE pg_ts_dict ADD PRIMARY KEY USING INDEX pg_ts_dict_oid_index;
+ALTER TABLE pg_ts_parser ADD PRIMARY KEY USING INDEX pg_ts_parser_oid_index;
+ALTER TABLE pg_ts_template ADD PRIMARY KEY USING INDEX
pg_ts_template_oid_index;
+ALTER TABLE pg_type ADD PRIMARY KEY USING INDEX pg_type_oid_index;
+ALTER TABLE pg_user_mapping ADD PRIMARY KEY USING INDEX
pg_user_mapping_oid_index;
+
+
CREATE VIEW pg_roles AS
SELECT
rolname,
diff --git a/src/test/modules/unsafe_tests/expected/alter_system_table.out
b/src/test/modules/unsafe_tests/expected/alter_system_table.out
index ecd1505cdc..941e4095d5 100644
--- a/src/test/modules/unsafe_tests/expected/alter_system_table.out
+++ b/src/test/modules/unsafe_tests/expected/alter_system_table.out
@@ -16,7 +16,7 @@ DETAIL: System catalog modifications are currently
disallowed.
CREATE TABLE t1x (a int, b anyarray);
ERROR: column "b" has pseudo-type anyarray
-- index on system catalog
-ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_oid_index;
+ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_nspname_index;
ERROR: permission denied: "pg_namespace" is a system catalog
-- write to system catalog table as superuser
-- (allowed even without allow_system_table_mods)
@@ -102,7 +102,7 @@ CREATE TABLE t1 (a int, b anyarray);
ROLLBACK;
-- index on system catalog
BEGIN;
-ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_oid_index;
+ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_nspname_index;
ROLLBACK;
-- write to system catalog table as superuser
BEGIN;
@@ -146,7 +146,6 @@ ALTER TABLE pg_description ALTER COLUMN description SET
STATISTICS -1;
ROLLBACK;
-- foreign key referencing catalog
BEGIN;
-ALTER TABLE pg_description ADD PRIMARY KEY USING INDEX
pg_description_o_c_o_index;
CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES
pg_description);
ROLLBACK;
-- RangeVarCallbackOwnsRelation()
diff --git a/src/test/modules/unsafe_tests/sql/alter_system_table.sql
b/src/test/modules/unsafe_tests/sql/alter_system_table.sql
index 5663570d31..d65659afa6 100644
--- a/src/test/modules/unsafe_tests/sql/alter_system_table.sql
+++ b/src/test/modules/unsafe_tests/sql/alter_system_table.sql
@@ -18,7 +18,7 @@ CREATE TABLE pg_catalog.test (a int);
CREATE TABLE t1x (a int, b anyarray);
-- index on system catalog
-ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_oid_index;
+ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_nspname_index;
-- write to system catalog table as superuser
-- (allowed even without allow_system_table_mods)
@@ -104,7 +104,7 @@ CREATE TABLE t1 (a int, b anyarray);
-- index on system catalog
BEGIN;
-ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_oid_index;
+ALTER TABLE pg_namespace ADD UNIQUE USING INDEX pg_namespace_nspname_index;
ROLLBACK;
-- write to system catalog table as superuser
@@ -156,7 +156,6 @@ CREATE SCHEMA pg_foo;
-- foreign key referencing catalog
BEGIN;
-ALTER TABLE pg_description ADD PRIMARY KEY USING INDEX
pg_description_o_c_o_index;
CREATE TABLE foo (a oid, b oid, c int, FOREIGN KEY (a, b, c) REFERENCES
pg_description);
ROLLBACK;
diff --git a/src/test/regress/expected/misc_sanity.out
b/src/test/regress/expected/misc_sanity.out
index 8538173ff8..7de58a69d2 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -109,3 +109,17 @@ ORDER BY 1, 2;
pg_largeobject_metadata | lomacl | aclitem[]
(11 rows)
+-- system catalogs without primary keys
+SELECT relname
+FROM pg_class
+WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'r'
+ AND pg_class.oid NOT IN (SELECT indrelid FROM pg_index WHERE
indisprimary)
+ORDER BY 1;
+ relname
+---------------
+ pg_depend
+ pg_seclabel
+ pg_shdepend
+ pg_shseclabel
+(4 rows)
+
diff --git a/src/test/regress/sql/misc_sanity.sql
b/src/test/regress/sql/misc_sanity.sql
index 3ce32e4725..e4451fd11f 100644
--- a/src/test/regress/sql/misc_sanity.sql
+++ b/src/test/regress/sql/misc_sanity.sql
@@ -94,3 +94,12 @@
relkind = 'r' AND
attstorage != 'p'
ORDER BY 1, 2;
+
+
+-- system catalogs without primary keys
+
+SELECT relname
+FROM pg_class
+WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'r'
+ AND pg_class.oid NOT IN (SELECT indrelid FROM pg_index WHERE
indisprimary)
+ORDER BY 1;
--
2.28.0
