Add primary keys to system catalogs
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
Attachments:
0001-Add-primary-keys-to-system-catalogs.patchtext/plain; charset=UTF-8; name=0001-Add-primary-keys-to-system-catalogs.patch; x-mac-creator=0; x-mac-type=0Download
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
On Sat, 3 Oct 2020, 14:40 Peter Eisentraut, <
peter.eisentraut@2ndquadrant.com> wrote:
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.Any thoughts on this direction?
I like the general idea a lot. I've used Pg for a long time and I still
struggle to march up relationships sometimes. Especially given that some
things use relation oid oid keys and some use named cols as keys.
So a big +1 from me for the idea. Especially if we ensure psql recognises
when the relation 'oid' attribute has a declared PK and includes it in the
column list.
I don't object to simply declaring them without any implementing triggers.
You aren't supposed to mess with the catalogs anyway. I'd actually like it
to be defended against more actively by default. So the FKs are implicitly
always valid, because the implementation says so. Much the same way trigger
based FKs are unless you break the rules and mess with the triggers.
Frankly I think we really need a way to mark FKs to be DISABLED or NOT
CHECKED or something and a way to mark them as NOT VALID. Rsther than
expecting uses to fiddle with the implementation triggers. But I don't
think FKs on system catalogs require that, it's just be cosmetic there.
On Sat, Oct 3, 2020 at 09:27:02PM +0800, Craig Ringer wrote:
On Sat, 3 Oct 2020, 14:40 Peter Eisentraut, <peter.eisentraut@2ndquadrant.com>
wrote: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.Any thoughts on this direction?
I like the general idea a lot. I've used Pg for a long time and I still
struggle to march up relationships sometimes. Especially given that some things
use relation oid oid keys and some use named cols as keys.�So a big +1 from me for the idea. Especially if we ensure psql recognises when
the relation 'oid' attribute has a declared PK and includes it in the column
list.�
+1
I don't object to simply declaring them without any implementing triggers. You
aren't supposed to mess with the catalogs anyway. I'd actually like it to be
defended against more actively by default. So the FKs are implicitly always
valid, because the implementation says so. Much the same way trigger based FKs
are unless you break the rules and mess with the triggers.Frankly I think we really need a way to mark FKs to be DISABLED or NOT CHECKED
or something and a way to mark them as NOT VALID. Rsther than expecting uses to
fiddle with the implementation triggers. But I don't think FKs on system
catalogs require that, it's just be cosmetic there.
+1
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
On Sat, Oct 3, 2020 at 9:27 AM Craig Ringer <craig.ringer@enterprisedb.com>
wrote:
So a big +1 from me for the idea. Especially if we ensure psql recognises
when the relation 'oid' attribute has a declared PK and includes it in the
column list.
Oid has been in the normal column list since v12 when it stopped being a
system column. Or did you mean something else?
On Sun, 4 Oct 2020, 01:32 John Naylor, <john.naylor@enterprisedb.com> wrote:
On Sat, Oct 3, 2020 at 9:27 AM Craig Ringer <craig.ringer@enterprisedb.com>
wrote:So a big +1 from me for the idea. Especially if we ensure psql
recognises when the relation 'oid' attribute has a declared PK and includes
it in the column list.Oid has been in the normal column list since v12 when it stopped being a
system column. Or did you mean something else?
It means I've spent way too long working on extensions running mainly on
pg11 lately and way too little reading -hackers. Ignore they bit. Thanks..
Show quoted text
Hi,
On 2020-10-03 08:40:31 +0200, Peter Eisentraut wrote:
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.
Hm. What prevents us from declaring the pkey during bootstrap? I don't
at all like adding yet another place that needs manual editing when
doing DDL changes.
Greetings,
Andres Freund
Andres Freund <andres@anarazel.de> writes:
On 2020-10-03 08:40:31 +0200, Peter Eisentraut wrote:
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.
Hm. What prevents us from declaring the pkey during bootstrap? I don't
at all like adding yet another place that needs manual editing when
doing DDL changes.
We don't add new catalogs often, so the cost-benefit ratio of automating
this looks pretty poor. It's not like you'll be able to forget to do it,
given the proposed regression test check for catalogs without pkeys.
One thing I'm wondering about though is whether Peter's implementation
ends up with desirable pg_depend contents for the pkey constraints.
Probably we want them to just be pinned and not have any explicit
dependencies on the associated indexes, but I haven't thought hard
about it.
regards, tom lane
Hi,
On 2020-10-06 15:31:16 -0400, Tom Lane wrote:
Andres Freund <andres@anarazel.de> writes:
On 2020-10-03 08:40:31 +0200, Peter Eisentraut wrote:
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.Hm. What prevents us from declaring the pkey during bootstrap? I don't
at all like adding yet another place that needs manual editing when
doing DDL changes.We don't add new catalogs often, so the cost-benefit ratio of automating
this looks pretty poor.
True, we don't create new ones that often. Still think that distributing
such setup over fewer places is good. And it's not like there's only a
handful of pkeys to start with. To me it makes more sense to add a
DECLARE_PRIMARY_KEY in indexing.h, replacing the relevant
DECLARE_UNIQUE_INDEX stanzas. Or, possibly preferrable, do it as part of
the CATALOG() directly - which'd avoid needing the index statement in
the first place.
The Catalog.pm part is trivial. It's abit harder to implement actually
creating the constraint - but even the hard route of adding a new field
to Boot_DeclareIndexStmt or such wouldn't be particularly hard.
One thing I'm wondering about though is whether Peter's implementation
ends up with desirable pg_depend contents for the pkey constraints.
Probably we want them to just be pinned and not have any explicit
dependencies on the associated indexes, but I haven't thought hard
about it.
That sounds right to me.
Wonder whether it's not time to move the DECLARE bits from indexing.h to
the individual catalogs, independent of what we're discussing here. With
todays Catalog.pm there's really not much point in keeping them
separate, imo.
Greetings,
Andres Freund
On Tue, Oct 6, 2020 at 4:16 PM Andres Freund <andres@anarazel.de> wrote:
True, we don't create new ones that often. Still think that distributing
such setup over fewer places is good. And it's not like there's only a
handful of pkeys to start with. To me it makes more sense to add a
DECLARE_PRIMARY_KEY in indexing.h, replacing the relevant
DECLARE_UNIQUE_INDEX stanzas.
That seems logical.
Or, possibly preferrable, do it as part of
the CATALOG() directly - which'd avoid needing the index statement in
the first place.
That line is already messy in places. AFAICT, you'd still need info from
the index statement (at least my shortened version below), leading to two
syntaxes and two places for the same thing. Maybe I'm missing something?
Wonder whether it's not time to move the DECLARE bits from indexing.h to
the individual catalogs, independent of what we're discussing here. With
todays Catalog.pm there's really not much point in keeping them
separate, imo.
That would look nicer, at least. A declaration could go from
DECLARE_UNIQUE_INDEX(pg_subscription_rel_srrelid_srsubid_index, 6117, on
pg_subscription_rel using btree(srrelid oid_ops, srsubid oid_ops));
#define SubscriptionRelSrrelidSrsubidIndexId 6117
to something like
DECLARE_UNIQUE_INDEX(btree(srrelid oid_ops, srsubid oid_ops), 6117,
SubscriptionRelSrrelidSrsubidIndexId));
--
John Naylor
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 2020-10-03 08:40, Peter Eisentraut wrote:
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 have reworked my patch like this. Now, the primary key is identified
by using DECLARE_UNIQUE_INDEX_PKEY() instead of DECLARE_UNIQUE_INDEX()
in the catalog pg_*.h files. This automatically generates the required
ALTER TABLE commands in a new file system_constraints.sql. initdb is
ordered so that that file is run before dependency processing, so the
system constraints also end up pinned (as was requested during previous
discussion).
Note, this needs the get_constraint_index() patch I just posted
separately. The old implementation of get_constraint_index() does not
handle pinned constraints.
There is something strange going on in the misc_sanity test, as seen by
the test output change
-NOTICE: pg_constraint contains unpinned initdb-created object(s)
This previously detected some constraints from the information schema,
so it was correct to point those out. But since it looks for the
min(oid) of a catalog, this will now find one of the new pinned
constraints, so it won't detect this case anymore. I think that test is
not written correctly.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
Attachments:
v2-0001-Add-primary-keys-and-unique-constraints-to-system.patchtext/plain; charset=UTF-8; name=v2-0001-Add-primary-keys-and-unique-constraints-to-system.patch; x-mac-creator=0; x-mac-type=0Download
From 90bee9c99aa7347861a5a7651922cc7116d9f54b Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Mon, 7 Dec 2020 11:11:24 +0100
Subject: [PATCH v2] Add primary keys and unique constraints to system catalogs
For those system catalogs that have a unique indexes, make a primary
key and unique constraint, using ALTER TABLE ... PRIMARY KEY/UNIQUE
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.
The constraint creation statements are automatically created by
genbki.pl from DECLARE_UNIQUE_INDEX directives. To specify which one
of the available unique indexes is the primary key, use the new
directive DECLARE_UNIQUE_INDEX_PKEY instead.
---
src/backend/catalog/.gitignore | 1 +
src/backend/catalog/Catalog.pm | 11 ++--
src/backend/catalog/Makefile | 3 +-
src/backend/catalog/genbki.pl | 21 +++++++
src/bin/initdb/initdb.c | 60 +++++--------------
src/include/catalog/genbki.h | 3 +-
src/include/catalog/pg_aggregate.h | 2 +-
src/include/catalog/pg_am.h | 2 +-
src/include/catalog/pg_amop.h | 2 +-
src/include/catalog/pg_amproc.h | 2 +-
src/include/catalog/pg_attrdef.h | 2 +-
src/include/catalog/pg_attribute.h | 2 +-
src/include/catalog/pg_auth_members.h | 2 +-
src/include/catalog/pg_authid.h | 2 +-
src/include/catalog/pg_cast.h | 2 +-
src/include/catalog/pg_class.h | 2 +-
src/include/catalog/pg_collation.h | 2 +-
src/include/catalog/pg_constraint.h | 2 +-
src/include/catalog/pg_conversion.h | 2 +-
src/include/catalog/pg_database.h | 2 +-
src/include/catalog/pg_db_role_setting.h | 2 +-
src/include/catalog/pg_default_acl.h | 2 +-
src/include/catalog/pg_description.h | 2 +-
src/include/catalog/pg_enum.h | 2 +-
src/include/catalog/pg_event_trigger.h | 2 +-
src/include/catalog/pg_extension.h | 2 +-
src/include/catalog/pg_foreign_data_wrapper.h | 2 +-
src/include/catalog/pg_foreign_server.h | 2 +-
src/include/catalog/pg_foreign_table.h | 2 +-
src/include/catalog/pg_index.h | 2 +-
src/include/catalog/pg_inherits.h | 2 +-
src/include/catalog/pg_init_privs.h | 2 +-
src/include/catalog/pg_language.h | 2 +-
src/include/catalog/pg_largeobject.h | 2 +-
src/include/catalog/pg_largeobject_metadata.h | 2 +-
src/include/catalog/pg_namespace.h | 2 +-
src/include/catalog/pg_opclass.h | 2 +-
src/include/catalog/pg_operator.h | 2 +-
src/include/catalog/pg_opfamily.h | 2 +-
src/include/catalog/pg_partitioned_table.h | 2 +-
src/include/catalog/pg_policy.h | 2 +-
src/include/catalog/pg_proc.h | 2 +-
src/include/catalog/pg_publication.h | 2 +-
src/include/catalog/pg_publication_rel.h | 2 +-
src/include/catalog/pg_range.h | 2 +-
src/include/catalog/pg_replication_origin.h | 2 +-
src/include/catalog/pg_rewrite.h | 2 +-
src/include/catalog/pg_sequence.h | 2 +-
src/include/catalog/pg_shdescription.h | 2 +-
src/include/catalog/pg_statistic.h | 2 +-
src/include/catalog/pg_statistic_ext.h | 2 +-
src/include/catalog/pg_statistic_ext_data.h | 2 +-
src/include/catalog/pg_subscription.h | 2 +-
src/include/catalog/pg_subscription_rel.h | 2 +-
src/include/catalog/pg_tablespace.h | 2 +-
src/include/catalog/pg_transform.h | 2 +-
src/include/catalog/pg_trigger.h | 2 +-
src/include/catalog/pg_ts_config.h | 2 +-
src/include/catalog/pg_ts_config_map.h | 2 +-
src/include/catalog/pg_ts_dict.h | 2 +-
src/include/catalog/pg_ts_parser.h | 2 +-
src/include/catalog/pg_ts_template.h | 2 +-
src/include/catalog/pg_type.h | 2 +-
src/include/catalog/pg_user_mapping.h | 2 +-
.../expected/alter_system_table.out | 6 +-
.../unsafe_tests/sql/alter_system_table.sql | 5 +-
src/test/regress/expected/misc_sanity.out | 31 +++++++++-
src/test/regress/sql/misc_sanity.sql | 22 +++++++
68 files changed, 161 insertions(+), 118 deletions(-)
diff --git a/src/backend/catalog/.gitignore b/src/backend/catalog/.gitignore
index 11e2e52023..4bd3ee9d7f 100644
--- a/src/backend/catalog/.gitignore
+++ b/src/backend/catalog/.gitignore
@@ -1,4 +1,5 @@
/postgres.bki
/schemapg.h
+/system_constraints.sql
/pg_*_d.h
/bki-stamp
diff --git a/src/backend/catalog/Catalog.pm b/src/backend/catalog/Catalog.pm
index dd39a086ce..20b121c551 100644
--- a/src/backend/catalog/Catalog.pm
+++ b/src/backend/catalog/Catalog.pm
@@ -94,14 +94,15 @@ sub ParseHeader
push @{ $catalog{toasting} },
{ parent_table => $1, toast_oid => $2, toast_index_oid => $3 };
}
- elsif (/^DECLARE_(UNIQUE_)?INDEX\(\s*(\w+),\s*(\d+),\s*(.+)\)/)
+ elsif (/^DECLARE_(UNIQUE_)?INDEX(_PKEY)?\(\s*(\w+),\s*(\d+),\s*(.+)\)/)
{
push @{ $catalog{indexing} },
- {
+ {
is_unique => $1 ? 1 : 0,
- index_name => $2,
- index_oid => $3,
- index_decl => $4
+ is_pkey => $2 ? 1 : 0,
+ index_name => $3,
+ index_oid => $4,
+ index_decl => $5
};
}
elsif (/^CATALOG\((\w+),(\d+),(\w+)\)/)
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 2519771210..2c1b8185d5 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -121,6 +121,7 @@ $(top_builddir)/src/include/catalog/header-stamp: bki-stamp
.PHONY: install-data
install-data: bki-stamp installdirs
$(INSTALL_DATA) $(call vpathsearch,postgres.bki) '$(DESTDIR)$(datadir)/postgres.bki'
+ $(INSTALL_DATA) $(call vpathsearch,system_constraints.sql) '$(DESTDIR)$(datadir)/system_constraints.sql'
$(INSTALL_DATA) $(srcdir)/system_views.sql '$(DESTDIR)$(datadir)/system_views.sql'
$(INSTALL_DATA) $(srcdir)/information_schema.sql '$(DESTDIR)$(datadir)/information_schema.sql'
$(INSTALL_DATA) $(srcdir)/sql_features.txt '$(DESTDIR)$(datadir)/sql_features.txt'
@@ -130,7 +131,7 @@ installdirs:
.PHONY: uninstall-data
uninstall-data:
- rm -f $(addprefix '$(DESTDIR)$(datadir)'/, postgres.bki system_views.sql information_schema.sql sql_features.txt)
+ rm -f $(addprefix '$(DESTDIR)$(datadir)'/, postgres.bki system_constraints.sql system_views.sql information_schema.sql sql_features.txt)
# postgres.bki and the generated headers are in the distribution tarball,
# so they are not cleaned here.
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 66fdaf67b1..711a864115 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -55,6 +55,7 @@
my @toast_decls;
my @index_decls;
my %oidcounts;
+my @system_constraints;
foreach my $header (@ARGV)
{
@@ -137,6 +138,17 @@
$index->{index_name}, $index->{index_oid},
$index->{index_decl};
$oidcounts{ $index->{index_oid} }++;
+
+ if ($index->{is_unique})
+ {
+ $index->{index_decl} =~ /on (\w+) using/;
+ my $tblname = $1;
+ push @system_constraints,
+ sprintf "ALTER TABLE %s ADD %s USING INDEX %s;",
+ $tblname,
+ $index->{is_pkey} ? "PRIMARY KEY" : "UNIQUE",
+ $index->{index_name};
+ }
}
}
@@ -388,6 +400,9 @@
my $schemafile = $output_path . 'schemapg.h';
open my $schemapg, '>', $schemafile . $tmpext
or die "can't open $schemafile$tmpext: $!";
+my $constraints_file = $output_path . 'system_constraints.sql';
+open my $constraints, '>', $constraints_file . $tmpext
+ or die "can't open $constraints_file$tmpext: $!";
# Generate postgres.bki and pg_*_d.h headers.
@@ -648,6 +663,10 @@
"genbki OID counter reached $GenbkiNextOid, overrunning FirstBootstrapObjectId\n"
if $GenbkiNextOid > $FirstBootstrapObjectId;
+foreach my $c (@system_constraints)
+{
+ print $constraints $c, "\n";
+}
# Now generate schemapg.h
@@ -688,10 +707,12 @@
# We're done emitting data
close $bki;
close $schemapg;
+close $constraints;
# Finally, rename the completed files into place.
Catalog::RenameTempFile($bkifile, $tmpext);
Catalog::RenameTempFile($schemafile, $tmpext);
+Catalog::RenameTempFile($constraints_file, $tmpext);
exit 0;
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index ee3bfa82f4..f5ab22d5cd 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -158,6 +158,7 @@ static char *conf_file;
static char *dictionary_file;
static char *info_schema_file;
static char *features_file;
+static char *system_constraints_file;
static char *system_views_file;
static bool success = false;
static bool made_new_pgdata = false;
@@ -250,10 +251,9 @@ static void bootstrap_template1(void);
static void setup_auth(FILE *cmdfd);
static void get_su_pwd(void);
static void setup_depend(FILE *cmdfd);
-static void setup_sysviews(FILE *cmdfd);
+static void setup_run_file(FILE *cmdfd, const char *filename);
static void setup_description(FILE *cmdfd);
static void setup_collation(FILE *cmdfd);
-static void setup_dictionary(FILE *cmdfd);
static void setup_privileges(FILE *cmdfd);
static void set_info_version(void);
static void setup_schema(FILE *cmdfd);
@@ -1599,17 +1599,16 @@ setup_depend(FILE *cmdfd)
}
/*
- * set up system views
+ * Run external file
*/
static void
-setup_sysviews(FILE *cmdfd)
+setup_run_file(FILE *cmdfd, const char *filename)
{
- char **line;
- char **sysviews_setup;
+ char **lines;
- sysviews_setup = readfile(system_views_file);
+ lines = readfile(filename);
- for (line = sysviews_setup; *line != NULL; line++)
+ for (char **line = lines; *line != NULL; line++)
{
PG_CMD_PUTS(*line);
free(*line);
@@ -1617,7 +1616,7 @@ setup_sysviews(FILE *cmdfd)
PG_CMD_PUTS("\n\n");
- free(sysviews_setup);
+ free(lines);
}
/*
@@ -1660,27 +1659,6 @@ setup_collation(FILE *cmdfd)
PG_CMD_PUTS("SELECT pg_import_system_collations('pg_catalog');\n\n");
}
-/*
- * load extra dictionaries (Snowball stemmers)
- */
-static void
-setup_dictionary(FILE *cmdfd)
-{
- char **line;
- char **conv_lines;
-
- conv_lines = readfile(dictionary_file);
- for (line = conv_lines; *line != NULL; line++)
- {
- PG_CMD_PUTS(*line);
- free(*line);
- }
-
- PG_CMD_PUTS("\n\n");
-
- free(conv_lines);
-}
-
/*
* Set up privileges
*
@@ -1881,20 +1859,7 @@ set_info_version(void)
static void
setup_schema(FILE *cmdfd)
{
- char **line;
- char **lines;
-
- lines = readfile(info_schema_file);
-
- for (line = lines; *line != NULL; line++)
- {
- PG_CMD_PUTS(*line);
- free(*line);
- }
-
- PG_CMD_PUTS("\n\n");
-
- free(lines);
+ setup_run_file(cmdfd, info_schema_file);
PG_CMD_PRINTF("UPDATE information_schema.sql_implementation_info "
" SET character_value = '%s' "
@@ -2530,6 +2495,7 @@ setup_data_file_paths(void)
set_input(&dictionary_file, "snowball_create.sql");
set_input(&info_schema_file, "information_schema.sql");
set_input(&features_file, "sql_features.txt");
+ set_input(&system_constraints_file, "system_constraints.sql");
set_input(&system_views_file, "system_views.sql");
if (show_setting || debug)
@@ -2891,6 +2857,8 @@ initialize_data_directory(void)
setup_auth(cmdfd);
+ setup_run_file(cmdfd, system_constraints_file);
+
setup_depend(cmdfd);
/*
@@ -2898,13 +2866,13 @@ initialize_data_directory(void)
* They are all droppable at the whim of the DBA.
*/
- setup_sysviews(cmdfd);
+ setup_run_file(cmdfd, system_views_file);
setup_description(cmdfd);
setup_collation(cmdfd);
- setup_dictionary(cmdfd);
+ setup_run_file(cmdfd, dictionary_file);
setup_privileges(cmdfd);
diff --git a/src/include/catalog/genbki.h b/src/include/catalog/genbki.h
index 92eea9d345..a35181a3b1 100644
--- a/src/include/catalog/genbki.h
+++ b/src/include/catalog/genbki.h
@@ -58,7 +58,7 @@
* These lines processed by genbki.pl to create the statements
* the bootstrap parser will turn into DefineIndex calls.
*
- * The keyword is DECLARE_INDEX or DECLARE_UNIQUE_INDEX. The first two
+ * The keyword is DECLARE_INDEX or DECLARE_UNIQUE_INDEX or DECLARE_UNIQUE_INDEX_PKEY. The first two
* arguments are the index name and OID, the rest is much like a standard
* 'create index' SQL command.
*
@@ -70,6 +70,7 @@
*/
#define DECLARE_INDEX(name,oid,decl) extern int no_such_variable
#define DECLARE_UNIQUE_INDEX(name,oid,decl) extern int no_such_variable
+#define DECLARE_UNIQUE_INDEX_PKEY(name,oid,decl) extern int no_such_variable
/* The following are never defined; they are here only for documentation. */
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index 7e94d604b9..18e2a973ad 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -110,7 +110,7 @@ typedef FormData_pg_aggregate *Form_pg_aggregate;
DECLARE_TOAST(pg_aggregate, 4159, 4160);
-DECLARE_UNIQUE_INDEX(pg_aggregate_fnoid_index, 2650, on pg_aggregate using btree(aggfnoid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_aggregate_fnoid_index, 2650, on pg_aggregate using btree(aggfnoid oid_ops));
#define AggregateFnoidIndexId 2650
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_am.h b/src/include/catalog/pg_am.h
index bc73e88a1a..1630b7fe87 100644
--- a/src/include/catalog/pg_am.h
+++ b/src/include/catalog/pg_am.h
@@ -49,7 +49,7 @@ typedef FormData_pg_am *Form_pg_am;
DECLARE_UNIQUE_INDEX(pg_am_name_index, 2651, on pg_am using btree(amname name_ops));
#define AmNameIndexId 2651
-DECLARE_UNIQUE_INDEX(pg_am_oid_index, 2652, on pg_am using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_am_oid_index, 2652, on pg_am using btree(oid oid_ops));
#define AmOidIndexId 2652
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index c5215cdaaf..1aa014613d 100644
--- a/src/include/catalog/pg_amop.h
+++ b/src/include/catalog/pg_amop.h
@@ -91,7 +91,7 @@ DECLARE_UNIQUE_INDEX(pg_amop_fam_strat_index, 2653, on pg_amop using btree(amopf
#define AccessMethodStrategyIndexId 2653
DECLARE_UNIQUE_INDEX(pg_amop_opr_fam_index, 2654, on pg_amop using btree(amopopr oid_ops, amoppurpose char_ops, amopfamily oid_ops));
#define AccessMethodOperatorIndexId 2654
-DECLARE_UNIQUE_INDEX(pg_amop_oid_index, 2756, on pg_amop using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_amop_oid_index, 2756, on pg_amop using btree(oid oid_ops));
#define AccessMethodOperatorOidIndexId 2756
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h
index cd422d5ad7..f337efd558 100644
--- a/src/include/catalog/pg_amproc.h
+++ b/src/include/catalog/pg_amproc.h
@@ -69,7 +69,7 @@ typedef FormData_pg_amproc *Form_pg_amproc;
DECLARE_UNIQUE_INDEX(pg_amproc_fam_proc_index, 2655, on pg_amproc using btree(amprocfamily oid_ops, amproclefttype oid_ops, amprocrighttype oid_ops, amprocnum int2_ops));
#define AccessMethodProcedureIndexId 2655
-DECLARE_UNIQUE_INDEX(pg_amproc_oid_index, 2757, on pg_amproc using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_amproc_oid_index, 2757, on pg_amproc using btree(oid oid_ops));
#define AccessMethodProcedureOidIndexId 2757
#endif /* PG_AMPROC_H */
diff --git a/src/include/catalog/pg_attrdef.h b/src/include/catalog/pg_attrdef.h
index 9d5426be97..d48fc402b5 100644
--- a/src/include/catalog/pg_attrdef.h
+++ b/src/include/catalog/pg_attrdef.h
@@ -50,7 +50,7 @@ DECLARE_TOAST(pg_attrdef, 2830, 2831);
DECLARE_UNIQUE_INDEX(pg_attrdef_adrelid_adnum_index, 2656, on pg_attrdef using btree(adrelid oid_ops, adnum int2_ops));
#define AttrDefaultIndexId 2656
-DECLARE_UNIQUE_INDEX(pg_attrdef_oid_index, 2657, on pg_attrdef using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_attrdef_oid_index, 2657, on pg_attrdef using btree(oid oid_ops));
#define AttrDefaultOidIndexId 2657
#endif /* PG_ATTRDEF_H */
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index cdf75a2380..064d66d0cf 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -194,7 +194,7 @@ typedef FormData_pg_attribute *Form_pg_attribute;
DECLARE_UNIQUE_INDEX(pg_attribute_relid_attnam_index, 2658, on pg_attribute using btree(attrelid oid_ops, attname name_ops));
#define AttributeRelidNameIndexId 2658
-DECLARE_UNIQUE_INDEX(pg_attribute_relid_attnum_index, 2659, on pg_attribute using btree(attrelid oid_ops, attnum int2_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_attribute_relid_attnum_index, 2659, on pg_attribute using btree(attrelid oid_ops, attnum int2_ops));
#define AttributeRelidNumIndexId 2659
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_auth_members.h b/src/include/catalog/pg_auth_members.h
index d1f609da50..13831b7691 100644
--- a/src/include/catalog/pg_auth_members.h
+++ b/src/include/catalog/pg_auth_members.h
@@ -42,7 +42,7 @@ CATALOG(pg_auth_members,1261,AuthMemRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_
*/
typedef FormData_pg_auth_members *Form_pg_auth_members;
-DECLARE_UNIQUE_INDEX(pg_auth_members_role_member_index, 2694, on pg_auth_members using btree(roleid oid_ops, member oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_auth_members_role_member_index, 2694, on pg_auth_members using btree(roleid oid_ops, member oid_ops));
#define AuthMemRoleMemIndexId 2694
DECLARE_UNIQUE_INDEX(pg_auth_members_member_role_index, 2695, on pg_auth_members using btree(member oid_ops, roleid oid_ops));
#define AuthMemMemRoleIndexId 2695
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index 664b3910f5..6866e0e9ed 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -61,7 +61,7 @@ DECLARE_TOAST(pg_authid, 4175, 4176);
DECLARE_UNIQUE_INDEX(pg_authid_rolname_index, 2676, on pg_authid using btree(rolname name_ops));
#define AuthIdRolnameIndexId 2676
-DECLARE_UNIQUE_INDEX(pg_authid_oid_index, 2677, on pg_authid using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_authid_oid_index, 2677, on pg_authid using btree(oid oid_ops));
#define AuthIdOidIndexId 2677
#endif /* PG_AUTHID_H */
diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h
index 0a38ec00bc..8aafb95052 100644
--- a/src/include/catalog/pg_cast.h
+++ b/src/include/catalog/pg_cast.h
@@ -56,7 +56,7 @@ CATALOG(pg_cast,2605,CastRelationId)
*/
typedef FormData_pg_cast *Form_pg_cast;
-DECLARE_UNIQUE_INDEX(pg_cast_oid_index, 2660, on pg_cast using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_cast_oid_index, 2660, on pg_cast using btree(oid oid_ops));
#define CastOidIndexId 2660
DECLARE_UNIQUE_INDEX(pg_cast_source_target_index, 2661, on pg_cast using btree(castsource oid_ops, casttarget oid_ops));
#define CastSourceTargetIndexId 2661
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index bb5e72ca43..e56e704934 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -152,7 +152,7 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,Relat
*/
typedef FormData_pg_class *Form_pg_class;
-DECLARE_UNIQUE_INDEX(pg_class_oid_index, 2662, on pg_class using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_class_oid_index, 2662, on pg_class using btree(oid oid_ops));
#define ClassOidIndexId 2662
DECLARE_UNIQUE_INDEX(pg_class_relname_nsp_index, 2663, on pg_class using btree(relname name_ops, relnamespace oid_ops));
#define ClassNameNspIndexId 2663
diff --git a/src/include/catalog/pg_collation.h b/src/include/catalog/pg_collation.h
index e59b1484aa..b19e05f9aa 100644
--- a/src/include/catalog/pg_collation.h
+++ b/src/include/catalog/pg_collation.h
@@ -48,7 +48,7 @@ typedef FormData_pg_collation *Form_pg_collation;
DECLARE_UNIQUE_INDEX(pg_collation_name_enc_nsp_index, 3164, on pg_collation using btree(collname name_ops, collencoding int4_ops, collnamespace oid_ops));
#define CollationNameEncNspIndexId 3164
-DECLARE_UNIQUE_INDEX(pg_collation_oid_index, 3085, on pg_collation using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_collation_oid_index, 3085, on pg_collation using btree(oid oid_ops));
#define CollationOidIndexId 3085
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index 8580887519..e8fe5569e7 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -161,7 +161,7 @@ DECLARE_UNIQUE_INDEX(pg_constraint_conrelid_contypid_conname_index, 2665, on pg_
#define ConstraintRelidTypidNameIndexId 2665
DECLARE_INDEX(pg_constraint_contypid_index, 2666, on pg_constraint using btree(contypid oid_ops));
#define ConstraintTypidIndexId 2666
-DECLARE_UNIQUE_INDEX(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
#define ConstraintOidIndexId 2667
DECLARE_INDEX(pg_constraint_conparentid_index, 2579, on pg_constraint using btree(conparentid oid_ops));
#define ConstraintParentIndexId 2579
diff --git a/src/include/catalog/pg_conversion.h b/src/include/catalog/pg_conversion.h
index 0536c4a544..8f52702581 100644
--- a/src/include/catalog/pg_conversion.h
+++ b/src/include/catalog/pg_conversion.h
@@ -64,7 +64,7 @@ DECLARE_UNIQUE_INDEX(pg_conversion_default_index, 2668, on pg_conversion using b
#define ConversionDefaultIndexId 2668
DECLARE_UNIQUE_INDEX(pg_conversion_name_nsp_index, 2669, on pg_conversion using btree(conname name_ops, connamespace oid_ops));
#define ConversionNameNspIndexId 2669
-DECLARE_UNIQUE_INDEX(pg_conversion_oid_index, 2670, on pg_conversion using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_conversion_oid_index, 2670, on pg_conversion using btree(oid oid_ops));
#define ConversionOidIndexId 2670
diff --git a/src/include/catalog/pg_database.h b/src/include/catalog/pg_database.h
index 47bcf40346..fe5f43b589 100644
--- a/src/include/catalog/pg_database.h
+++ b/src/include/catalog/pg_database.h
@@ -86,7 +86,7 @@ DECLARE_TOAST(pg_database, 4177, 4178);
DECLARE_UNIQUE_INDEX(pg_database_datname_index, 2671, on pg_database using btree(datname name_ops));
#define DatabaseNameIndexId 2671
-DECLARE_UNIQUE_INDEX(pg_database_oid_index, 2672, on pg_database using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_database_oid_index, 2672, on pg_database using btree(oid oid_ops));
#define DatabaseOidIndexId 2672
#endif /* PG_DATABASE_H */
diff --git a/src/include/catalog/pg_db_role_setting.h b/src/include/catalog/pg_db_role_setting.h
index 6f8ea02485..f95d8dc44d 100644
--- a/src/include/catalog/pg_db_role_setting.h
+++ b/src/include/catalog/pg_db_role_setting.h
@@ -47,7 +47,7 @@ DECLARE_TOAST(pg_db_role_setting, 2966, 2967);
#define PgDbRoleSettingToastTable 2966
#define PgDbRoleSettingToastIndex 2967
-DECLARE_UNIQUE_INDEX(pg_db_role_setting_databaseid_rol_index, 2965, on pg_db_role_setting using btree(setdatabase oid_ops, setrole oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_db_role_setting_databaseid_rol_index, 2965, on pg_db_role_setting using btree(setdatabase oid_ops, setrole oid_ops));
#define DbRoleSettingDatidRolidIndexId 2965
/*
diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h
index a8771dfaa9..ac31162536 100644
--- a/src/include/catalog/pg_default_acl.h
+++ b/src/include/catalog/pg_default_acl.h
@@ -51,7 +51,7 @@ DECLARE_TOAST(pg_default_acl, 4143, 4144);
DECLARE_UNIQUE_INDEX(pg_default_acl_role_nsp_obj_index, 827, on pg_default_acl using btree(defaclrole oid_ops, defaclnamespace oid_ops, defaclobjtype char_ops));
#define DefaultAclRoleNspObjIndexId 827
-DECLARE_UNIQUE_INDEX(pg_default_acl_oid_index, 828, on pg_default_acl using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_default_acl_oid_index, 828, on pg_default_acl using btree(oid oid_ops));
#define DefaultAclOidIndexId 828
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_description.h b/src/include/catalog/pg_description.h
index 0686ff7ada..7058121e60 100644
--- a/src/include/catalog/pg_description.h
+++ b/src/include/catalog/pg_description.h
@@ -65,7 +65,7 @@ typedef FormData_pg_description * Form_pg_description;
DECLARE_TOAST(pg_description, 2834, 2835);
-DECLARE_UNIQUE_INDEX(pg_description_o_c_o_index, 2675, on pg_description using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_description_o_c_o_index, 2675, on pg_description using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops));
#define DescriptionObjIndexId 2675
#endif /* PG_DESCRIPTION_H */
diff --git a/src/include/catalog/pg_enum.h b/src/include/catalog/pg_enum.h
index 39e47fd1a0..f1e1810a0e 100644
--- a/src/include/catalog/pg_enum.h
+++ b/src/include/catalog/pg_enum.h
@@ -43,7 +43,7 @@ CATALOG(pg_enum,3501,EnumRelationId)
*/
typedef FormData_pg_enum *Form_pg_enum;
-DECLARE_UNIQUE_INDEX(pg_enum_oid_index, 3502, on pg_enum using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_enum_oid_index, 3502, on pg_enum using btree(oid oid_ops));
#define EnumOidIndexId 3502
DECLARE_UNIQUE_INDEX(pg_enum_typid_label_index, 3503, on pg_enum using btree(enumtypid oid_ops, enumlabel name_ops));
#define EnumTypIdLabelIndexId 3503
diff --git a/src/include/catalog/pg_event_trigger.h b/src/include/catalog/pg_event_trigger.h
index dda54a8649..0f86d91e17 100644
--- a/src/include/catalog/pg_event_trigger.h
+++ b/src/include/catalog/pg_event_trigger.h
@@ -52,7 +52,7 @@ DECLARE_TOAST(pg_event_trigger, 4145, 4146);
DECLARE_UNIQUE_INDEX(pg_event_trigger_evtname_index, 3467, on pg_event_trigger using btree(evtname name_ops));
#define EventTriggerNameIndexId 3467
-DECLARE_UNIQUE_INDEX(pg_event_trigger_oid_index, 3468, on pg_event_trigger using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_event_trigger_oid_index, 3468, on pg_event_trigger using btree(oid oid_ops));
#define EventTriggerOidIndexId 3468
#endif /* PG_EVENT_TRIGGER_H */
diff --git a/src/include/catalog/pg_extension.h b/src/include/catalog/pg_extension.h
index dc23fd59d0..ed0deecb13 100644
--- a/src/include/catalog/pg_extension.h
+++ b/src/include/catalog/pg_extension.h
@@ -51,7 +51,7 @@ typedef FormData_pg_extension *Form_pg_extension;
DECLARE_TOAST(pg_extension, 4147, 4148);
-DECLARE_UNIQUE_INDEX(pg_extension_oid_index, 3080, on pg_extension using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_extension_oid_index, 3080, on pg_extension using btree(oid oid_ops));
#define ExtensionOidIndexId 3080
DECLARE_UNIQUE_INDEX(pg_extension_name_index, 3081, on pg_extension using btree(extname name_ops));
#define ExtensionNameIndexId 3081
diff --git a/src/include/catalog/pg_foreign_data_wrapper.h b/src/include/catalog/pg_foreign_data_wrapper.h
index 0506a25461..934d935c13 100644
--- a/src/include/catalog/pg_foreign_data_wrapper.h
+++ b/src/include/catalog/pg_foreign_data_wrapper.h
@@ -49,7 +49,7 @@ typedef FormData_pg_foreign_data_wrapper *Form_pg_foreign_data_wrapper;
DECLARE_TOAST(pg_foreign_data_wrapper, 4149, 4150);
-DECLARE_UNIQUE_INDEX(pg_foreign_data_wrapper_oid_index, 112, on pg_foreign_data_wrapper using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_foreign_data_wrapper_oid_index, 112, on pg_foreign_data_wrapper using btree(oid oid_ops));
#define ForeignDataWrapperOidIndexId 112
DECLARE_UNIQUE_INDEX(pg_foreign_data_wrapper_name_index, 548, on pg_foreign_data_wrapper using btree(fdwname name_ops));
#define ForeignDataWrapperNameIndexId 548
diff --git a/src/include/catalog/pg_foreign_server.h b/src/include/catalog/pg_foreign_server.h
index b076d4c2be..3d84722354 100644
--- a/src/include/catalog/pg_foreign_server.h
+++ b/src/include/catalog/pg_foreign_server.h
@@ -49,7 +49,7 @@ typedef FormData_pg_foreign_server *Form_pg_foreign_server;
DECLARE_TOAST(pg_foreign_server, 4151, 4152);
-DECLARE_UNIQUE_INDEX(pg_foreign_server_oid_index, 113, on pg_foreign_server using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_foreign_server_oid_index, 113, on pg_foreign_server using btree(oid oid_ops));
#define ForeignServerOidIndexId 113
DECLARE_UNIQUE_INDEX(pg_foreign_server_name_index, 549, on pg_foreign_server using btree(srvname name_ops));
#define ForeignServerNameIndexId 549
diff --git a/src/include/catalog/pg_foreign_table.h b/src/include/catalog/pg_foreign_table.h
index ff01cc6c14..d75dc26693 100644
--- a/src/include/catalog/pg_foreign_table.h
+++ b/src/include/catalog/pg_foreign_table.h
@@ -44,7 +44,7 @@ typedef FormData_pg_foreign_table *Form_pg_foreign_table;
DECLARE_TOAST(pg_foreign_table, 4153, 4154);
-DECLARE_UNIQUE_INDEX(pg_foreign_table_relid_index, 3119, on pg_foreign_table using btree(ftrelid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_foreign_table_relid_index, 3119, on pg_foreign_table using btree(ftrelid oid_ops));
#define ForeignTableRelidIndexId 3119
#endif /* PG_FOREIGN_TABLE_H */
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index 0af6617b13..35580612d4 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -69,7 +69,7 @@ typedef FormData_pg_index *Form_pg_index;
DECLARE_INDEX(pg_index_indrelid_index, 2678, on pg_index using btree(indrelid oid_ops));
#define IndexIndrelidIndexId 2678
-DECLARE_UNIQUE_INDEX(pg_index_indexrelid_index, 2679, on pg_index using btree(indexrelid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_index_indexrelid_index, 2679, on pg_index using btree(indexrelid oid_ops));
#define IndexRelidIndexId 2679
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_inherits.h b/src/include/catalog/pg_inherits.h
index 63ee9a75e8..87283c040e 100644
--- a/src/include/catalog/pg_inherits.h
+++ b/src/include/catalog/pg_inherits.h
@@ -43,7 +43,7 @@ CATALOG(pg_inherits,2611,InheritsRelationId)
*/
typedef FormData_pg_inherits *Form_pg_inherits;
-DECLARE_UNIQUE_INDEX(pg_inherits_relid_seqno_index, 2680, on pg_inherits using btree(inhrelid oid_ops, inhseqno int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_inherits_relid_seqno_index, 2680, on pg_inherits using btree(inhrelid oid_ops, inhseqno int4_ops));
#define InheritsRelidSeqnoIndexId 2680
DECLARE_INDEX(pg_inherits_parent_index, 2187, on pg_inherits using btree(inhparent oid_ops));
#define InheritsParentIndexId 2187
diff --git a/src/include/catalog/pg_init_privs.h b/src/include/catalog/pg_init_privs.h
index 048556418a..7d56b2e461 100644
--- a/src/include/catalog/pg_init_privs.h
+++ b/src/include/catalog/pg_init_privs.h
@@ -64,7 +64,7 @@ typedef FormData_pg_init_privs * Form_pg_init_privs;
DECLARE_TOAST(pg_init_privs, 4155, 4156);
-DECLARE_UNIQUE_INDEX(pg_init_privs_o_c_o_index, 3395, on pg_init_privs using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_init_privs_o_c_o_index, 3395, on pg_init_privs using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops));
#define InitPrivsObjIndexId 3395
/*
diff --git a/src/include/catalog/pg_language.h b/src/include/catalog/pg_language.h
index df17d332d2..f27bbc984e 100644
--- a/src/include/catalog/pg_language.h
+++ b/src/include/catalog/pg_language.h
@@ -68,7 +68,7 @@ DECLARE_TOAST(pg_language, 4157, 4158);
DECLARE_UNIQUE_INDEX(pg_language_name_index, 2681, on pg_language using btree(lanname name_ops));
#define LanguageNameIndexId 2681
-DECLARE_UNIQUE_INDEX(pg_language_oid_index, 2682, on pg_language using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_language_oid_index, 2682, on pg_language using btree(oid oid_ops));
#define LanguageOidIndexId 2682
#endif /* PG_LANGUAGE_H */
diff --git a/src/include/catalog/pg_largeobject.h b/src/include/catalog/pg_largeobject.h
index 064b2468ac..308555e223 100644
--- a/src/include/catalog/pg_largeobject.h
+++ b/src/include/catalog/pg_largeobject.h
@@ -43,7 +43,7 @@ CATALOG(pg_largeobject,2613,LargeObjectRelationId)
*/
typedef FormData_pg_largeobject *Form_pg_largeobject;
-DECLARE_UNIQUE_INDEX(pg_largeobject_loid_pn_index, 2683, on pg_largeobject using btree(loid oid_ops, pageno int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_largeobject_loid_pn_index, 2683, on pg_largeobject using btree(loid oid_ops, pageno int4_ops));
#define LargeObjectLOidPNIndexId 2683
extern Oid LargeObjectCreate(Oid loid);
diff --git a/src/include/catalog/pg_largeobject_metadata.h b/src/include/catalog/pg_largeobject_metadata.h
index 0d33f5bd19..6b85da15e9 100644
--- a/src/include/catalog/pg_largeobject_metadata.h
+++ b/src/include/catalog/pg_largeobject_metadata.h
@@ -45,7 +45,7 @@ CATALOG(pg_largeobject_metadata,2995,LargeObjectMetadataRelationId)
*/
typedef FormData_pg_largeobject_metadata *Form_pg_largeobject_metadata;
-DECLARE_UNIQUE_INDEX(pg_largeobject_metadata_oid_index, 2996, on pg_largeobject_metadata using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_largeobject_metadata_oid_index, 2996, on pg_largeobject_metadata using btree(oid oid_ops));
#define LargeObjectMetadataOidIndexId 2996
#endif /* PG_LARGEOBJECT_METADATA_H */
diff --git a/src/include/catalog/pg_namespace.h b/src/include/catalog/pg_namespace.h
index 9c3d126644..d5da72bb54 100644
--- a/src/include/catalog/pg_namespace.h
+++ b/src/include/catalog/pg_namespace.h
@@ -55,7 +55,7 @@ DECLARE_TOAST(pg_namespace, 4163, 4164);
DECLARE_UNIQUE_INDEX(pg_namespace_nspname_index, 2684, on pg_namespace using btree(nspname name_ops));
#define NamespaceNameIndexId 2684
-DECLARE_UNIQUE_INDEX(pg_namespace_oid_index, 2685, on pg_namespace using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_namespace_oid_index, 2685, on pg_namespace using btree(oid oid_ops));
#define NamespaceOidIndexId 2685
/*
diff --git a/src/include/catalog/pg_opclass.h b/src/include/catalog/pg_opclass.h
index 446ae52c2e..2d9113f243 100644
--- a/src/include/catalog/pg_opclass.h
+++ b/src/include/catalog/pg_opclass.h
@@ -84,7 +84,7 @@ typedef FormData_pg_opclass *Form_pg_opclass;
DECLARE_UNIQUE_INDEX(pg_opclass_am_name_nsp_index, 2686, on pg_opclass using btree(opcmethod oid_ops, opcname name_ops, opcnamespace oid_ops));
#define OpclassAmNameNspIndexId 2686
-DECLARE_UNIQUE_INDEX(pg_opclass_oid_index, 2687, on pg_opclass using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_opclass_oid_index, 2687, on pg_opclass using btree(oid oid_ops));
#define OpclassOidIndexId 2687
#endif /* PG_OPCLASS_H */
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index bdcd7fcde8..6c2107aa60 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -82,7 +82,7 @@ CATALOG(pg_operator,2617,OperatorRelationId)
*/
typedef FormData_pg_operator *Form_pg_operator;
-DECLARE_UNIQUE_INDEX(pg_operator_oid_index, 2688, on pg_operator using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_operator_oid_index, 2688, on pg_operator using btree(oid oid_ops));
#define OperatorOidIndexId 2688
DECLARE_UNIQUE_INDEX(pg_operator_oprname_l_r_n_index, 2689, on pg_operator using btree(oprname name_ops, oprleft oid_ops, oprright oid_ops, oprnamespace oid_ops));
#define OperatorNameNspIndexId 2689
diff --git a/src/include/catalog/pg_opfamily.h b/src/include/catalog/pg_opfamily.h
index 56fd5d0e6b..5a71e6b249 100644
--- a/src/include/catalog/pg_opfamily.h
+++ b/src/include/catalog/pg_opfamily.h
@@ -52,7 +52,7 @@ typedef FormData_pg_opfamily *Form_pg_opfamily;
DECLARE_UNIQUE_INDEX(pg_opfamily_am_name_nsp_index, 2754, on pg_opfamily using btree(opfmethod oid_ops, opfname name_ops, opfnamespace oid_ops));
#define OpfamilyAmNameNspIndexId 2754
-DECLARE_UNIQUE_INDEX(pg_opfamily_oid_index, 2755, on pg_opfamily using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_opfamily_oid_index, 2755, on pg_opfamily using btree(oid oid_ops));
#define OpfamilyOidIndexId 2755
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_partitioned_table.h b/src/include/catalog/pg_partitioned_table.h
index 45d613d4ec..3fa86d693a 100644
--- a/src/include/catalog/pg_partitioned_table.h
+++ b/src/include/catalog/pg_partitioned_table.h
@@ -66,7 +66,7 @@ typedef FormData_pg_partitioned_table *Form_pg_partitioned_table;
DECLARE_TOAST(pg_partitioned_table, 4165, 4166);
-DECLARE_UNIQUE_INDEX(pg_partitioned_table_partrelid_index, 3351, on pg_partitioned_table using btree(partrelid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_partitioned_table_partrelid_index, 3351, on pg_partitioned_table using btree(partrelid oid_ops));
#define PartitionedRelidIndexId 3351
#endif /* PG_PARTITIONED_TABLE_H */
diff --git a/src/include/catalog/pg_policy.h b/src/include/catalog/pg_policy.h
index b06580a155..7d80344173 100644
--- a/src/include/catalog/pg_policy.h
+++ b/src/include/catalog/pg_policy.h
@@ -51,7 +51,7 @@ typedef FormData_pg_policy *Form_pg_policy;
DECLARE_TOAST(pg_policy, 4167, 4168);
-DECLARE_UNIQUE_INDEX(pg_policy_oid_index, 3257, on pg_policy using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_policy_oid_index, 3257, on pg_policy using btree(oid oid_ops));
#define PolicyOidIndexId 3257
DECLARE_UNIQUE_INDEX(pg_policy_polrelid_polname_index, 3258, on pg_policy using btree(polrelid oid_ops, polname name_ops));
#define PolicyPolrelidPolnameIndexId 3258
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f8e6dea22d..c957199d39 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -134,7 +134,7 @@ typedef FormData_pg_proc *Form_pg_proc;
DECLARE_TOAST(pg_proc, 2836, 2837);
-DECLARE_UNIQUE_INDEX(pg_proc_oid_index, 2690, on pg_proc using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_proc_oid_index, 2690, on pg_proc using btree(oid oid_ops));
#define ProcedureOidIndexId 2690
DECLARE_UNIQUE_INDEX(pg_proc_proname_args_nsp_index, 2691, on pg_proc using btree(proname name_ops, proargtypes oidvector_ops, pronamespace oid_ops));
#define ProcedureNameArgsNspIndexId 2691
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 309d102d7d..45012273c8 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -63,7 +63,7 @@ CATALOG(pg_publication,6104,PublicationRelationId)
*/
typedef FormData_pg_publication *Form_pg_publication;
-DECLARE_UNIQUE_INDEX(pg_publication_oid_index, 6110, on pg_publication using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_publication_oid_index, 6110, on pg_publication using btree(oid oid_ops));
#define PublicationObjectIndexId 6110
DECLARE_UNIQUE_INDEX(pg_publication_pubname_index, 6111, on pg_publication using btree(pubname name_ops));
#define PublicationNameIndexId 6111
diff --git a/src/include/catalog/pg_publication_rel.h b/src/include/catalog/pg_publication_rel.h
index 652cbcd6cb..40256f129f 100644
--- a/src/include/catalog/pg_publication_rel.h
+++ b/src/include/catalog/pg_publication_rel.h
@@ -40,7 +40,7 @@ CATALOG(pg_publication_rel,6106,PublicationRelRelationId)
*/
typedef FormData_pg_publication_rel *Form_pg_publication_rel;
-DECLARE_UNIQUE_INDEX(pg_publication_rel_oid_index, 6112, on pg_publication_rel using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_publication_rel_oid_index, 6112, on pg_publication_rel using btree(oid oid_ops));
#define PublicationRelObjectIndexId 6112
DECLARE_UNIQUE_INDEX(pg_publication_rel_prrelid_prpubid_index, 6113, on pg_publication_rel using btree(prrelid oid_ops, prpubid oid_ops));
#define PublicationRelPrrelidPrpubidIndexId 6113
diff --git a/src/include/catalog/pg_range.h b/src/include/catalog/pg_range.h
index c28bb57b6c..39ad0d3714 100644
--- a/src/include/catalog/pg_range.h
+++ b/src/include/catalog/pg_range.h
@@ -54,7 +54,7 @@ CATALOG(pg_range,3541,RangeRelationId)
*/
typedef FormData_pg_range *Form_pg_range;
-DECLARE_UNIQUE_INDEX(pg_range_rngtypid_index, 3542, on pg_range using btree(rngtypid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_range_rngtypid_index, 3542, on pg_range using btree(rngtypid oid_ops));
#define RangeTypidIndexId 3542
/*
diff --git a/src/include/catalog/pg_replication_origin.h b/src/include/catalog/pg_replication_origin.h
index 4c7d9a1765..668653595d 100644
--- a/src/include/catalog/pg_replication_origin.h
+++ b/src/include/catalog/pg_replication_origin.h
@@ -58,7 +58,7 @@ DECLARE_TOAST(pg_replication_origin, 4181, 4182);
#define PgReplicationOriginToastTable 4181
#define PgReplicationOriginToastIndex 4182
-DECLARE_UNIQUE_INDEX(pg_replication_origin_roiident_index, 6001, on pg_replication_origin using btree(roident oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_replication_origin_roiident_index, 6001, on pg_replication_origin using btree(roident oid_ops));
#define ReplicationOriginIdentIndex 6001
DECLARE_UNIQUE_INDEX(pg_replication_origin_roname_index, 6002, on pg_replication_origin using btree(roname text_ops));
#define ReplicationOriginNameIndex 6002
diff --git a/src/include/catalog/pg_rewrite.h b/src/include/catalog/pg_rewrite.h
index 9b1d8a375a..d86ce1fb1a 100644
--- a/src/include/catalog/pg_rewrite.h
+++ b/src/include/catalog/pg_rewrite.h
@@ -53,7 +53,7 @@ typedef FormData_pg_rewrite *Form_pg_rewrite;
DECLARE_TOAST(pg_rewrite, 2838, 2839);
-DECLARE_UNIQUE_INDEX(pg_rewrite_oid_index, 2692, on pg_rewrite using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_rewrite_oid_index, 2692, on pg_rewrite using btree(oid oid_ops));
#define RewriteOidIndexId 2692
DECLARE_UNIQUE_INDEX(pg_rewrite_rel_rulename_index, 2693, on pg_rewrite using btree(ev_class oid_ops, rulename name_ops));
#define RewriteRelRulenameIndexId 2693
diff --git a/src/include/catalog/pg_sequence.h b/src/include/catalog/pg_sequence.h
index 4ee91cd7e3..b000cb73b1 100644
--- a/src/include/catalog/pg_sequence.h
+++ b/src/include/catalog/pg_sequence.h
@@ -39,7 +39,7 @@ CATALOG(pg_sequence,2224,SequenceRelationId)
*/
typedef FormData_pg_sequence *Form_pg_sequence;
-DECLARE_UNIQUE_INDEX(pg_sequence_seqrelid_index, 5002, on pg_sequence using btree(seqrelid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_sequence_seqrelid_index, 5002, on pg_sequence using btree(seqrelid oid_ops));
#define SequenceRelidIndexId 5002
#endif /* PG_SEQUENCE_H */
diff --git a/src/include/catalog/pg_shdescription.h b/src/include/catalog/pg_shdescription.h
index 81abd8debf..d4d5ee93d3 100644
--- a/src/include/catalog/pg_shdescription.h
+++ b/src/include/catalog/pg_shdescription.h
@@ -59,7 +59,7 @@ DECLARE_TOAST(pg_shdescription, 2846, 2847);
#define PgShdescriptionToastTable 2846
#define PgShdescriptionToastIndex 2847
-DECLARE_UNIQUE_INDEX(pg_shdescription_o_c_index, 2397, on pg_shdescription using btree(objoid oid_ops, classoid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_shdescription_o_c_index, 2397, on pg_shdescription using btree(objoid oid_ops, classoid oid_ops));
#define SharedDescriptionObjIndexId 2397
#endif /* PG_SHDESCRIPTION_H */
diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index fb819734a1..e2191e9ff4 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -135,7 +135,7 @@ typedef FormData_pg_statistic *Form_pg_statistic;
DECLARE_TOAST(pg_statistic, 2840, 2841);
-DECLARE_UNIQUE_INDEX(pg_statistic_relid_att_inh_index, 2696, on pg_statistic using btree(starelid oid_ops, staattnum int2_ops, stainherit bool_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_statistic_relid_att_inh_index, 2696, on pg_statistic using btree(starelid oid_ops, staattnum int2_ops, stainherit bool_ops));
#define StatisticRelidAttnumInhIndexId 2696
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_statistic_ext.h b/src/include/catalog/pg_statistic_ext.h
index 61d402c600..44e0d20b8b 100644
--- a/src/include/catalog/pg_statistic_ext.h
+++ b/src/include/catalog/pg_statistic_ext.h
@@ -65,7 +65,7 @@ typedef FormData_pg_statistic_ext *Form_pg_statistic_ext;
DECLARE_TOAST(pg_statistic_ext, 3439, 3440);
-DECLARE_UNIQUE_INDEX(pg_statistic_ext_oid_index, 3380, on pg_statistic_ext using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_statistic_ext_oid_index, 3380, on pg_statistic_ext using btree(oid oid_ops));
#define StatisticExtOidIndexId 3380
DECLARE_UNIQUE_INDEX(pg_statistic_ext_name_index, 3997, on pg_statistic_ext using btree(stxname name_ops, stxnamespace oid_ops));
#define StatisticExtNameIndexId 3997
diff --git a/src/include/catalog/pg_statistic_ext_data.h b/src/include/catalog/pg_statistic_ext_data.h
index c9515df117..cfe1fc9423 100644
--- a/src/include/catalog/pg_statistic_ext_data.h
+++ b/src/include/catalog/pg_statistic_ext_data.h
@@ -51,7 +51,7 @@ typedef FormData_pg_statistic_ext_data * Form_pg_statistic_ext_data;
DECLARE_TOAST(pg_statistic_ext_data, 3430, 3431);
-DECLARE_UNIQUE_INDEX(pg_statistic_ext_data_stxoid_index, 3433, on pg_statistic_ext_data using btree(stxoid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_statistic_ext_data_stxoid_index, 3433, on pg_statistic_ext_data using btree(stxoid oid_ops));
#define StatisticExtDataStxoidIndexId 3433
#endif /* PG_STATISTIC_EXT_DATA_H */
diff --git a/src/include/catalog/pg_subscription.h b/src/include/catalog/pg_subscription.h
index 3fa02af703..1632a2670a 100644
--- a/src/include/catalog/pg_subscription.h
+++ b/src/include/catalog/pg_subscription.h
@@ -74,7 +74,7 @@ DECLARE_TOAST(pg_subscription, 4183, 4184);
#define PgSubscriptionToastTable 4183
#define PgSubscriptionToastIndex 4184
-DECLARE_UNIQUE_INDEX(pg_subscription_oid_index, 6114, on pg_subscription using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_subscription_oid_index, 6114, on pg_subscription using btree(oid oid_ops));
#define SubscriptionObjectIndexId 6114
DECLARE_UNIQUE_INDEX(pg_subscription_subname_index, 6115, on pg_subscription using btree(subdbid oid_ops, subname name_ops));
#define SubscriptionNameIndexId 6115
diff --git a/src/include/catalog/pg_subscription_rel.h b/src/include/catalog/pg_subscription_rel.h
index acc29265b2..e591a8e9bd 100644
--- a/src/include/catalog/pg_subscription_rel.h
+++ b/src/include/catalog/pg_subscription_rel.h
@@ -49,7 +49,7 @@ CATALOG(pg_subscription_rel,6102,SubscriptionRelRelationId)
typedef FormData_pg_subscription_rel *Form_pg_subscription_rel;
-DECLARE_UNIQUE_INDEX(pg_subscription_rel_srrelid_srsubid_index, 6117, on pg_subscription_rel using btree(srrelid oid_ops, srsubid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_subscription_rel_srrelid_srsubid_index, 6117, on pg_subscription_rel using btree(srrelid oid_ops, srsubid oid_ops));
#define SubscriptionRelSrrelidSrsubidIndexId 6117
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index 9787c75986..3456cef029 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -49,7 +49,7 @@ DECLARE_TOAST(pg_tablespace, 4185, 4186);
#define PgTablespaceToastTable 4185
#define PgTablespaceToastIndex 4186
-DECLARE_UNIQUE_INDEX(pg_tablespace_oid_index, 2697, on pg_tablespace using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_tablespace_oid_index, 2697, on pg_tablespace using btree(oid oid_ops));
#define TablespaceOidIndexId 2697
DECLARE_UNIQUE_INDEX(pg_tablespace_spcname_index, 2698, on pg_tablespace using btree(spcname name_ops));
#define TablespaceNameIndexId 2698
diff --git a/src/include/catalog/pg_transform.h b/src/include/catalog/pg_transform.h
index 8efba2f7e2..e07cc69882 100644
--- a/src/include/catalog/pg_transform.h
+++ b/src/include/catalog/pg_transform.h
@@ -42,7 +42,7 @@ CATALOG(pg_transform,3576,TransformRelationId)
*/
typedef FormData_pg_transform *Form_pg_transform;
-DECLARE_UNIQUE_INDEX(pg_transform_oid_index, 3574, on pg_transform using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_transform_oid_index, 3574, on pg_transform using btree(oid oid_ops));
#define TransformOidIndexId 3574
DECLARE_UNIQUE_INDEX(pg_transform_type_lang_index, 3575, on pg_transform using btree(trftype oid_ops, trflang oid_ops));
#define TransformTypeLangIndexId 3575
diff --git a/src/include/catalog/pg_trigger.h b/src/include/catalog/pg_trigger.h
index abd4bee75f..736365a21e 100644
--- a/src/include/catalog/pg_trigger.h
+++ b/src/include/catalog/pg_trigger.h
@@ -78,7 +78,7 @@ DECLARE_INDEX(pg_trigger_tgconstraint_index, 2699, on pg_trigger using btree(tgc
#define TriggerConstraintIndexId 2699
DECLARE_UNIQUE_INDEX(pg_trigger_tgrelid_tgname_index, 2701, on pg_trigger using btree(tgrelid oid_ops, tgname name_ops));
#define TriggerRelidNameIndexId 2701
-DECLARE_UNIQUE_INDEX(pg_trigger_oid_index, 2702, on pg_trigger using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_trigger_oid_index, 2702, on pg_trigger using btree(oid oid_ops));
#define TriggerOidIndexId 2702
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_ts_config.h b/src/include/catalog/pg_ts_config.h
index 3d83de2e0c..ba4076b3c3 100644
--- a/src/include/catalog/pg_ts_config.h
+++ b/src/include/catalog/pg_ts_config.h
@@ -49,7 +49,7 @@ typedef FormData_pg_ts_config *Form_pg_ts_config;
DECLARE_UNIQUE_INDEX(pg_ts_config_cfgname_index, 3608, on pg_ts_config using btree(cfgname name_ops, cfgnamespace oid_ops));
#define TSConfigNameNspIndexId 3608
-DECLARE_UNIQUE_INDEX(pg_ts_config_oid_index, 3712, on pg_ts_config using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_config_oid_index, 3712, on pg_ts_config using btree(oid oid_ops));
#define TSConfigOidIndexId 3712
#endif /* PG_TS_CONFIG_H */
diff --git a/src/include/catalog/pg_ts_config_map.h b/src/include/catalog/pg_ts_config_map.h
index caa4571095..303a3c223f 100644
--- a/src/include/catalog/pg_ts_config_map.h
+++ b/src/include/catalog/pg_ts_config_map.h
@@ -44,7 +44,7 @@ CATALOG(pg_ts_config_map,3603,TSConfigMapRelationId)
typedef FormData_pg_ts_config_map *Form_pg_ts_config_map;
-DECLARE_UNIQUE_INDEX(pg_ts_config_map_index, 3609, on pg_ts_config_map using btree(mapcfg oid_ops, maptokentype int4_ops, mapseqno int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_config_map_index, 3609, on pg_ts_config_map using btree(mapcfg oid_ops, maptokentype int4_ops, mapseqno int4_ops));
#define TSConfigMapIndexId 3609
#endif /* PG_TS_CONFIG_MAP_H */
diff --git a/src/include/catalog/pg_ts_dict.h b/src/include/catalog/pg_ts_dict.h
index 9af090f4bd..40aab6ad7b 100644
--- a/src/include/catalog/pg_ts_dict.h
+++ b/src/include/catalog/pg_ts_dict.h
@@ -55,7 +55,7 @@ DECLARE_TOAST(pg_ts_dict, 4169, 4170);
DECLARE_UNIQUE_INDEX(pg_ts_dict_dictname_index, 3604, on pg_ts_dict using btree(dictname name_ops, dictnamespace oid_ops));
#define TSDictionaryNameNspIndexId 3604
-DECLARE_UNIQUE_INDEX(pg_ts_dict_oid_index, 3605, on pg_ts_dict using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_dict_oid_index, 3605, on pg_ts_dict using btree(oid oid_ops));
#define TSDictionaryOidIndexId 3605
#endif /* PG_TS_DICT_H */
diff --git a/src/include/catalog/pg_ts_parser.h b/src/include/catalog/pg_ts_parser.h
index 54e57c0db6..6687481379 100644
--- a/src/include/catalog/pg_ts_parser.h
+++ b/src/include/catalog/pg_ts_parser.h
@@ -56,7 +56,7 @@ typedef FormData_pg_ts_parser *Form_pg_ts_parser;
DECLARE_UNIQUE_INDEX(pg_ts_parser_prsname_index, 3606, on pg_ts_parser using btree(prsname name_ops, prsnamespace oid_ops));
#define TSParserNameNspIndexId 3606
-DECLARE_UNIQUE_INDEX(pg_ts_parser_oid_index, 3607, on pg_ts_parser using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_parser_oid_index, 3607, on pg_ts_parser using btree(oid oid_ops));
#define TSParserOidIndexId 3607
#endif /* PG_TS_PARSER_H */
diff --git a/src/include/catalog/pg_ts_template.h b/src/include/catalog/pg_ts_template.h
index 548e4c2088..572fedce9e 100644
--- a/src/include/catalog/pg_ts_template.h
+++ b/src/include/catalog/pg_ts_template.h
@@ -47,7 +47,7 @@ typedef FormData_pg_ts_template *Form_pg_ts_template;
DECLARE_UNIQUE_INDEX(pg_ts_template_tmplname_index, 3766, on pg_ts_template using btree(tmplname name_ops, tmplnamespace oid_ops));
#define TSTemplateNameNspIndexId 3766
-DECLARE_UNIQUE_INDEX(pg_ts_template_oid_index, 3767, on pg_ts_template using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_template_oid_index, 3767, on pg_ts_template using btree(oid oid_ops));
#define TSTemplateOidIndexId 3767
#endif /* PG_TS_TEMPLATE_H */
diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h
index 6099e5f57c..09b2e9e111 100644
--- a/src/include/catalog/pg_type.h
+++ b/src/include/catalog/pg_type.h
@@ -256,7 +256,7 @@ typedef FormData_pg_type *Form_pg_type;
DECLARE_TOAST(pg_type, 4171, 4172);
-DECLARE_UNIQUE_INDEX(pg_type_oid_index, 2703, on pg_type using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_type_oid_index, 2703, on pg_type using btree(oid oid_ops));
#define TypeOidIndexId 2703
DECLARE_UNIQUE_INDEX(pg_type_typname_nsp_index, 2704, on pg_type using btree(typname name_ops, typnamespace oid_ops));
#define TypeNameNspIndexId 2704
diff --git a/src/include/catalog/pg_user_mapping.h b/src/include/catalog/pg_user_mapping.h
index c71d8cdd2a..ef72a09e86 100644
--- a/src/include/catalog/pg_user_mapping.h
+++ b/src/include/catalog/pg_user_mapping.h
@@ -47,7 +47,7 @@ typedef FormData_pg_user_mapping *Form_pg_user_mapping;
DECLARE_TOAST(pg_user_mapping, 4173, 4174);
-DECLARE_UNIQUE_INDEX(pg_user_mapping_oid_index, 174, on pg_user_mapping using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_user_mapping_oid_index, 174, on pg_user_mapping using btree(oid oid_ops));
#define UserMappingOidIndexId 174
DECLARE_UNIQUE_INDEX(pg_user_mapping_user_server_index, 175, on pg_user_mapping using btree(umuser oid_ops, umserver oid_ops));
#define UserMappingUserServerIndexId 175
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..4b2f63a95e 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 CONSTRAINT foo 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,8 @@ 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 CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index;
+NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "pg_namespace_nspname_index" to "foo"
ROLLBACK;
-- write to system catalog table as superuser
BEGIN;
@@ -146,7 +147,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..6bfc896ef9 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 CONSTRAINT foo 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 CONSTRAINT foo 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 d40afeef78..379c526249 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -73,7 +73,6 @@ loop
end if;
end loop;
end$$;
-NOTICE: pg_constraint contains unpinned initdb-created object(s)
NOTICE: pg_database contains unpinned initdb-created object(s)
NOTICE: pg_extension contains unpinned initdb-created object(s)
NOTICE: pg_rewrite contains unpinned initdb-created object(s)
@@ -109,3 +108,33 @@ ORDER BY 1, 2;
pg_largeobject_metadata | lomacl | aclitem[]
(11 rows)
+-- system catalogs without primary keys
+--
+-- Current exceptions:
+-- * pg_depend, pg_shdepend don't have a unique key
+-- * pg_seclabel, pg_shseclabel use a nondefault operator class
+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)
+
+-- system catalog unique indexes not wrapped in a constraint
+-- (There should be none.)
+SELECT relname
+FROM pg_class c JOIN pg_index i ON c.oid = i.indexrelid
+WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'i'
+ AND i.indisunique
+ AND c.oid NOT IN (SELECT conindid FROM pg_constraint)
+ORDER BY 1;
+ relname
+---------
+(0 rows)
+
diff --git a/src/test/regress/sql/misc_sanity.sql b/src/test/regress/sql/misc_sanity.sql
index 3ce32e4725..d3add6a729 100644
--- a/src/test/regress/sql/misc_sanity.sql
+++ b/src/test/regress/sql/misc_sanity.sql
@@ -94,3 +94,25 @@
relkind = 'r' AND
attstorage != 'p'
ORDER BY 1, 2;
+
+
+-- system catalogs without primary keys
+--
+-- Current exceptions:
+-- * pg_depend, pg_shdepend don't have a unique key
+-- * pg_seclabel, pg_shseclabel use a nondefault operator class
+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;
+
+
+-- system catalog unique indexes not wrapped in a constraint
+-- (There should be none.)
+SELECT relname
+FROM pg_class c JOIN pg_index i ON c.oid = i.indexrelid
+WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'i'
+ AND i.indisunique
+ AND c.oid NOT IN (SELECT conindid FROM pg_constraint)
+ORDER BY 1;
--
2.29.2
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
[ v2-0001-Add-primary-keys-and-unique-constraints-to-system.patch ]
I've reviewed this patch. It looks pretty solid to me, with a couple
trivial nits as mentioned below, and one bigger thing that's perhaps
in the category of bikeshedding. Namely, do we really want to prefer
using the OID indexes as the primary keys? In most cases there's some
other index that seems to me to be what a user would think of as the
pkey, for example pg_class_relname_nsp_index for pg_class or
pg_proc_proname_args_nsp_index for pg_proc. Preferring OID where it
exists is a nice simple rule, which has some attractiveness, but the
OID indexes seem to me like a lookup aid rather than the "real" object
identity.
There is something strange going on in the misc_sanity test, as seen by
the test output change
-NOTICE: pg_constraint contains unpinned initdb-created object(s)
Formerly the lowest OID in pg_constraint was that of the
information_schema.cardinal_number_domain_check CHECK constraint,
which is intentionally not pinned. Now the lowest OID is that of
pg_proc_oid_index, which is pinned, hence the output change. I think
that's fine. The idea here is just to notice whether any catalogs have
been missed by setup_depend, and we have now proven that pg_constraint
wasn't missed, so it's cool.
The other catalog that is listed in the expected output, but is not
one of the ones intentionally excluded by setup_depend, is pg_rewrite.
That's because its lowest OID is a rewrite rule for a system view, which
we've intentionally made non-pinned. So that's also fine, but maybe
it'd be worth explaining it in the comment above the DO block.
Anyway, on to the minor nits:
system_constraints.sql should be removed by the maintainer-clean target
in src/backend/catalog/Makefile; perhaps also mention it in the comment
for the clean target. Also I suppose src/tools/msvc/clean.bat needs to
remove it, like it does postgres.bki.
The contents of system_constraints.sql seem pretty randomly ordered,
and I bet the order isn't stable across machines. It would be wise
to sort the entries by name to ensure we don't get inconsistencies
between different builds. (If nothing else, that could complicate
validating tarballs.)
I don't follow why the pg_seclabel, pg_shseclabel indexes aren't made
into pkeys? There's a comment claiming they "use a nondefault operator
class", but that's untrue AFAICS.
regards, tom lane
On Sat, Oct 3, 2020 at 9:27 AM Craig Ringer
<craig.ringer@enterprisedb.com> wrote:
Frankly I think we really need a way to mark FKs to be DISABLED or NOT CHECKED or something and a way to mark them as NOT VALID. Rsther than expecting uses to fiddle with the implementation triggers. But I don't think FKs on system catalogs require that, it's just be cosmetic there.
Not really. I think the idea that users don't or shouldn't ever do
manual DDL on system catalogs is not very plausible, considering that
we suggest such steps in our own release notes.
I don't have any complaint about labelling some of the unique indexes
as primary keys, but I think installing foreign keys that don't really
enforce anything may lead to confusion.
--
Robert Haas
EDB: http://www.enterprisedb.com
Robert Haas <robertmhaas@gmail.com> writes:
I don't have any complaint about labelling some of the unique indexes
as primary keys, but I think installing foreign keys that don't really
enforce anything may lead to confusion.
I'm not sure if I buy the "confusion" argument, but after thinking
about this more I realized that there's a stronger roadblock for
treating catalog interrelationships as SQL foreign keys. Namely,
that we always represent no-reference situations with a zero OID,
whereas it'd have to be NULL to look like a valid foreign-key case.
Changing to real NULLs in those columns would of course break a ton
of C code, so I don't think that's gonna happen.
We could overlay an FK onto OID columns that should never have zero
entries, but that would miss enough of the interesting cases that
I don't find it a very attractive proposal.
So I think we should finish up the project of making real SQL
constraints for the catalog indexes, but the idea of making FK
constraints too looks like it'll end in failure.
The reason I got interested in this right now is the nearby
discussion [1]/messages/by-id/d1f413ff-0e50-413d-910c-bdd9ee9752c1@www.fastmail.com about why findoidjoins misses some catalog
relationships and whether we should fix that and/or make its results
more readily accessible. I'd thought perhaps FK constraint entries
could supersede the need for that tool altogether, but now it seems
like not. I still like the idea of marking OID relationships in the
catalog headers though. Perhaps we should take Joel's suggestion
of a new system catalog more seriously, and have genbki.pl populate
such a catalog from info in the catalog header files.
regards, tom lane
[1]: /messages/by-id/d1f413ff-0e50-413d-910c-bdd9ee9752c1@www.fastmail.com
I wrote:
... I still like the idea of marking OID relationships in the
catalog headers though. Perhaps we should take Joel's suggestion
of a new system catalog more seriously, and have genbki.pl populate
such a catalog from info in the catalog header files.
On second thought, a catalog is overkill; it'd only be useful if the data
could change after initdb, which this data surely cannot. The right way
to expose such info to SQL is with a set-returning function reading a
constant table in the C code, a la pg_get_keywords(). That way takes a
whole lot less infrastructure and is just as useful to SQL code.
[ wanders away wondering about a debug mode in which we actually validate
OIDs inserted into such columns... ]
regards, tom lane
On Mon, Jan 18, 2021, at 19:33, Tom Lane wrote:
On second thought, a catalog is overkill; it'd only be useful if the data
could change after initdb, which this data surely cannot. The right way
to expose such info to SQL is with a set-returning function reading a
constant table in the C code, a la pg_get_keywords(). That way takes a
whole lot less infrastructure and is just as useful to SQL code.
+1
On Sun, 2021-01-17 at 17:07 -0500, Tom Lane wrote:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
[ v2-0001-Add-primary-keys-and-unique-constraints-to-system.patch ]
[...] do we really want to prefer
using the OID indexes as the primary keys? In most cases there's some
other index that seems to me to be what a user would think of as the
pkey, for example pg_class_relname_nsp_index for pg_class or
pg_proc_proname_args_nsp_index for pg_proc. Preferring OID where it
exists is a nice simple rule, which has some attractiveness, but the
OID indexes seem to me like a lookup aid rather than the "real" object
identity.
I disagree. The OID is the real, immutable identity of an object.
The "relname" of a "pg_class" encatalogtry can change any time.
Since there are no foreign keys that reference catalogs, that won't cause
problems, but I still think that primary keys should change as little as
possible.
Yours,
Laurenz Albe
On Mon, Jan 18, 2021, at 18:23, Tom Lane wrote:
I realized that there's a stronger roadblock for
treating catalog interrelationships as SQL foreign keys. Namely,
that we always represent no-reference situations with a zero OID,
whereas it'd have to be NULL to look like a valid foreign-key case.
Another roadblock is perhaps the lack of foreign keys on arrays,
which would be needed by the following references:
SELECT * FROM oidjoins WHERE column_type ~ '(vector|\[\])$' ORDER BY 1,2,3;
table_name | column_name | column_type | ref_table_name | ref_column_name
----------------------+----------------+-------------+----------------+-----------------
pg_constraint | conexclop | oid[] | pg_operator | oid
pg_constraint | conffeqop | oid[] | pg_operator | oid
pg_constraint | confkey | int2[] | pg_attribute | attnum
pg_constraint | conkey | int2[] | pg_attribute | attnum
pg_constraint | conpfeqop | oid[] | pg_operator | oid
pg_constraint | conppeqop | oid[] | pg_operator | oid
pg_extension | extconfig | oid[] | pg_class | oid
pg_index | indclass | oidvector | pg_opclass | oid
pg_index | indcollation | oidvector | pg_collation | oid
pg_index | indkey | int2vector | pg_attribute | attnum
pg_partitioned_table | partattrs | int2vector | pg_attribute | attnum
pg_partitioned_table | partclass | oidvector | pg_opclass | oid
pg_partitioned_table | partcollation | oidvector | pg_collation | oid
pg_policy | polroles | oid[] | pg_authid | oid
pg_proc | proallargtypes | oid[] | pg_type | oid
pg_proc | proargtypes | oidvector | pg_type | oid
pg_statistic_ext | stxkeys | int2vector | pg_attribute | attnum
pg_trigger | tgattr | int2vector | pg_attribute | attnum
(18 rows)
I see there is an old thread with work in this area, "Re: GSoC 2017: Foreign Key Arrays":
/messages/by-id/76a8d3d8-a22e-3299-7c4e-6e115dbf3762@proxel.se
The last message in the thread is from 2018-10-02:
On Tue, 2 Oct, 2018 at 05:13:26AM +0200, Michael Paquier wrote:
On Sat, Aug 11, 2018 at 05:20:57AM +0200, Mark Rofail wrote:
I am still having problems rebasing this patch. I can not figure it out on
my own.Okay, it's been a couple of months since this last email, and nothing
has happened, so I am marking it as returned with feedback.
--
Michael
Personally, I would absolutely *love* FKs on array columns.
I always feel shameful when adding array columns to tables,
when the elements are PKs in some other table.
It's convenient and often the best design,
but it feels dirty knowing there are no FKs to help detect application bugs.
Let's hope the current FKs-on-catalog-discussion can ignite the old Foreign Key Arrays thread.
/Joel
On 1/19/21 11:46 AM, Laurenz Albe wrote:
On Sun, 2021-01-17 at 17:07 -0500, Tom Lane wrote:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
[ v2-0001-Add-primary-keys-and-unique-constraints-to-system.patch ]
[...] do we really want to prefer
using the OID indexes as the primary keys? In most cases there's some
other index that seems to me to be what a user would think of as the
pkey, for example pg_class_relname_nsp_index for pg_class or
pg_proc_proname_args_nsp_index for pg_proc. Preferring OID where it
exists is a nice simple rule, which has some attractiveness, but the
OID indexes seem to me like a lookup aid rather than the "real" object
identity.I disagree. The OID is the real, immutable identity of an object.
The "relname" of a "pg_class" encatalogtry can change any time.
Since there are no foreign keys that reference catalogs, that won't cause
problems, but I still think that primary keys should change as little as
possible.
This might be good advice for systems where the primary key defines the
physical layout of the table, but for postgres's heap there is no reason
not to prefer the natural key of the table.
My vote is with Tom on this one.
--
Vik Fearing
Dear Joel,
I would love to start working on this again, I tried to revive the patch
again in 2019, however, I faced the same problems as last time (detailed in
the thread) and I didn't get the support I needed to continue with this
patch.
Are you willing to help rebase and finally publish this patch?
Best Regards,
Mark Rofail
On Tue, 19 Jan 2021 at 2:22 PM Joel Jacobson <joel@compiler.org> wrote:
Show quoted text
On Mon, Jan 18, 2021, at 18:23, Tom Lane wrote:
I realized that there's a stronger roadblock for
treating catalog interrelationships as SQL foreign keys. Namely,
that we always represent no-reference situations with a zero OID,
whereas it'd have to be NULL to look like a valid foreign-key case.Another roadblock is perhaps the lack of foreign keys on arrays,
which would be needed by the following references:SELECT * FROM oidjoins WHERE column_type ~ '(vector|\[\])$' ORDER BY 1,2,3;
table_name | column_name | column_type | ref_table_name |
ref_column_name----------------------+----------------+-------------+----------------+-----------------
pg_constraint | conexclop | oid[] | pg_operator | oid
pg_constraint | conffeqop | oid[] | pg_operator | oid
pg_constraint | confkey | int2[] | pg_attribute |
attnum
pg_constraint | conkey | int2[] | pg_attribute |
attnum
pg_constraint | conpfeqop | oid[] | pg_operator | oid
pg_constraint | conppeqop | oid[] | pg_operator | oid
pg_extension | extconfig | oid[] | pg_class | oid
pg_index | indclass | oidvector | pg_opclass | oid
pg_index | indcollation | oidvector | pg_collation | oid
pg_index | indkey | int2vector | pg_attribute |
attnum
pg_partitioned_table | partattrs | int2vector | pg_attribute |
attnum
pg_partitioned_table | partclass | oidvector | pg_opclass | oid
pg_partitioned_table | partcollation | oidvector | pg_collation | oid
pg_policy | polroles | oid[] | pg_authid | oid
pg_proc | proallargtypes | oid[] | pg_type | oid
pg_proc | proargtypes | oidvector | pg_type | oid
pg_statistic_ext | stxkeys | int2vector | pg_attribute |
attnum
pg_trigger | tgattr | int2vector | pg_attribute |
attnum
(18 rows)I see there is an old thread with work in this area, "Re: GSoC 2017:
Foreign Key Arrays":/messages/by-id/76a8d3d8-a22e-3299-7c4e-6e115dbf3762@proxel.se
The last message in the thread is from 2018-10-02:
On Tue, 2 Oct, 2018 at 05:13:26AM +0200, Michael Paquier wrote:
On Sat, Aug 11, 2018 at 05:20:57AM +0200, Mark Rofail wrote:
I am still having problems rebasing this patch. I can not figure it outon
my own.
Okay, it's been a couple of months since this last email, and nothing
has happened, so I am marking it as returned with feedback.
--
MichaelPersonally, I would absolutely *love* FKs on array columns.
I always feel shameful when adding array columns to tables,
when the elements are PKs in some other table.
It's convenient and often the best design,
but it feels dirty knowing there are no FKs to help detect application
bugs.Let's hope the current FKs-on-catalog-discussion can ignite the old
Foreign Key Arrays thread./Joel
Laurenz Albe <laurenz.albe@cybertec.at> writes:
On Sun, 2021-01-17 at 17:07 -0500, Tom Lane wrote:
[...] do we really want to prefer
using the OID indexes as the primary keys? In most cases there's some
other index that seems to me to be what a user would think of as the
pkey, for example pg_class_relname_nsp_index for pg_class or
pg_proc_proname_args_nsp_index for pg_proc. Preferring OID where it
exists is a nice simple rule, which has some attractiveness, but the
OID indexes seem to me like a lookup aid rather than the "real" object
identity.
I disagree. The OID is the real, immutable identity of an object.
The "relname" of a "pg_class" encatalogtry can change any time.
Since there are no foreign keys that reference catalogs, that won't cause
problems, but I still think that primary keys should change as little as
possible.
Yeah, there's also the point that the OID is what we use for "foreign
key" references from other catalogs. I don't deny that there are
reasons to think of OID as the pkey. But as long as these constraints
are basically cosmetic, it seemed to me that we should consider the
other approach. I'm not dead set on that, just wanted to discuss it.
regards, tom lane
On Tue, Jan 19, 2021, at 18:25, Mark Rofail wrote:
Dear Joel,
I would love to start working on this again, I tried to revive the patch again in 2019, however, I faced the same problems as >last time (detailed in the thread) and I didn't get the support I needed to continue with this patch.
Are you willing to help rebase and finally publish this patch?
Willing, sure, not perhaps not able,
if there are complex problems that need a deep understanding of code base,
I'm not sure I will be of much help, but I can for sure do testing and reviewing.
/Joel
I'll post tomorrow the latest rebased patch with a summary of the issues.
Let's continue this in the foreign key array thread, as to not clutter this
thread.
Regards, Mark Rofail.
On Tue, Jan 19, 2021, 11:00 PM Joel Jacobson <joel@compiler.org> wrote:
Show quoted text
On Tue, Jan 19, 2021, at 18:25, Mark Rofail wrote:
Dear Joel,
I would love to start working on this again, I tried to revive the patch
again in 2019, however, I faced the same problems as >last time (detailed
in the thread) and I didn't get the support I needed to continue with this
patch.Are you willing to help rebase and finally publish this patch?
Willing, sure, not perhaps not able,
if there are complex problems that need a deep understanding of code base,
I'm not sure I will be of much help, but I can for sure do testing and
reviewing./Joel
On 2021-01-18 00:35, Robert Haas wrote:
I don't have any complaint about labelling some of the unique indexes
as primary keys, but I think installing foreign keys that don't really
enforce anything may lead to confusion.
FWIW, "not enforced" constraints (such as foreign keys) is a feature
that is in the SQL standard and in other SQL implementations.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
On 2021-01-18 18:23, Tom Lane wrote:
The reason I got interested in this right now is the nearby
discussion [1] about why findoidjoins misses some catalog
relationships and whether we should fix that and/or make its results
more readily accessible. I'd thought perhaps FK constraint entries
could supersede the need for that tool altogether, but now it seems
like not.
Yes, catalog consistency checking was also something I had in mind.
There are clearly a number of complications still to resolve. But in
general the idea of representing relationships between tables in the
DDL/schema sounds like a sensible idea.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
On 2021-01-17 23:07, Tom Lane wrote:
I've reviewed this patch. It looks pretty solid to me, with a couple
trivial nits as mentioned below, and one bigger thing that's perhaps
in the category of bikeshedding. Namely, do we really want to prefer
using the OID indexes as the primary keys? In most cases there's some
other index that seems to me to be what a user would think of as the
pkey, for example pg_class_relname_nsp_index for pg_class or
pg_proc_proname_args_nsp_index for pg_proc. Preferring OID where it
exists is a nice simple rule, which has some attractiveness, but the
OID indexes seem to me like a lookup aid rather than the "real" object
identity.
I chose this because the notional foreign keys point to the OID.
If you design some basic business database with customer IDs, product
IDs, etc., you'd also usually make the ID the primary key, even if you
have, say, a unique constraint on the product name. But this is of
course a matter of taste to some degree.
system_constraints.sql should be removed by the maintainer-clean target
in src/backend/catalog/Makefile; perhaps also mention it in the comment
for the clean target. Also I suppose src/tools/msvc/clean.bat needs to
remove it, like it does postgres.bki.
done
The contents of system_constraints.sql seem pretty randomly ordered,
and I bet the order isn't stable across machines. It would be wise
to sort the entries by name to ensure we don't get inconsistencies
between different builds. (If nothing else, that could complicate
validating tarballs.)
They follow the order in which the catalogs are processed byt genbki.pl.
This is the same order in which the catalog data and indexes are
created in postgres.bki. The Perl code to handle each of these is
conceptually the same, so that seems solid. We could order them
differently, but there is also value in keeping the catalog processing
order globally consistent.
I don't follow why the pg_seclabel, pg_shseclabel indexes aren't made
into pkeys? There's a comment claiming they "use a nondefault operator
class", but that's untrue AFAICS.
When I started the patch, it was text_pattern_ops, but that was a long
time ago. ;-) Fixed now.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
Attachments:
v3-0001-Add-primary-keys-and-unique-constraints-to-system.patchtext/plain; charset=UTF-8; name=v3-0001-Add-primary-keys-and-unique-constraints-to-system.patch; x-mac-creator=0; x-mac-type=0Download
From d6e15c6ac1903af5dbe424a34e7ea9ad776517ff Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Thu, 21 Jan 2021 09:47:42 +0100
Subject: [PATCH v3] Add primary keys and unique constraints to system catalogs
For those system catalogs that have a unique indexes, make a primary
key and unique constraint, using ALTER TABLE ... PRIMARY KEY/UNIQUE
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.
The constraint creation statements are automatically created by
genbki.pl from DECLARE_UNIQUE_INDEX directives. To specify which one
of the available unique indexes is the primary key, use the new
directive DECLARE_UNIQUE_INDEX_PKEY instead.
Discussion: https://www.postgresql.org/message-id/flat/dc5f44d9-5ec1-a596-0251-dadadcdede98@2ndquadrant.com
---
src/backend/catalog/.gitignore | 1 +
src/backend/catalog/Catalog.pm | 11 ++--
src/backend/catalog/Makefile | 9 +--
src/backend/catalog/genbki.pl | 21 +++++++
src/bin/initdb/initdb.c | 60 +++++--------------
src/include/catalog/genbki.h | 3 +-
src/include/catalog/pg_aggregate.h | 2 +-
src/include/catalog/pg_am.h | 2 +-
src/include/catalog/pg_amop.h | 2 +-
src/include/catalog/pg_amproc.h | 2 +-
src/include/catalog/pg_attrdef.h | 2 +-
src/include/catalog/pg_attribute.h | 2 +-
src/include/catalog/pg_auth_members.h | 2 +-
src/include/catalog/pg_authid.h | 2 +-
src/include/catalog/pg_cast.h | 2 +-
src/include/catalog/pg_class.h | 2 +-
src/include/catalog/pg_collation.h | 2 +-
src/include/catalog/pg_constraint.h | 2 +-
src/include/catalog/pg_conversion.h | 2 +-
src/include/catalog/pg_database.h | 2 +-
src/include/catalog/pg_db_role_setting.h | 2 +-
src/include/catalog/pg_default_acl.h | 2 +-
src/include/catalog/pg_description.h | 2 +-
src/include/catalog/pg_enum.h | 2 +-
src/include/catalog/pg_event_trigger.h | 2 +-
src/include/catalog/pg_extension.h | 2 +-
src/include/catalog/pg_foreign_data_wrapper.h | 2 +-
src/include/catalog/pg_foreign_server.h | 2 +-
src/include/catalog/pg_foreign_table.h | 2 +-
src/include/catalog/pg_index.h | 2 +-
src/include/catalog/pg_inherits.h | 2 +-
src/include/catalog/pg_init_privs.h | 2 +-
src/include/catalog/pg_language.h | 2 +-
src/include/catalog/pg_largeobject.h | 2 +-
src/include/catalog/pg_largeobject_metadata.h | 2 +-
src/include/catalog/pg_namespace.h | 2 +-
src/include/catalog/pg_opclass.h | 2 +-
src/include/catalog/pg_operator.h | 2 +-
src/include/catalog/pg_opfamily.h | 2 +-
src/include/catalog/pg_partitioned_table.h | 2 +-
src/include/catalog/pg_policy.h | 2 +-
src/include/catalog/pg_proc.h | 2 +-
src/include/catalog/pg_publication.h | 2 +-
src/include/catalog/pg_publication_rel.h | 2 +-
src/include/catalog/pg_range.h | 2 +-
src/include/catalog/pg_replication_origin.h | 2 +-
src/include/catalog/pg_rewrite.h | 2 +-
src/include/catalog/pg_seclabel.h | 2 +-
src/include/catalog/pg_sequence.h | 2 +-
src/include/catalog/pg_shdescription.h | 2 +-
src/include/catalog/pg_shseclabel.h | 2 +-
src/include/catalog/pg_statistic.h | 2 +-
src/include/catalog/pg_statistic_ext.h | 2 +-
src/include/catalog/pg_statistic_ext_data.h | 2 +-
src/include/catalog/pg_subscription.h | 2 +-
src/include/catalog/pg_subscription_rel.h | 2 +-
src/include/catalog/pg_tablespace.h | 2 +-
src/include/catalog/pg_transform.h | 2 +-
src/include/catalog/pg_trigger.h | 2 +-
src/include/catalog/pg_ts_config.h | 2 +-
src/include/catalog/pg_ts_config_map.h | 2 +-
src/include/catalog/pg_ts_dict.h | 2 +-
src/include/catalog/pg_ts_parser.h | 2 +-
src/include/catalog/pg_ts_template.h | 2 +-
src/include/catalog/pg_type.h | 2 +-
src/include/catalog/pg_user_mapping.h | 2 +-
.../expected/alter_system_table.out | 6 +-
.../unsafe_tests/sql/alter_system_table.sql | 5 +-
src/test/regress/expected/misc_sanity.out | 28 ++++++++-
src/test/regress/sql/misc_sanity.sql | 21 +++++++
src/tools/msvc/clean.bat | 1 +
71 files changed, 163 insertions(+), 123 deletions(-)
diff --git a/src/backend/catalog/.gitignore b/src/backend/catalog/.gitignore
index 11e2e52023..4bd3ee9d7f 100644
--- a/src/backend/catalog/.gitignore
+++ b/src/backend/catalog/.gitignore
@@ -1,4 +1,5 @@
/postgres.bki
/schemapg.h
+/system_constraints.sql
/pg_*_d.h
/bki-stamp
diff --git a/src/backend/catalog/Catalog.pm b/src/backend/catalog/Catalog.pm
index a37c327122..061f3d8c21 100644
--- a/src/backend/catalog/Catalog.pm
+++ b/src/backend/catalog/Catalog.pm
@@ -94,14 +94,15 @@ sub ParseHeader
push @{ $catalog{toasting} },
{ parent_table => $1, toast_oid => $2, toast_index_oid => $3 };
}
- elsif (/^DECLARE_(UNIQUE_)?INDEX\(\s*(\w+),\s*(\d+),\s*(.+)\)/)
+ elsif (/^DECLARE_(UNIQUE_)?INDEX(_PKEY)?\(\s*(\w+),\s*(\d+),\s*(.+)\)/)
{
push @{ $catalog{indexing} },
- {
+ {
is_unique => $1 ? 1 : 0,
- index_name => $2,
- index_oid => $3,
- index_decl => $4
+ is_pkey => $2 ? 1 : 0,
+ index_name => $3,
+ index_oid => $4,
+ index_decl => $5
};
}
elsif (/^CATALOG\((\w+),(\d+),(\w+)\)/)
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index c85f0ca7b6..995ddf1285 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -121,6 +121,7 @@ $(top_builddir)/src/include/catalog/header-stamp: bki-stamp
.PHONY: install-data
install-data: bki-stamp installdirs
$(INSTALL_DATA) $(call vpathsearch,postgres.bki) '$(DESTDIR)$(datadir)/postgres.bki'
+ $(INSTALL_DATA) $(call vpathsearch,system_constraints.sql) '$(DESTDIR)$(datadir)/system_constraints.sql'
$(INSTALL_DATA) $(srcdir)/system_views.sql '$(DESTDIR)$(datadir)/system_views.sql'
$(INSTALL_DATA) $(srcdir)/information_schema.sql '$(DESTDIR)$(datadir)/information_schema.sql'
$(INSTALL_DATA) $(srcdir)/sql_features.txt '$(DESTDIR)$(datadir)/sql_features.txt'
@@ -130,11 +131,11 @@ installdirs:
.PHONY: uninstall-data
uninstall-data:
- rm -f $(addprefix '$(DESTDIR)$(datadir)'/, postgres.bki system_views.sql information_schema.sql sql_features.txt)
+ rm -f $(addprefix '$(DESTDIR)$(datadir)'/, postgres.bki system_constraints.sql system_views.sql information_schema.sql sql_features.txt)
-# postgres.bki and the generated headers are in the distribution tarball,
-# so they are not cleaned here.
+# postgres.bki, system_constraints.sql, and the generated headers are
+# in the distribution tarball, so they are not cleaned here.
clean:
maintainer-clean: clean
- rm -f bki-stamp postgres.bki $(GENERATED_HEADERS)
+ rm -f bki-stamp postgres.bki system_constraints.sql $(GENERATED_HEADERS)
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 009e215da1..6747996ce3 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -55,6 +55,7 @@
my @toast_decls;
my @index_decls;
my %oidcounts;
+my @system_constraints;
foreach my $header (@ARGV)
{
@@ -137,6 +138,17 @@
$index->{index_name}, $index->{index_oid},
$index->{index_decl};
$oidcounts{ $index->{index_oid} }++;
+
+ if ($index->{is_unique})
+ {
+ $index->{index_decl} =~ /on (\w+) using/;
+ my $tblname = $1;
+ push @system_constraints,
+ sprintf "ALTER TABLE %s ADD %s USING INDEX %s;",
+ $tblname,
+ $index->{is_pkey} ? "PRIMARY KEY" : "UNIQUE",
+ $index->{index_name};
+ }
}
}
@@ -388,6 +400,9 @@
my $schemafile = $output_path . 'schemapg.h';
open my $schemapg, '>', $schemafile . $tmpext
or die "can't open $schemafile$tmpext: $!";
+my $constraints_file = $output_path . 'system_constraints.sql';
+open my $constraints, '>', $constraints_file . $tmpext
+ or die "can't open $constraints_file$tmpext: $!";
# Generate postgres.bki and pg_*_d.h headers.
@@ -648,6 +663,10 @@
"genbki OID counter reached $GenbkiNextOid, overrunning FirstBootstrapObjectId\n"
if $GenbkiNextOid > $FirstBootstrapObjectId;
+foreach my $c (@system_constraints)
+{
+ print $constraints $c, "\n";
+}
# Now generate schemapg.h
@@ -688,10 +707,12 @@
# We're done emitting data
close $bki;
close $schemapg;
+close $constraints;
# Finally, rename the completed files into place.
Catalog::RenameTempFile($bkifile, $tmpext);
Catalog::RenameTempFile($schemafile, $tmpext);
+Catalog::RenameTempFile($constraints_file, $tmpext);
exit 0;
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index e242a4a5b5..62540a1b37 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -159,6 +159,7 @@ static char *conf_file;
static char *dictionary_file;
static char *info_schema_file;
static char *features_file;
+static char *system_constraints_file;
static char *system_views_file;
static bool success = false;
static bool made_new_pgdata = false;
@@ -251,10 +252,9 @@ static void bootstrap_template1(void);
static void setup_auth(FILE *cmdfd);
static void get_su_pwd(void);
static void setup_depend(FILE *cmdfd);
-static void setup_sysviews(FILE *cmdfd);
+static void setup_run_file(FILE *cmdfd, const char *filename);
static void setup_description(FILE *cmdfd);
static void setup_collation(FILE *cmdfd);
-static void setup_dictionary(FILE *cmdfd);
static void setup_privileges(FILE *cmdfd);
static void set_info_version(void);
static void setup_schema(FILE *cmdfd);
@@ -1600,17 +1600,16 @@ setup_depend(FILE *cmdfd)
}
/*
- * set up system views
+ * Run external file
*/
static void
-setup_sysviews(FILE *cmdfd)
+setup_run_file(FILE *cmdfd, const char *filename)
{
- char **line;
- char **sysviews_setup;
+ char **lines;
- sysviews_setup = readfile(system_views_file);
+ lines = readfile(filename);
- for (line = sysviews_setup; *line != NULL; line++)
+ for (char **line = lines; *line != NULL; line++)
{
PG_CMD_PUTS(*line);
free(*line);
@@ -1618,7 +1617,7 @@ setup_sysviews(FILE *cmdfd)
PG_CMD_PUTS("\n\n");
- free(sysviews_setup);
+ free(lines);
}
/*
@@ -1661,27 +1660,6 @@ setup_collation(FILE *cmdfd)
PG_CMD_PUTS("SELECT pg_import_system_collations('pg_catalog');\n\n");
}
-/*
- * load extra dictionaries (Snowball stemmers)
- */
-static void
-setup_dictionary(FILE *cmdfd)
-{
- char **line;
- char **conv_lines;
-
- conv_lines = readfile(dictionary_file);
- for (line = conv_lines; *line != NULL; line++)
- {
- PG_CMD_PUTS(*line);
- free(*line);
- }
-
- PG_CMD_PUTS("\n\n");
-
- free(conv_lines);
-}
-
/*
* Set up privileges
*
@@ -1882,20 +1860,7 @@ set_info_version(void)
static void
setup_schema(FILE *cmdfd)
{
- char **line;
- char **lines;
-
- lines = readfile(info_schema_file);
-
- for (line = lines; *line != NULL; line++)
- {
- PG_CMD_PUTS(*line);
- free(*line);
- }
-
- PG_CMD_PUTS("\n\n");
-
- free(lines);
+ setup_run_file(cmdfd, info_schema_file);
PG_CMD_PRINTF("UPDATE information_schema.sql_implementation_info "
" SET character_value = '%s' "
@@ -2534,6 +2499,7 @@ setup_data_file_paths(void)
set_input(&dictionary_file, "snowball_create.sql");
set_input(&info_schema_file, "information_schema.sql");
set_input(&features_file, "sql_features.txt");
+ set_input(&system_constraints_file, "system_constraints.sql");
set_input(&system_views_file, "system_views.sql");
if (show_setting || debug)
@@ -2895,6 +2861,8 @@ initialize_data_directory(void)
setup_auth(cmdfd);
+ setup_run_file(cmdfd, system_constraints_file);
+
setup_depend(cmdfd);
/*
@@ -2902,13 +2870,13 @@ initialize_data_directory(void)
* They are all droppable at the whim of the DBA.
*/
- setup_sysviews(cmdfd);
+ setup_run_file(cmdfd, system_views_file);
setup_description(cmdfd);
setup_collation(cmdfd);
- setup_dictionary(cmdfd);
+ setup_run_file(cmdfd, dictionary_file);
setup_privileges(cmdfd);
diff --git a/src/include/catalog/genbki.h b/src/include/catalog/genbki.h
index e26bde6d09..296765d987 100644
--- a/src/include/catalog/genbki.h
+++ b/src/include/catalog/genbki.h
@@ -58,7 +58,7 @@
* These lines processed by genbki.pl to create the statements
* the bootstrap parser will turn into DefineIndex calls.
*
- * The keyword is DECLARE_INDEX or DECLARE_UNIQUE_INDEX. The first two
+ * The keyword is DECLARE_INDEX or DECLARE_UNIQUE_INDEX or DECLARE_UNIQUE_INDEX_PKEY. The first two
* arguments are the index name and OID, the rest is much like a standard
* 'create index' SQL command.
*
@@ -70,6 +70,7 @@
*/
#define DECLARE_INDEX(name,oid,decl) extern int no_such_variable
#define DECLARE_UNIQUE_INDEX(name,oid,decl) extern int no_such_variable
+#define DECLARE_UNIQUE_INDEX_PKEY(name,oid,decl) extern int no_such_variable
/* The following are never defined; they are here only for documentation. */
diff --git a/src/include/catalog/pg_aggregate.h b/src/include/catalog/pg_aggregate.h
index e90c3f847e..8b03cdeea2 100644
--- a/src/include/catalog/pg_aggregate.h
+++ b/src/include/catalog/pg_aggregate.h
@@ -110,7 +110,7 @@ typedef FormData_pg_aggregate *Form_pg_aggregate;
DECLARE_TOAST(pg_aggregate, 4159, 4160);
-DECLARE_UNIQUE_INDEX(pg_aggregate_fnoid_index, 2650, on pg_aggregate using btree(aggfnoid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_aggregate_fnoid_index, 2650, on pg_aggregate using btree(aggfnoid oid_ops));
#define AggregateFnoidIndexId 2650
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_am.h b/src/include/catalog/pg_am.h
index 358f5aac8f..ced86faef8 100644
--- a/src/include/catalog/pg_am.h
+++ b/src/include/catalog/pg_am.h
@@ -49,7 +49,7 @@ typedef FormData_pg_am *Form_pg_am;
DECLARE_UNIQUE_INDEX(pg_am_name_index, 2651, on pg_am using btree(amname name_ops));
#define AmNameIndexId 2651
-DECLARE_UNIQUE_INDEX(pg_am_oid_index, 2652, on pg_am using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_am_oid_index, 2652, on pg_am using btree(oid oid_ops));
#define AmOidIndexId 2652
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index 3ccd75f67b..554fc41497 100644
--- a/src/include/catalog/pg_amop.h
+++ b/src/include/catalog/pg_amop.h
@@ -91,7 +91,7 @@ DECLARE_UNIQUE_INDEX(pg_amop_fam_strat_index, 2653, on pg_amop using btree(amopf
#define AccessMethodStrategyIndexId 2653
DECLARE_UNIQUE_INDEX(pg_amop_opr_fam_index, 2654, on pg_amop using btree(amopopr oid_ops, amoppurpose char_ops, amopfamily oid_ops));
#define AccessMethodOperatorIndexId 2654
-DECLARE_UNIQUE_INDEX(pg_amop_oid_index, 2756, on pg_amop using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_amop_oid_index, 2756, on pg_amop using btree(oid oid_ops));
#define AccessMethodOperatorOidIndexId 2756
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h
index 86f1f25d17..8a727c359a 100644
--- a/src/include/catalog/pg_amproc.h
+++ b/src/include/catalog/pg_amproc.h
@@ -69,7 +69,7 @@ typedef FormData_pg_amproc *Form_pg_amproc;
DECLARE_UNIQUE_INDEX(pg_amproc_fam_proc_index, 2655, on pg_amproc using btree(amprocfamily oid_ops, amproclefttype oid_ops, amprocrighttype oid_ops, amprocnum int2_ops));
#define AccessMethodProcedureIndexId 2655
-DECLARE_UNIQUE_INDEX(pg_amproc_oid_index, 2757, on pg_amproc using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_amproc_oid_index, 2757, on pg_amproc using btree(oid oid_ops));
#define AccessMethodProcedureOidIndexId 2757
#endif /* PG_AMPROC_H */
diff --git a/src/include/catalog/pg_attrdef.h b/src/include/catalog/pg_attrdef.h
index fe3aa28992..03efaaded9 100644
--- a/src/include/catalog/pg_attrdef.h
+++ b/src/include/catalog/pg_attrdef.h
@@ -50,7 +50,7 @@ DECLARE_TOAST(pg_attrdef, 2830, 2831);
DECLARE_UNIQUE_INDEX(pg_attrdef_adrelid_adnum_index, 2656, on pg_attrdef using btree(adrelid oid_ops, adnum int2_ops));
#define AttrDefaultIndexId 2656
-DECLARE_UNIQUE_INDEX(pg_attrdef_oid_index, 2657, on pg_attrdef using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_attrdef_oid_index, 2657, on pg_attrdef using btree(oid oid_ops));
#define AttrDefaultOidIndexId 2657
#endif /* PG_ATTRDEF_H */
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index 059dec3647..ba0efff08c 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -194,7 +194,7 @@ typedef FormData_pg_attribute *Form_pg_attribute;
DECLARE_UNIQUE_INDEX(pg_attribute_relid_attnam_index, 2658, on pg_attribute using btree(attrelid oid_ops, attname name_ops));
#define AttributeRelidNameIndexId 2658
-DECLARE_UNIQUE_INDEX(pg_attribute_relid_attnum_index, 2659, on pg_attribute using btree(attrelid oid_ops, attnum int2_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_attribute_relid_attnum_index, 2659, on pg_attribute using btree(attrelid oid_ops, attnum int2_ops));
#define AttributeRelidNumIndexId 2659
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_auth_members.h b/src/include/catalog/pg_auth_members.h
index fc5a2dced7..e90c395640 100644
--- a/src/include/catalog/pg_auth_members.h
+++ b/src/include/catalog/pg_auth_members.h
@@ -42,7 +42,7 @@ CATALOG(pg_auth_members,1261,AuthMemRelationId) BKI_SHARED_RELATION BKI_ROWTYPE_
*/
typedef FormData_pg_auth_members *Form_pg_auth_members;
-DECLARE_UNIQUE_INDEX(pg_auth_members_role_member_index, 2694, on pg_auth_members using btree(roleid oid_ops, member oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_auth_members_role_member_index, 2694, on pg_auth_members using btree(roleid oid_ops, member oid_ops));
#define AuthMemRoleMemIndexId 2694
DECLARE_UNIQUE_INDEX(pg_auth_members_member_role_index, 2695, on pg_auth_members using btree(member oid_ops, roleid oid_ops));
#define AuthMemMemRoleIndexId 2695
diff --git a/src/include/catalog/pg_authid.h b/src/include/catalog/pg_authid.h
index 4063072cc9..1a5c7a73c7 100644
--- a/src/include/catalog/pg_authid.h
+++ b/src/include/catalog/pg_authid.h
@@ -61,7 +61,7 @@ DECLARE_TOAST(pg_authid, 4175, 4176);
DECLARE_UNIQUE_INDEX(pg_authid_rolname_index, 2676, on pg_authid using btree(rolname name_ops));
#define AuthIdRolnameIndexId 2676
-DECLARE_UNIQUE_INDEX(pg_authid_oid_index, 2677, on pg_authid using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_authid_oid_index, 2677, on pg_authid using btree(oid oid_ops));
#define AuthIdOidIndexId 2677
#endif /* PG_AUTHID_H */
diff --git a/src/include/catalog/pg_cast.h b/src/include/catalog/pg_cast.h
index 03ad900a49..2d36628c20 100644
--- a/src/include/catalog/pg_cast.h
+++ b/src/include/catalog/pg_cast.h
@@ -56,7 +56,7 @@ CATALOG(pg_cast,2605,CastRelationId)
*/
typedef FormData_pg_cast *Form_pg_cast;
-DECLARE_UNIQUE_INDEX(pg_cast_oid_index, 2660, on pg_cast using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_cast_oid_index, 2660, on pg_cast using btree(oid oid_ops));
#define CastOidIndexId 2660
DECLARE_UNIQUE_INDEX(pg_cast_source_target_index, 2661, on pg_cast using btree(castsource oid_ops, casttarget oid_ops));
#define CastSourceTargetIndexId 2661
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index e8dcd15a55..eca306ca98 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -152,7 +152,7 @@ CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,Relat
*/
typedef FormData_pg_class *Form_pg_class;
-DECLARE_UNIQUE_INDEX(pg_class_oid_index, 2662, on pg_class using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_class_oid_index, 2662, on pg_class using btree(oid oid_ops));
#define ClassOidIndexId 2662
DECLARE_UNIQUE_INDEX(pg_class_relname_nsp_index, 2663, on pg_class using btree(relname name_ops, relnamespace oid_ops));
#define ClassNameNspIndexId 2663
diff --git a/src/include/catalog/pg_collation.h b/src/include/catalog/pg_collation.h
index 251c2b9ada..3c496ea914 100644
--- a/src/include/catalog/pg_collation.h
+++ b/src/include/catalog/pg_collation.h
@@ -48,7 +48,7 @@ typedef FormData_pg_collation *Form_pg_collation;
DECLARE_UNIQUE_INDEX(pg_collation_name_enc_nsp_index, 3164, on pg_collation using btree(collname name_ops, collencoding int4_ops, collnamespace oid_ops));
#define CollationNameEncNspIndexId 3164
-DECLARE_UNIQUE_INDEX(pg_collation_oid_index, 3085, on pg_collation using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_collation_oid_index, 3085, on pg_collation using btree(oid oid_ops));
#define CollationOidIndexId 3085
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h
index f3c3df390f..6449937b35 100644
--- a/src/include/catalog/pg_constraint.h
+++ b/src/include/catalog/pg_constraint.h
@@ -161,7 +161,7 @@ DECLARE_UNIQUE_INDEX(pg_constraint_conrelid_contypid_conname_index, 2665, on pg_
#define ConstraintRelidTypidNameIndexId 2665
DECLARE_INDEX(pg_constraint_contypid_index, 2666, on pg_constraint using btree(contypid oid_ops));
#define ConstraintTypidIndexId 2666
-DECLARE_UNIQUE_INDEX(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
#define ConstraintOidIndexId 2667
DECLARE_INDEX(pg_constraint_conparentid_index, 2579, on pg_constraint using btree(conparentid oid_ops));
#define ConstraintParentIndexId 2579
diff --git a/src/include/catalog/pg_conversion.h b/src/include/catalog/pg_conversion.h
index 93dcc35840..b02dfe0c3f 100644
--- a/src/include/catalog/pg_conversion.h
+++ b/src/include/catalog/pg_conversion.h
@@ -64,7 +64,7 @@ DECLARE_UNIQUE_INDEX(pg_conversion_default_index, 2668, on pg_conversion using b
#define ConversionDefaultIndexId 2668
DECLARE_UNIQUE_INDEX(pg_conversion_name_nsp_index, 2669, on pg_conversion using btree(conname name_ops, connamespace oid_ops));
#define ConversionNameNspIndexId 2669
-DECLARE_UNIQUE_INDEX(pg_conversion_oid_index, 2670, on pg_conversion using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_conversion_oid_index, 2670, on pg_conversion using btree(oid oid_ops));
#define ConversionOidIndexId 2670
diff --git a/src/include/catalog/pg_database.h b/src/include/catalog/pg_database.h
index 97691127bf..b7a0b6a381 100644
--- a/src/include/catalog/pg_database.h
+++ b/src/include/catalog/pg_database.h
@@ -86,7 +86,7 @@ DECLARE_TOAST(pg_database, 4177, 4178);
DECLARE_UNIQUE_INDEX(pg_database_datname_index, 2671, on pg_database using btree(datname name_ops));
#define DatabaseNameIndexId 2671
-DECLARE_UNIQUE_INDEX(pg_database_oid_index, 2672, on pg_database using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_database_oid_index, 2672, on pg_database using btree(oid oid_ops));
#define DatabaseOidIndexId 2672
#endif /* PG_DATABASE_H */
diff --git a/src/include/catalog/pg_db_role_setting.h b/src/include/catalog/pg_db_role_setting.h
index d3c006d754..f18819d670 100644
--- a/src/include/catalog/pg_db_role_setting.h
+++ b/src/include/catalog/pg_db_role_setting.h
@@ -47,7 +47,7 @@ DECLARE_TOAST(pg_db_role_setting, 2966, 2967);
#define PgDbRoleSettingToastTable 2966
#define PgDbRoleSettingToastIndex 2967
-DECLARE_UNIQUE_INDEX(pg_db_role_setting_databaseid_rol_index, 2965, on pg_db_role_setting using btree(setdatabase oid_ops, setrole oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_db_role_setting_databaseid_rol_index, 2965, on pg_db_role_setting using btree(setdatabase oid_ops, setrole oid_ops));
#define DbRoleSettingDatidRolidIndexId 2965
/*
diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h
index 0f3974c32c..bb7db32cd6 100644
--- a/src/include/catalog/pg_default_acl.h
+++ b/src/include/catalog/pg_default_acl.h
@@ -51,7 +51,7 @@ DECLARE_TOAST(pg_default_acl, 4143, 4144);
DECLARE_UNIQUE_INDEX(pg_default_acl_role_nsp_obj_index, 827, on pg_default_acl using btree(defaclrole oid_ops, defaclnamespace oid_ops, defaclobjtype char_ops));
#define DefaultAclRoleNspObjIndexId 827
-DECLARE_UNIQUE_INDEX(pg_default_acl_oid_index, 828, on pg_default_acl using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_default_acl_oid_index, 828, on pg_default_acl using btree(oid oid_ops));
#define DefaultAclOidIndexId 828
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_description.h b/src/include/catalog/pg_description.h
index 0002643a76..ad9de5e0a0 100644
--- a/src/include/catalog/pg_description.h
+++ b/src/include/catalog/pg_description.h
@@ -65,7 +65,7 @@ typedef FormData_pg_description * Form_pg_description;
DECLARE_TOAST(pg_description, 2834, 2835);
-DECLARE_UNIQUE_INDEX(pg_description_o_c_o_index, 2675, on pg_description using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_description_o_c_o_index, 2675, on pg_description using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops));
#define DescriptionObjIndexId 2675
#endif /* PG_DESCRIPTION_H */
diff --git a/src/include/catalog/pg_enum.h b/src/include/catalog/pg_enum.h
index 3ae7a03d4e..5eaf70772c 100644
--- a/src/include/catalog/pg_enum.h
+++ b/src/include/catalog/pg_enum.h
@@ -43,7 +43,7 @@ CATALOG(pg_enum,3501,EnumRelationId)
*/
typedef FormData_pg_enum *Form_pg_enum;
-DECLARE_UNIQUE_INDEX(pg_enum_oid_index, 3502, on pg_enum using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_enum_oid_index, 3502, on pg_enum using btree(oid oid_ops));
#define EnumOidIndexId 3502
DECLARE_UNIQUE_INDEX(pg_enum_typid_label_index, 3503, on pg_enum using btree(enumtypid oid_ops, enumlabel name_ops));
#define EnumTypIdLabelIndexId 3503
diff --git a/src/include/catalog/pg_event_trigger.h b/src/include/catalog/pg_event_trigger.h
index 4011654969..6f0266ed0f 100644
--- a/src/include/catalog/pg_event_trigger.h
+++ b/src/include/catalog/pg_event_trigger.h
@@ -52,7 +52,7 @@ DECLARE_TOAST(pg_event_trigger, 4145, 4146);
DECLARE_UNIQUE_INDEX(pg_event_trigger_evtname_index, 3467, on pg_event_trigger using btree(evtname name_ops));
#define EventTriggerNameIndexId 3467
-DECLARE_UNIQUE_INDEX(pg_event_trigger_oid_index, 3468, on pg_event_trigger using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_event_trigger_oid_index, 3468, on pg_event_trigger using btree(oid oid_ops));
#define EventTriggerOidIndexId 3468
#endif /* PG_EVENT_TRIGGER_H */
diff --git a/src/include/catalog/pg_extension.h b/src/include/catalog/pg_extension.h
index f7d640fac6..af119bfea7 100644
--- a/src/include/catalog/pg_extension.h
+++ b/src/include/catalog/pg_extension.h
@@ -51,7 +51,7 @@ typedef FormData_pg_extension *Form_pg_extension;
DECLARE_TOAST(pg_extension, 4147, 4148);
-DECLARE_UNIQUE_INDEX(pg_extension_oid_index, 3080, on pg_extension using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_extension_oid_index, 3080, on pg_extension using btree(oid oid_ops));
#define ExtensionOidIndexId 3080
DECLARE_UNIQUE_INDEX(pg_extension_name_index, 3081, on pg_extension using btree(extname name_ops));
#define ExtensionNameIndexId 3081
diff --git a/src/include/catalog/pg_foreign_data_wrapper.h b/src/include/catalog/pg_foreign_data_wrapper.h
index ca73dc280c..0f523a26b9 100644
--- a/src/include/catalog/pg_foreign_data_wrapper.h
+++ b/src/include/catalog/pg_foreign_data_wrapper.h
@@ -49,7 +49,7 @@ typedef FormData_pg_foreign_data_wrapper *Form_pg_foreign_data_wrapper;
DECLARE_TOAST(pg_foreign_data_wrapper, 4149, 4150);
-DECLARE_UNIQUE_INDEX(pg_foreign_data_wrapper_oid_index, 112, on pg_foreign_data_wrapper using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_foreign_data_wrapper_oid_index, 112, on pg_foreign_data_wrapper using btree(oid oid_ops));
#define ForeignDataWrapperOidIndexId 112
DECLARE_UNIQUE_INDEX(pg_foreign_data_wrapper_name_index, 548, on pg_foreign_data_wrapper using btree(fdwname name_ops));
#define ForeignDataWrapperNameIndexId 548
diff --git a/src/include/catalog/pg_foreign_server.h b/src/include/catalog/pg_foreign_server.h
index 3a68f444e7..385b896e97 100644
--- a/src/include/catalog/pg_foreign_server.h
+++ b/src/include/catalog/pg_foreign_server.h
@@ -49,7 +49,7 @@ typedef FormData_pg_foreign_server *Form_pg_foreign_server;
DECLARE_TOAST(pg_foreign_server, 4151, 4152);
-DECLARE_UNIQUE_INDEX(pg_foreign_server_oid_index, 113, on pg_foreign_server using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_foreign_server_oid_index, 113, on pg_foreign_server using btree(oid oid_ops));
#define ForeignServerOidIndexId 113
DECLARE_UNIQUE_INDEX(pg_foreign_server_name_index, 549, on pg_foreign_server using btree(srvname name_ops));
#define ForeignServerNameIndexId 549
diff --git a/src/include/catalog/pg_foreign_table.h b/src/include/catalog/pg_foreign_table.h
index c761852bb9..24f7f2998e 100644
--- a/src/include/catalog/pg_foreign_table.h
+++ b/src/include/catalog/pg_foreign_table.h
@@ -44,7 +44,7 @@ typedef FormData_pg_foreign_table *Form_pg_foreign_table;
DECLARE_TOAST(pg_foreign_table, 4153, 4154);
-DECLARE_UNIQUE_INDEX(pg_foreign_table_relid_index, 3119, on pg_foreign_table using btree(ftrelid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_foreign_table_relid_index, 3119, on pg_foreign_table using btree(ftrelid oid_ops));
#define ForeignTableRelidIndexId 3119
#endif /* PG_FOREIGN_TABLE_H */
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index b6d7ebec55..1a7aef18ce 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -69,7 +69,7 @@ typedef FormData_pg_index *Form_pg_index;
DECLARE_INDEX(pg_index_indrelid_index, 2678, on pg_index using btree(indrelid oid_ops));
#define IndexIndrelidIndexId 2678
-DECLARE_UNIQUE_INDEX(pg_index_indexrelid_index, 2679, on pg_index using btree(indexrelid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_index_indexrelid_index, 2679, on pg_index using btree(indexrelid oid_ops));
#define IndexRelidIndexId 2679
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_inherits.h b/src/include/catalog/pg_inherits.h
index a0f2ec4e0b..b8147796d8 100644
--- a/src/include/catalog/pg_inherits.h
+++ b/src/include/catalog/pg_inherits.h
@@ -43,7 +43,7 @@ CATALOG(pg_inherits,2611,InheritsRelationId)
*/
typedef FormData_pg_inherits *Form_pg_inherits;
-DECLARE_UNIQUE_INDEX(pg_inherits_relid_seqno_index, 2680, on pg_inherits using btree(inhrelid oid_ops, inhseqno int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_inherits_relid_seqno_index, 2680, on pg_inherits using btree(inhrelid oid_ops, inhseqno int4_ops));
#define InheritsRelidSeqnoIndexId 2680
DECLARE_INDEX(pg_inherits_parent_index, 2187, on pg_inherits using btree(inhparent oid_ops));
#define InheritsParentIndexId 2187
diff --git a/src/include/catalog/pg_init_privs.h b/src/include/catalog/pg_init_privs.h
index da1fa5b279..983b1857c0 100644
--- a/src/include/catalog/pg_init_privs.h
+++ b/src/include/catalog/pg_init_privs.h
@@ -64,7 +64,7 @@ typedef FormData_pg_init_privs * Form_pg_init_privs;
DECLARE_TOAST(pg_init_privs, 4155, 4156);
-DECLARE_UNIQUE_INDEX(pg_init_privs_o_c_o_index, 3395, on pg_init_privs using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_init_privs_o_c_o_index, 3395, on pg_init_privs using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops));
#define InitPrivsObjIndexId 3395
/*
diff --git a/src/include/catalog/pg_language.h b/src/include/catalog/pg_language.h
index ffab93eb6a..b1dcd0a4f5 100644
--- a/src/include/catalog/pg_language.h
+++ b/src/include/catalog/pg_language.h
@@ -68,7 +68,7 @@ DECLARE_TOAST(pg_language, 4157, 4158);
DECLARE_UNIQUE_INDEX(pg_language_name_index, 2681, on pg_language using btree(lanname name_ops));
#define LanguageNameIndexId 2681
-DECLARE_UNIQUE_INDEX(pg_language_oid_index, 2682, on pg_language using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_language_oid_index, 2682, on pg_language using btree(oid oid_ops));
#define LanguageOidIndexId 2682
#endif /* PG_LANGUAGE_H */
diff --git a/src/include/catalog/pg_largeobject.h b/src/include/catalog/pg_largeobject.h
index 80b1cbae83..f453319322 100644
--- a/src/include/catalog/pg_largeobject.h
+++ b/src/include/catalog/pg_largeobject.h
@@ -43,7 +43,7 @@ CATALOG(pg_largeobject,2613,LargeObjectRelationId)
*/
typedef FormData_pg_largeobject *Form_pg_largeobject;
-DECLARE_UNIQUE_INDEX(pg_largeobject_loid_pn_index, 2683, on pg_largeobject using btree(loid oid_ops, pageno int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_largeobject_loid_pn_index, 2683, on pg_largeobject using btree(loid oid_ops, pageno int4_ops));
#define LargeObjectLOidPNIndexId 2683
extern Oid LargeObjectCreate(Oid loid);
diff --git a/src/include/catalog/pg_largeobject_metadata.h b/src/include/catalog/pg_largeobject_metadata.h
index b1504a22fa..220988b0ad 100644
--- a/src/include/catalog/pg_largeobject_metadata.h
+++ b/src/include/catalog/pg_largeobject_metadata.h
@@ -45,7 +45,7 @@ CATALOG(pg_largeobject_metadata,2995,LargeObjectMetadataRelationId)
*/
typedef FormData_pg_largeobject_metadata *Form_pg_largeobject_metadata;
-DECLARE_UNIQUE_INDEX(pg_largeobject_metadata_oid_index, 2996, on pg_largeobject_metadata using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_largeobject_metadata_oid_index, 2996, on pg_largeobject_metadata using btree(oid oid_ops));
#define LargeObjectMetadataOidIndexId 2996
#endif /* PG_LARGEOBJECT_METADATA_H */
diff --git a/src/include/catalog/pg_namespace.h b/src/include/catalog/pg_namespace.h
index 5710c37d78..0a68958b1c 100644
--- a/src/include/catalog/pg_namespace.h
+++ b/src/include/catalog/pg_namespace.h
@@ -55,7 +55,7 @@ DECLARE_TOAST(pg_namespace, 4163, 4164);
DECLARE_UNIQUE_INDEX(pg_namespace_nspname_index, 2684, on pg_namespace using btree(nspname name_ops));
#define NamespaceNameIndexId 2684
-DECLARE_UNIQUE_INDEX(pg_namespace_oid_index, 2685, on pg_namespace using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_namespace_oid_index, 2685, on pg_namespace using btree(oid oid_ops));
#define NamespaceOidIndexId 2685
/*
diff --git a/src/include/catalog/pg_opclass.h b/src/include/catalog/pg_opclass.h
index 7836d56c3f..d132df1f2f 100644
--- a/src/include/catalog/pg_opclass.h
+++ b/src/include/catalog/pg_opclass.h
@@ -84,7 +84,7 @@ typedef FormData_pg_opclass *Form_pg_opclass;
DECLARE_UNIQUE_INDEX(pg_opclass_am_name_nsp_index, 2686, on pg_opclass using btree(opcmethod oid_ops, opcname name_ops, opcnamespace oid_ops));
#define OpclassAmNameNspIndexId 2686
-DECLARE_UNIQUE_INDEX(pg_opclass_oid_index, 2687, on pg_opclass using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_opclass_oid_index, 2687, on pg_opclass using btree(oid oid_ops));
#define OpclassOidIndexId 2687
#endif /* PG_OPCLASS_H */
diff --git a/src/include/catalog/pg_operator.h b/src/include/catalog/pg_operator.h
index 05a3fe1815..3ca57e7c1b 100644
--- a/src/include/catalog/pg_operator.h
+++ b/src/include/catalog/pg_operator.h
@@ -82,7 +82,7 @@ CATALOG(pg_operator,2617,OperatorRelationId)
*/
typedef FormData_pg_operator *Form_pg_operator;
-DECLARE_UNIQUE_INDEX(pg_operator_oid_index, 2688, on pg_operator using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_operator_oid_index, 2688, on pg_operator using btree(oid oid_ops));
#define OperatorOidIndexId 2688
DECLARE_UNIQUE_INDEX(pg_operator_oprname_l_r_n_index, 2689, on pg_operator using btree(oprname name_ops, oprleft oid_ops, oprright oid_ops, oprnamespace oid_ops));
#define OperatorNameNspIndexId 2689
diff --git a/src/include/catalog/pg_opfamily.h b/src/include/catalog/pg_opfamily.h
index c00eda0785..18385a6fd6 100644
--- a/src/include/catalog/pg_opfamily.h
+++ b/src/include/catalog/pg_opfamily.h
@@ -52,7 +52,7 @@ typedef FormData_pg_opfamily *Form_pg_opfamily;
DECLARE_UNIQUE_INDEX(pg_opfamily_am_name_nsp_index, 2754, on pg_opfamily using btree(opfmethod oid_ops, opfname name_ops, opfnamespace oid_ops));
#define OpfamilyAmNameNspIndexId 2754
-DECLARE_UNIQUE_INDEX(pg_opfamily_oid_index, 2755, on pg_opfamily using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_opfamily_oid_index, 2755, on pg_opfamily using btree(oid oid_ops));
#define OpfamilyOidIndexId 2755
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_partitioned_table.h b/src/include/catalog/pg_partitioned_table.h
index f51d7e16bf..038730b005 100644
--- a/src/include/catalog/pg_partitioned_table.h
+++ b/src/include/catalog/pg_partitioned_table.h
@@ -66,7 +66,7 @@ typedef FormData_pg_partitioned_table *Form_pg_partitioned_table;
DECLARE_TOAST(pg_partitioned_table, 4165, 4166);
-DECLARE_UNIQUE_INDEX(pg_partitioned_table_partrelid_index, 3351, on pg_partitioned_table using btree(partrelid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_partitioned_table_partrelid_index, 3351, on pg_partitioned_table using btree(partrelid oid_ops));
#define PartitionedRelidIndexId 3351
#endif /* PG_PARTITIONED_TABLE_H */
diff --git a/src/include/catalog/pg_policy.h b/src/include/catalog/pg_policy.h
index a5af6eb352..44197613e0 100644
--- a/src/include/catalog/pg_policy.h
+++ b/src/include/catalog/pg_policy.h
@@ -51,7 +51,7 @@ typedef FormData_pg_policy *Form_pg_policy;
DECLARE_TOAST(pg_policy, 4167, 4168);
-DECLARE_UNIQUE_INDEX(pg_policy_oid_index, 3257, on pg_policy using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_policy_oid_index, 3257, on pg_policy using btree(oid oid_ops));
#define PolicyOidIndexId 3257
DECLARE_UNIQUE_INDEX(pg_policy_polrelid_polname_index, 3258, on pg_policy using btree(polrelid oid_ops, polname name_ops));
#define PolicyPolrelidPolnameIndexId 3258
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 1c2551c932..03c8bef422 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -134,7 +134,7 @@ typedef FormData_pg_proc *Form_pg_proc;
DECLARE_TOAST(pg_proc, 2836, 2837);
-DECLARE_UNIQUE_INDEX(pg_proc_oid_index, 2690, on pg_proc using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_proc_oid_index, 2690, on pg_proc using btree(oid oid_ops));
#define ProcedureOidIndexId 2690
DECLARE_UNIQUE_INDEX(pg_proc_proname_args_nsp_index, 2691, on pg_proc using btree(proname name_ops, proargtypes oidvector_ops, pronamespace oid_ops));
#define ProcedureNameArgsNspIndexId 2691
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 0dd50fe428..4127611f5a 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -63,7 +63,7 @@ CATALOG(pg_publication,6104,PublicationRelationId)
*/
typedef FormData_pg_publication *Form_pg_publication;
-DECLARE_UNIQUE_INDEX(pg_publication_oid_index, 6110, on pg_publication using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_publication_oid_index, 6110, on pg_publication using btree(oid oid_ops));
#define PublicationObjectIndexId 6110
DECLARE_UNIQUE_INDEX(pg_publication_pubname_index, 6111, on pg_publication using btree(pubname name_ops));
#define PublicationNameIndexId 6111
diff --git a/src/include/catalog/pg_publication_rel.h b/src/include/catalog/pg_publication_rel.h
index 6e6ec6444d..c79b7fb487 100644
--- a/src/include/catalog/pg_publication_rel.h
+++ b/src/include/catalog/pg_publication_rel.h
@@ -40,7 +40,7 @@ CATALOG(pg_publication_rel,6106,PublicationRelRelationId)
*/
typedef FormData_pg_publication_rel *Form_pg_publication_rel;
-DECLARE_UNIQUE_INDEX(pg_publication_rel_oid_index, 6112, on pg_publication_rel using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_publication_rel_oid_index, 6112, on pg_publication_rel using btree(oid oid_ops));
#define PublicationRelObjectIndexId 6112
DECLARE_UNIQUE_INDEX(pg_publication_rel_prrelid_prpubid_index, 6113, on pg_publication_rel using btree(prrelid oid_ops, prpubid oid_ops));
#define PublicationRelPrrelidPrpubidIndexId 6113
diff --git a/src/include/catalog/pg_range.h b/src/include/catalog/pg_range.h
index f0c3897184..2ec6a4b126 100644
--- a/src/include/catalog/pg_range.h
+++ b/src/include/catalog/pg_range.h
@@ -57,7 +57,7 @@ CATALOG(pg_range,3541,RangeRelationId)
*/
typedef FormData_pg_range *Form_pg_range;
-DECLARE_UNIQUE_INDEX(pg_range_rngtypid_index, 3542, on pg_range using btree(rngtypid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_range_rngtypid_index, 3542, on pg_range using btree(rngtypid oid_ops));
#define RangeTypidIndexId 3542
DECLARE_UNIQUE_INDEX(pg_range_rngmultitypid_index, 2228, on pg_range using btree(rngmultitypid oid_ops));
diff --git a/src/include/catalog/pg_replication_origin.h b/src/include/catalog/pg_replication_origin.h
index bd44968f88..184f2403ce 100644
--- a/src/include/catalog/pg_replication_origin.h
+++ b/src/include/catalog/pg_replication_origin.h
@@ -58,7 +58,7 @@ DECLARE_TOAST(pg_replication_origin, 4181, 4182);
#define PgReplicationOriginToastTable 4181
#define PgReplicationOriginToastIndex 4182
-DECLARE_UNIQUE_INDEX(pg_replication_origin_roiident_index, 6001, on pg_replication_origin using btree(roident oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_replication_origin_roiident_index, 6001, on pg_replication_origin using btree(roident oid_ops));
#define ReplicationOriginIdentIndex 6001
DECLARE_UNIQUE_INDEX(pg_replication_origin_roname_index, 6002, on pg_replication_origin using btree(roname text_ops));
#define ReplicationOriginNameIndex 6002
diff --git a/src/include/catalog/pg_rewrite.h b/src/include/catalog/pg_rewrite.h
index 61615cea21..36f92b1cf1 100644
--- a/src/include/catalog/pg_rewrite.h
+++ b/src/include/catalog/pg_rewrite.h
@@ -53,7 +53,7 @@ typedef FormData_pg_rewrite *Form_pg_rewrite;
DECLARE_TOAST(pg_rewrite, 2838, 2839);
-DECLARE_UNIQUE_INDEX(pg_rewrite_oid_index, 2692, on pg_rewrite using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_rewrite_oid_index, 2692, on pg_rewrite using btree(oid oid_ops));
#define RewriteOidIndexId 2692
DECLARE_UNIQUE_INDEX(pg_rewrite_rel_rulename_index, 2693, on pg_rewrite using btree(ev_class oid_ops, rulename name_ops));
#define RewriteRelRulenameIndexId 2693
diff --git a/src/include/catalog/pg_seclabel.h b/src/include/catalog/pg_seclabel.h
index caf67ab760..b14fd7febe 100644
--- a/src/include/catalog/pg_seclabel.h
+++ b/src/include/catalog/pg_seclabel.h
@@ -39,7 +39,7 @@ CATALOG(pg_seclabel,3596,SecLabelRelationId)
DECLARE_TOAST(pg_seclabel, 3598, 3599);
-DECLARE_UNIQUE_INDEX(pg_seclabel_object_index, 3597, on pg_seclabel using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops, provider text_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_seclabel_object_index, 3597, on pg_seclabel using btree(objoid oid_ops, classoid oid_ops, objsubid int4_ops, provider text_ops));
#define SecLabelObjectIndexId 3597
#endif /* PG_SECLABEL_H */
diff --git a/src/include/catalog/pg_sequence.h b/src/include/catalog/pg_sequence.h
index 61fe5a1930..addf21abce 100644
--- a/src/include/catalog/pg_sequence.h
+++ b/src/include/catalog/pg_sequence.h
@@ -39,7 +39,7 @@ CATALOG(pg_sequence,2224,SequenceRelationId)
*/
typedef FormData_pg_sequence *Form_pg_sequence;
-DECLARE_UNIQUE_INDEX(pg_sequence_seqrelid_index, 5002, on pg_sequence using btree(seqrelid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_sequence_seqrelid_index, 5002, on pg_sequence using btree(seqrelid oid_ops));
#define SequenceRelidIndexId 5002
#endif /* PG_SEQUENCE_H */
diff --git a/src/include/catalog/pg_shdescription.h b/src/include/catalog/pg_shdescription.h
index 5ecb95c00c..a37db4fa0b 100644
--- a/src/include/catalog/pg_shdescription.h
+++ b/src/include/catalog/pg_shdescription.h
@@ -59,7 +59,7 @@ DECLARE_TOAST(pg_shdescription, 2846, 2847);
#define PgShdescriptionToastTable 2846
#define PgShdescriptionToastIndex 2847
-DECLARE_UNIQUE_INDEX(pg_shdescription_o_c_index, 2397, on pg_shdescription using btree(objoid oid_ops, classoid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_shdescription_o_c_index, 2397, on pg_shdescription using btree(objoid oid_ops, classoid oid_ops));
#define SharedDescriptionObjIndexId 2397
#endif /* PG_SHDESCRIPTION_H */
diff --git a/src/include/catalog/pg_shseclabel.h b/src/include/catalog/pg_shseclabel.h
index dd89003169..406f5328a7 100644
--- a/src/include/catalog/pg_shseclabel.h
+++ b/src/include/catalog/pg_shseclabel.h
@@ -42,7 +42,7 @@ DECLARE_TOAST(pg_shseclabel, 4060, 4061);
#define PgShseclabelToastTable 4060
#define PgShseclabelToastIndex 4061
-DECLARE_UNIQUE_INDEX(pg_shseclabel_object_index, 3593, on pg_shseclabel using btree(objoid oid_ops, classoid oid_ops, provider text_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_shseclabel_object_index, 3593, on pg_shseclabel using btree(objoid oid_ops, classoid oid_ops, provider text_ops));
#define SharedSecLabelObjectIndexId 3593
#endif /* PG_SHSECLABEL_H */
diff --git a/src/include/catalog/pg_statistic.h b/src/include/catalog/pg_statistic.h
index 3f1534c29a..4a66bda879 100644
--- a/src/include/catalog/pg_statistic.h
+++ b/src/include/catalog/pg_statistic.h
@@ -135,7 +135,7 @@ typedef FormData_pg_statistic *Form_pg_statistic;
DECLARE_TOAST(pg_statistic, 2840, 2841);
-DECLARE_UNIQUE_INDEX(pg_statistic_relid_att_inh_index, 2696, on pg_statistic using btree(starelid oid_ops, staattnum int2_ops, stainherit bool_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_statistic_relid_att_inh_index, 2696, on pg_statistic using btree(starelid oid_ops, staattnum int2_ops, stainherit bool_ops));
#define StatisticRelidAttnumInhIndexId 2696
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_statistic_ext.h b/src/include/catalog/pg_statistic_ext.h
index 99f6cea0a5..10f52f912c 100644
--- a/src/include/catalog/pg_statistic_ext.h
+++ b/src/include/catalog/pg_statistic_ext.h
@@ -65,7 +65,7 @@ typedef FormData_pg_statistic_ext *Form_pg_statistic_ext;
DECLARE_TOAST(pg_statistic_ext, 3439, 3440);
-DECLARE_UNIQUE_INDEX(pg_statistic_ext_oid_index, 3380, on pg_statistic_ext using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_statistic_ext_oid_index, 3380, on pg_statistic_ext using btree(oid oid_ops));
#define StatisticExtOidIndexId 3380
DECLARE_UNIQUE_INDEX(pg_statistic_ext_name_index, 3997, on pg_statistic_ext using btree(stxname name_ops, stxnamespace oid_ops));
#define StatisticExtNameIndexId 3997
diff --git a/src/include/catalog/pg_statistic_ext_data.h b/src/include/catalog/pg_statistic_ext_data.h
index e0aa152f7b..6f7a36c141 100644
--- a/src/include/catalog/pg_statistic_ext_data.h
+++ b/src/include/catalog/pg_statistic_ext_data.h
@@ -51,7 +51,7 @@ typedef FormData_pg_statistic_ext_data * Form_pg_statistic_ext_data;
DECLARE_TOAST(pg_statistic_ext_data, 3430, 3431);
-DECLARE_UNIQUE_INDEX(pg_statistic_ext_data_stxoid_index, 3433, on pg_statistic_ext_data using btree(stxoid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_statistic_ext_data_stxoid_index, 3433, on pg_statistic_ext_data using btree(stxoid oid_ops));
#define StatisticExtDataStxoidIndexId 3433
#endif /* PG_STATISTIC_EXT_DATA_H */
diff --git a/src/include/catalog/pg_subscription.h b/src/include/catalog/pg_subscription.h
index e3618028f7..4e44c29149 100644
--- a/src/include/catalog/pg_subscription.h
+++ b/src/include/catalog/pg_subscription.h
@@ -74,7 +74,7 @@ DECLARE_TOAST(pg_subscription, 4183, 4184);
#define PgSubscriptionToastTable 4183
#define PgSubscriptionToastIndex 4184
-DECLARE_UNIQUE_INDEX(pg_subscription_oid_index, 6114, on pg_subscription using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_subscription_oid_index, 6114, on pg_subscription using btree(oid oid_ops));
#define SubscriptionObjectIndexId 6114
DECLARE_UNIQUE_INDEX(pg_subscription_subname_index, 6115, on pg_subscription using btree(subdbid oid_ops, subname name_ops));
#define SubscriptionNameIndexId 6115
diff --git a/src/include/catalog/pg_subscription_rel.h b/src/include/catalog/pg_subscription_rel.h
index 06663b9f16..ab1202cf9b 100644
--- a/src/include/catalog/pg_subscription_rel.h
+++ b/src/include/catalog/pg_subscription_rel.h
@@ -49,7 +49,7 @@ CATALOG(pg_subscription_rel,6102,SubscriptionRelRelationId)
typedef FormData_pg_subscription_rel *Form_pg_subscription_rel;
-DECLARE_UNIQUE_INDEX(pg_subscription_rel_srrelid_srsubid_index, 6117, on pg_subscription_rel using btree(srrelid oid_ops, srsubid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_subscription_rel_srrelid_srsubid_index, 6117, on pg_subscription_rel using btree(srrelid oid_ops, srsubid oid_ops));
#define SubscriptionRelSrrelidSrsubidIndexId 6117
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_tablespace.h b/src/include/catalog/pg_tablespace.h
index ff92e20c5b..6a6c66a61c 100644
--- a/src/include/catalog/pg_tablespace.h
+++ b/src/include/catalog/pg_tablespace.h
@@ -49,7 +49,7 @@ DECLARE_TOAST(pg_tablespace, 4185, 4186);
#define PgTablespaceToastTable 4185
#define PgTablespaceToastIndex 4186
-DECLARE_UNIQUE_INDEX(pg_tablespace_oid_index, 2697, on pg_tablespace using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_tablespace_oid_index, 2697, on pg_tablespace using btree(oid oid_ops));
#define TablespaceOidIndexId 2697
DECLARE_UNIQUE_INDEX(pg_tablespace_spcname_index, 2698, on pg_tablespace using btree(spcname name_ops));
#define TablespaceNameIndexId 2698
diff --git a/src/include/catalog/pg_transform.h b/src/include/catalog/pg_transform.h
index 474baf3547..ad25db1841 100644
--- a/src/include/catalog/pg_transform.h
+++ b/src/include/catalog/pg_transform.h
@@ -42,7 +42,7 @@ CATALOG(pg_transform,3576,TransformRelationId)
*/
typedef FormData_pg_transform *Form_pg_transform;
-DECLARE_UNIQUE_INDEX(pg_transform_oid_index, 3574, on pg_transform using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_transform_oid_index, 3574, on pg_transform using btree(oid oid_ops));
#define TransformOidIndexId 3574
DECLARE_UNIQUE_INDEX(pg_transform_type_lang_index, 3575, on pg_transform using btree(trftype oid_ops, trflang oid_ops));
#define TransformTypeLangIndexId 3575
diff --git a/src/include/catalog/pg_trigger.h b/src/include/catalog/pg_trigger.h
index 9afea78900..55111ed864 100644
--- a/src/include/catalog/pg_trigger.h
+++ b/src/include/catalog/pg_trigger.h
@@ -78,7 +78,7 @@ DECLARE_INDEX(pg_trigger_tgconstraint_index, 2699, on pg_trigger using btree(tgc
#define TriggerConstraintIndexId 2699
DECLARE_UNIQUE_INDEX(pg_trigger_tgrelid_tgname_index, 2701, on pg_trigger using btree(tgrelid oid_ops, tgname name_ops));
#define TriggerRelidNameIndexId 2701
-DECLARE_UNIQUE_INDEX(pg_trigger_oid_index, 2702, on pg_trigger using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_trigger_oid_index, 2702, on pg_trigger using btree(oid oid_ops));
#define TriggerOidIndexId 2702
#ifdef EXPOSE_TO_CLIENT_CODE
diff --git a/src/include/catalog/pg_ts_config.h b/src/include/catalog/pg_ts_config.h
index aef7da0f76..02ef1a1554 100644
--- a/src/include/catalog/pg_ts_config.h
+++ b/src/include/catalog/pg_ts_config.h
@@ -49,7 +49,7 @@ typedef FormData_pg_ts_config *Form_pg_ts_config;
DECLARE_UNIQUE_INDEX(pg_ts_config_cfgname_index, 3608, on pg_ts_config using btree(cfgname name_ops, cfgnamespace oid_ops));
#define TSConfigNameNspIndexId 3608
-DECLARE_UNIQUE_INDEX(pg_ts_config_oid_index, 3712, on pg_ts_config using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_config_oid_index, 3712, on pg_ts_config using btree(oid oid_ops));
#define TSConfigOidIndexId 3712
#endif /* PG_TS_CONFIG_H */
diff --git a/src/include/catalog/pg_ts_config_map.h b/src/include/catalog/pg_ts_config_map.h
index 95901a9406..f39d14fd79 100644
--- a/src/include/catalog/pg_ts_config_map.h
+++ b/src/include/catalog/pg_ts_config_map.h
@@ -44,7 +44,7 @@ CATALOG(pg_ts_config_map,3603,TSConfigMapRelationId)
typedef FormData_pg_ts_config_map *Form_pg_ts_config_map;
-DECLARE_UNIQUE_INDEX(pg_ts_config_map_index, 3609, on pg_ts_config_map using btree(mapcfg oid_ops, maptokentype int4_ops, mapseqno int4_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_config_map_index, 3609, on pg_ts_config_map using btree(mapcfg oid_ops, maptokentype int4_ops, mapseqno int4_ops));
#define TSConfigMapIndexId 3609
#endif /* PG_TS_CONFIG_MAP_H */
diff --git a/src/include/catalog/pg_ts_dict.h b/src/include/catalog/pg_ts_dict.h
index 814d0d4407..bfe3378ff8 100644
--- a/src/include/catalog/pg_ts_dict.h
+++ b/src/include/catalog/pg_ts_dict.h
@@ -55,7 +55,7 @@ DECLARE_TOAST(pg_ts_dict, 4169, 4170);
DECLARE_UNIQUE_INDEX(pg_ts_dict_dictname_index, 3604, on pg_ts_dict using btree(dictname name_ops, dictnamespace oid_ops));
#define TSDictionaryNameNspIndexId 3604
-DECLARE_UNIQUE_INDEX(pg_ts_dict_oid_index, 3605, on pg_ts_dict using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_dict_oid_index, 3605, on pg_ts_dict using btree(oid oid_ops));
#define TSDictionaryOidIndexId 3605
#endif /* PG_TS_DICT_H */
diff --git a/src/include/catalog/pg_ts_parser.h b/src/include/catalog/pg_ts_parser.h
index 57480ebfaf..f9f22716fd 100644
--- a/src/include/catalog/pg_ts_parser.h
+++ b/src/include/catalog/pg_ts_parser.h
@@ -56,7 +56,7 @@ typedef FormData_pg_ts_parser *Form_pg_ts_parser;
DECLARE_UNIQUE_INDEX(pg_ts_parser_prsname_index, 3606, on pg_ts_parser using btree(prsname name_ops, prsnamespace oid_ops));
#define TSParserNameNspIndexId 3606
-DECLARE_UNIQUE_INDEX(pg_ts_parser_oid_index, 3607, on pg_ts_parser using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_parser_oid_index, 3607, on pg_ts_parser using btree(oid oid_ops));
#define TSParserOidIndexId 3607
#endif /* PG_TS_PARSER_H */
diff --git a/src/include/catalog/pg_ts_template.h b/src/include/catalog/pg_ts_template.h
index c1c7fedbe7..ae91922688 100644
--- a/src/include/catalog/pg_ts_template.h
+++ b/src/include/catalog/pg_ts_template.h
@@ -47,7 +47,7 @@ typedef FormData_pg_ts_template *Form_pg_ts_template;
DECLARE_UNIQUE_INDEX(pg_ts_template_tmplname_index, 3766, on pg_ts_template using btree(tmplname name_ops, tmplnamespace oid_ops));
#define TSTemplateNameNspIndexId 3766
-DECLARE_UNIQUE_INDEX(pg_ts_template_oid_index, 3767, on pg_ts_template using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_ts_template_oid_index, 3767, on pg_ts_template using btree(oid oid_ops));
#define TSTemplateOidIndexId 3767
#endif /* PG_TS_TEMPLATE_H */
diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h
index 37345bec00..0d6981bc87 100644
--- a/src/include/catalog/pg_type.h
+++ b/src/include/catalog/pg_type.h
@@ -262,7 +262,7 @@ typedef FormData_pg_type *Form_pg_type;
DECLARE_TOAST(pg_type, 4171, 4172);
-DECLARE_UNIQUE_INDEX(pg_type_oid_index, 2703, on pg_type using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_type_oid_index, 2703, on pg_type using btree(oid oid_ops));
#define TypeOidIndexId 2703
DECLARE_UNIQUE_INDEX(pg_type_typname_nsp_index, 2704, on pg_type using btree(typname name_ops, typnamespace oid_ops));
#define TypeNameNspIndexId 2704
diff --git a/src/include/catalog/pg_user_mapping.h b/src/include/catalog/pg_user_mapping.h
index 3589125015..cabca048a9 100644
--- a/src/include/catalog/pg_user_mapping.h
+++ b/src/include/catalog/pg_user_mapping.h
@@ -47,7 +47,7 @@ typedef FormData_pg_user_mapping *Form_pg_user_mapping;
DECLARE_TOAST(pg_user_mapping, 4173, 4174);
-DECLARE_UNIQUE_INDEX(pg_user_mapping_oid_index, 174, on pg_user_mapping using btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX_PKEY(pg_user_mapping_oid_index, 174, on pg_user_mapping using btree(oid oid_ops));
#define UserMappingOidIndexId 174
DECLARE_UNIQUE_INDEX(pg_user_mapping_user_server_index, 175, on pg_user_mapping using btree(umuser oid_ops, umserver oid_ops));
#define UserMappingUserServerIndexId 175
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..4b2f63a95e 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 CONSTRAINT foo 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,8 @@ 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 CONSTRAINT foo UNIQUE USING INDEX pg_namespace_nspname_index;
+NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "pg_namespace_nspname_index" to "foo"
ROLLBACK;
-- write to system catalog table as superuser
BEGIN;
@@ -146,7 +147,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..6bfc896ef9 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 CONSTRAINT foo 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 CONSTRAINT foo 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 d40afeef78..ae85817c61 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -73,7 +73,6 @@ loop
end if;
end loop;
end$$;
-NOTICE: pg_constraint contains unpinned initdb-created object(s)
NOTICE: pg_database contains unpinned initdb-created object(s)
NOTICE: pg_extension contains unpinned initdb-created object(s)
NOTICE: pg_rewrite contains unpinned initdb-created object(s)
@@ -109,3 +108,30 @@ ORDER BY 1, 2;
pg_largeobject_metadata | lomacl | aclitem[]
(11 rows)
+-- system catalogs without primary keys
+--
+-- Current exceptions:
+-- * pg_depend, pg_shdepend don't have a unique key
+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_shdepend
+(2 rows)
+
+-- system catalog unique indexes not wrapped in a constraint
+-- (There should be none.)
+SELECT relname
+FROM pg_class c JOIN pg_index i ON c.oid = i.indexrelid
+WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'i'
+ AND i.indisunique
+ AND c.oid NOT IN (SELECT conindid FROM pg_constraint)
+ORDER BY 1;
+ relname
+---------
+(0 rows)
+
diff --git a/src/test/regress/sql/misc_sanity.sql b/src/test/regress/sql/misc_sanity.sql
index 3ce32e4725..ea80cf1f10 100644
--- a/src/test/regress/sql/misc_sanity.sql
+++ b/src/test/regress/sql/misc_sanity.sql
@@ -94,3 +94,24 @@
relkind = 'r' AND
attstorage != 'p'
ORDER BY 1, 2;
+
+
+-- system catalogs without primary keys
+--
+-- Current exceptions:
+-- * pg_depend, pg_shdepend don't have a unique key
+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;
+
+
+-- system catalog unique indexes not wrapped in a constraint
+-- (There should be none.)
+SELECT relname
+FROM pg_class c JOIN pg_index i ON c.oid = i.indexrelid
+WHERE relnamespace = 'pg_catalog'::regnamespace AND relkind = 'i'
+ AND i.indisunique
+ AND c.oid NOT IN (SELECT conindid FROM pg_constraint)
+ORDER BY 1;
diff --git a/src/tools/msvc/clean.bat b/src/tools/msvc/clean.bat
index 672bb2d650..4575e3f95f 100755
--- a/src/tools/msvc/clean.bat
+++ b/src/tools/msvc/clean.bat
@@ -71,6 +71,7 @@ if %DIST%==1 if exist src\interfaces\ecpg\preproc\c_kwlist_d.h del /q src\interf
if %DIST%==1 if exist src\interfaces\ecpg\preproc\ecpg_kwlist_d.h del /q src\interfaces\ecpg\preproc\ecpg_kwlist_d.h
if %DIST%==1 if exist src\interfaces\ecpg\preproc\preproc.y del /q src\interfaces\ecpg\preproc\preproc.y
if %DIST%==1 if exist src\backend\catalog\postgres.bki del /q src\backend\catalog\postgres.bki
+if %DIST%==1 if exist src\backend\catalog\system_constraints.sql del /q src\backend\catalog\system_constraints.sql
if %DIST%==1 if exist src\backend\catalog\schemapg.h del /q src\backend\catalog\schemapg.h
if %DIST%==1 if exist src\backend\catalog\pg_*_d.h del /q src\backend\catalog\pg_*_d.h
if %DIST%==1 if exist src\backend\catalog\bki-stamp del /q src\backend\catalog\bki-stamp
base-commit: 920f853dc948b98a5dc96580c4ee011a302e33e4
--
2.30.0
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
On 2021-01-17 23:07, Tom Lane wrote:
I've reviewed this patch. It looks pretty solid to me, with a couple
trivial nits as mentioned below, and one bigger thing that's perhaps
in the category of bikeshedding. Namely, do we really want to prefer
using the OID indexes as the primary keys?
I chose this because the notional foreign keys point to the OID.
If you design some basic business database with customer IDs, product
IDs, etc., you'd also usually make the ID the primary key, even if you
have, say, a unique constraint on the product name. But this is of
course a matter of taste to some degree.
Fair enough. As I said upthread, I just wanted to be sure we'd considered
the alternative. I'm content to use the OIDs as pkeys, although I think
that decision should be explicitly recorded somewhere (cf attachment).
The contents of system_constraints.sql seem pretty randomly ordered,
and I bet the order isn't stable across machines.
They follow the order in which the catalogs are processed byt genbki.pl.
Looking closer, I see the data structure is an array not a hash, so
I withdraw the concern about instability.
After reading the patch again, I have a couple more nits about comments,
which I'll just present as a proposed delta patch. Otherwise it's good.
I'll mark it RFC.
regards, tom lane
Attachments:
catalog-primary-keys-comments.patchtext/x-diff; charset=us-ascii; name=catalog-primary-keys-comments.patchDownload
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 6747996ce3..b68c1752c0 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -663,6 +663,8 @@ die
"genbki OID counter reached $GenbkiNextOid, overrunning FirstBootstrapObjectId\n"
if $GenbkiNextOid > $FirstBootstrapObjectId;
+# Now generate system_constraints.sql
+
foreach my $c (@system_constraints)
{
print $constraints $c, "\n";
diff --git a/src/include/catalog/genbki.h b/src/include/catalog/genbki.h
index 296765d987..5d05fafb5d 100644
--- a/src/include/catalog/genbki.h
+++ b/src/include/catalog/genbki.h
@@ -55,12 +55,15 @@
#define DECLARE_TOAST(name,toastoid,indexoid) extern int no_such_variable
/*
- * These lines processed by genbki.pl to create the statements
+ * These lines are processed by genbki.pl to create the statements
* the bootstrap parser will turn into DefineIndex calls.
*
- * The keyword is DECLARE_INDEX or DECLARE_UNIQUE_INDEX or DECLARE_UNIQUE_INDEX_PKEY. The first two
- * arguments are the index name and OID, the rest is much like a standard
- * 'create index' SQL command.
+ * The keyword is DECLARE_INDEX or DECLARE_UNIQUE_INDEX or
+ * DECLARE_UNIQUE_INDEX_PKEY. ("PKEY" marks the index as being the catalog's
+ * primary key; currently this is only cosmetically different from a regular
+ * unique index. By convention, we usually make a catalog's OID column its
+ * pkey, if it has one.) The first two arguments are the index's name and
+ * OID, the rest is much like a standard 'create index' SQL command.
*
* For each index, we also provide a #define for its OID. References to
* the index in the C code should always use these #defines, not the actual
diff --git a/src/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out
index ae85817c61..9ebe28a78d 100644
--- a/src/test/regress/expected/misc_sanity.out
+++ b/src/test/regress/expected/misc_sanity.out
@@ -43,6 +43,9 @@ WHERE refclassid = 0 OR refobjid = 0 OR
-- whatever OID the test is complaining about must have been added later
-- in initdb, where it intentionally isn't pinned. Legitimate exceptions
-- to that rule are listed in the comments in setup_depend().
+-- Currently, pg_rewrite is also listed by this check, even though it is
+-- covered by setup_depend(). That happens because there are no rules in
+-- the pinned data, but initdb creates some intentionally-not-pinned views.
do $$
declare relnm text;
reloid oid;
diff --git a/src/test/regress/sql/misc_sanity.sql b/src/test/regress/sql/misc_sanity.sql
index ea80cf1f10..9699f5cc3b 100644
--- a/src/test/regress/sql/misc_sanity.sql
+++ b/src/test/regress/sql/misc_sanity.sql
@@ -44,6 +44,9 @@ WHERE refclassid = 0 OR refobjid = 0 OR
-- whatever OID the test is complaining about must have been added later
-- in initdb, where it intentionally isn't pinned. Legitimate exceptions
-- to that rule are listed in the comments in setup_depend().
+-- Currently, pg_rewrite is also listed by this check, even though it is
+-- covered by setup_depend(). That happens because there are no rules in
+-- the pinned data, but initdb creates some intentionally-not-pinned views.
do $$
declare relnm text;
Many thanks for excellent work!
I've tested the patch successfully.
I ran this query (on a patched database) to see if there are still any catalog tables without primary keys:
SELECT
table_name
FROM information_schema.tables
WHERE table_schema = 'pg_catalog'
AND table_type = 'BASE TABLE'
EXCEPT
SELECT
table_constraints.table_name
FROM information_schema.table_constraints
JOIN information_schema.key_column_usage
ON key_column_usage.constraint_name = table_constraints.constraint_name
WHERE table_constraints.table_schema = 'pg_catalog'
AND table_constraints.constraint_type = 'PRIMARY KEY'
table_name
-------------
pg_depend
pg_shdepend
(2 rows)
Wouldn't it be possible to add primary keys to these two as well?
It would need to be multi-column primary keys, but should be ok
since we have that for other catalogs?
/Joel
Show quoted text
On Thu, Jan 21, 2021, at 18:15, Tom Lane wrote:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
On 2021-01-17 23:07, Tom Lane wrote:
I've reviewed this patch. It looks pretty solid to me, with a couple
trivial nits as mentioned below, and one bigger thing that's perhaps
in the category of bikeshedding. Namely, do we really want to prefer
using the OID indexes as the primary keys?I chose this because the notional foreign keys point to the OID.
If you design some basic business database with customer IDs, product
IDs, etc., you'd also usually make the ID the primary key, even if you
have, say, a unique constraint on the product name. But this is of
course a matter of taste to some degree.Fair enough. As I said upthread, I just wanted to be sure we'd considered
the alternative. I'm content to use the OIDs as pkeys, although I think
that decision should be explicitly recorded somewhere (cf attachment).The contents of system_constraints.sql seem pretty randomly ordered,
and I bet the order isn't stable across machines.They follow the order in which the catalogs are processed byt genbki.pl.
Looking closer, I see the data structure is an array not a hash, so
I withdraw the concern about instability.After reading the patch again, I have a couple more nits about comments,
which I'll just present as a proposed delta patch. Otherwise it's good.
I'll mark it RFC.regards, tom lane
"Joel Jacobson" <joel@compiler.org> writes:
I ran this query (on a patched database) to see if there are still any catalog tables without primary keys:
...
pg_depend
pg_shdepend
Yeah, this is noted in the patch's own regression tests.
Wouldn't it be possible to add primary keys to these two as well?
Neither of the existing indexes is suitable, not being unique.
We could imagine adding a unique index across the whole column set,
but that would be an awfully large price to pay for neatnik-ism.
Also, at least for pg_depend (less sure about pg_shdepend), some code
cleanup would be required, because I don't think that we try very
hard to avoid making duplicate dependency entries. On the whole
I feel this'd be counterproductive.
regards, tom lane
On Fri, Jan 22, 2021, at 16:42, Tom Lane wrote:
"Joel Jacobson" <joel@compiler.org> writes:
I ran this query (on a patched database) to see if there are still any catalog tables without primary keys:
...
pg_depend
pg_shdependYeah, this is noted in the patch's own regression tests.
Thanks. Looks good.
Wouldn't it be possible to add primary keys to these two as well?
Neither of the existing indexes is suitable, not being unique.
We could imagine adding a unique index across the whole column set,
but that would be an awfully large price to pay for neatnik-ism.
Also, at least for pg_depend (less sure about pg_shdepend), some code
cleanup would be required, because I don't think that we try very
hard to avoid making duplicate dependency entries. On the whole
I feel this'd be counterproductive.regards, tom lane
I see, and I agree with you.
I'm very happy with this patch.
I've already found great use of it in a tool I'm working on:
https://github.com/truthly/pg-pit/blob/master/FUNCTIONS/add_primary_keys.sql#L23
Many thanks to all of you for all the great work!
Can't wait to use this functionality in production.
/Joel
On 2021-01-21 18:15, Tom Lane wrote:
After reading the patch again, I have a couple more nits about comments,
which I'll just present as a proposed delta patch. Otherwise it's good.
I'll mark it RFC.
Committed with your update, thanks.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
Committed with your update, thanks.
Hmm, shouldn't there have been a catversion bump in there?
regards, tom lane
On 2021-01-30 22:56, Tom Lane wrote:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
Committed with your update, thanks.
Hmm, shouldn't there have been a catversion bump in there?
I suppose yes on the grounds that it introduces something new in a
freshly initdb-ed database. But I thought it wasn't necessary because
there is no dependency between the binaries and the on-disk state.
There has already been another catversion change since, so it's no
longer relevant.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
On 2021-01-30 22:56, Tom Lane wrote:
Hmm, shouldn't there have been a catversion bump in there?
I suppose yes on the grounds that it introduces something new in a
freshly initdb-ed database. But I thought it wasn't necessary because
there is no dependency between the binaries and the on-disk state.
I've generally worked on the theory that a catversion bump is indicated
if you need to initdb in order to pass the updated regression tests.
Which one did in this case. However ...
There has already been another catversion change since, so it's no
longer relevant.
... yeah, it's moot now.
regards, tom lane
On 2021-02-01 15:24, Tom Lane wrote:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
On 2021-01-30 22:56, Tom Lane wrote:
Hmm, shouldn't there have been a catversion bump in there?
I suppose yes on the grounds that it introduces something new in a
freshly initdb-ed database. But I thought it wasn't necessary because
there is no dependency between the binaries and the on-disk state.I've generally worked on the theory that a catversion bump is indicated
if you need to initdb in order to pass the updated regression tests.
Which one did in this case.
Yeah, that's a good way of looking at it. I'll keep that in mind.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
On 2021-01-22 16:42, Tom Lane wrote:
pg_depend
pg_shdependYeah, this is noted in the patch's own regression tests.
Wouldn't it be possible to add primary keys to these two as well?
Neither of the existing indexes is suitable, not being unique.
We could imagine adding a unique index across the whole column set,
but that would be an awfully large price to pay for neatnik-ism.
Also, at least for pg_depend (less sure about pg_shdepend), some code
cleanup would be required, because I don't think that we try very
hard to avoid making duplicate dependency entries. On the whole
I feel this'd be counterproductive.
I did attempt to make a six- or seven-column unique constraint on
pg_depend a while ago. This fails pretty much immediately during
initdb. None of the code that adds dependencies, in particular
recordMultipleDependencies(), checks if the dependency is already there.
I do wonder, however, under what circumstances code would be put into a
situation where it would add the exact same dependency again, and also
under what circumstances it would add a dependency between the same
objects but a different deptype, and how that would work during
recursive deletion. Now that we have the refobjversion column, the
presence of duplicate dependencies might be even more dubious. I think
that could be worth analyzing.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
On Tue, Feb 2, 2021 at 6:49 PM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
I do wonder, however, under what circumstances code would be put into a
situation where it would add the exact same dependency again, and also
under what circumstances it would add a dependency between the same
objects but a different deptype, and how that would work during
recursive deletion. Now that we have the refobjversion column, the
presence of duplicate dependencies might be even more dubious. I think
that could be worth analyzing.
There's at least dependencies from indexes to pg_class (underlying
table) and pg_collation that can be entirely duplicated (if the same
column and/or collation is used in both key and predicate for
instance), and this was the case before refobjversion was introduced.
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
I do wonder, however, under what circumstances code would be put into a
situation where it would add the exact same dependency again, and also
under what circumstances it would add a dependency between the same
objects but a different deptype, and how that would work during
recursive deletion. Now that we have the refobjversion column, the
presence of duplicate dependencies might be even more dubious. I think
that could be worth analyzing.
The duplicate-deps case occurs if, say, you use the same function more
than once in a view. While we could probably get rid of dups generated
within the same recordMultipleDependencies call (and maybe already do?),
it's harder to deal with dups made by retail calls. For example,
makeOperatorDependencies doesn't trouble to avoid making duplicate
dependencies when the operator's left and right input types are the
same, or the same as the result type.
The case with almost-dup-but-not-same-deptype *does* occur, for instance
it's very possible to have both normal and automatic dependencies.
As I recall, the policy is to perform the deletion if any of the deps
would allow it.
Dunno about refobjversion; I have my doubts that putting that info in
pg_depend was a sane design choice at all. But from what I understand
of it, wouldn't all deps on a given object necessarily have the same
version?
regards, tom lane
On Tue, Feb 2, 2021 at 11:17 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dunno about refobjversion; I have my doubts that putting that info in
pg_depend was a sane design choice at all. But from what I understand
of it, wouldn't all deps on a given object necessarily have the same
version?
Correct, assuming of course that the dependencies are from the same object.
On 03.10.20 08:40, Peter Eisentraut wrote:
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?
Given that these prerequisites have been accomplished in PostgreSQL 14,
I went back and made a little tool to convert the foreign-key
relationship information into a graphviz representation. The result
turns out to be pretty unwieldly and probably not something easily
suitable for the documentation; maybe someone wants to fine-tune the
graphviz settings to get a better view. My tool is at
<https://github.com/petere/pgcatviz>. Attached is a sample output.