0001-Add-primary-keys-to-system-catalogs.patch

From c9b1ff561dc7db045511a4da3cc87ecaac9c520b Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@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