commit 641f3dffcdf1c7378cfb94c98b6642793181d6db
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Mar 11 13:47:26 2022 -0500
Restore the previous semantics of get_constraint_index().
Commit 8b069ef5d changed this function to look at pg_constraint.conindid
rather than searching pg_depend. That was a good performance improvement,
but it failed to preserve the exact semantics. The old code would only
return an index that was "owned by" (internally dependent on) the
specified constraint, whereas the new code will also return indexes that
are just referenced by foreign key constraints. This confuses ALTER
TABLE, which was implicitly expecting the previous semantics, into
failing with errors like
ERROR: relation 146621 has multiple clustered indexes
or
ERROR: "pk_attbl" is not an index for table "atref"
We can fix this without reverting the performance improvement by adding
a contype check in get_constraint_index(). Another way could be to
make ALTER TABLE check it, but I'm worried that extension code could
also have subtle dependencies on the old semantics.
Tom Lane and Japin Li, per bug #17409 from Holly Roberts.
Back-patch to v14 where the error crept in.
Discussion: https://postgr.es/m/17409-52871dda8b5741cb@postgresql.org
现象
我们可以通过如下的示例来重现这个问题（源码为 PostgreSQL e94bb1473）。
1 2 3 4 5 6 7 8 9 10 11 12 13
$ psql postgres psql (15devel) Type "help" for help.
postgres=# CREATETABLE attbl (p1 intconstraint pk_attbl primary key); CREATETABLE postgres=# CREATETABLE atref (c1 intreferences attbl(p1)); CREATETABLE postgres=# CLUSTER attbl USING pk_attbl; CLUSTER postgres=# ALTERTABLE attbl ALTERCOLUMN p1 SET DATA TYPE bigint; ERROR: relation 16384 has multiple clustered indexes postgres=#
然而实际上表 attbl 并不存在多个聚集索引，如下所示。
1 2 3 4 5 6 7 8 9 10 11 12 13 14
postgres=# SELECT postgres-# table_cls.relnamespace::regnamespace::text AS schema, postgres-# table_cls.relname AS table, postgres-# index_cls.relname AS index, postgres-# indisclustered postgres-# FROM postgres-# pg_index pi INNER JOIN pg_class index_cls ON (pi.indexrelid = index_cls.oid) postgres-# INNER JOIN pg_class table_cls ON (pi.indrelid = table_cls.oid) postgres-# WHERE postgres-# (table_cls.relnamespace::regnamespace::text, table_cls.relname) = ('public', 'attbl'); schema | table | index | indisclustered --------+-------+----------+---------------- public | attbl | pk_attbl | t (1 row)
commit 8b069ef5dca97cd737a5fd64c420df3cd61ec1c9
Author: Peter Eisentraut <peter@eisentraut.org>
Date: Wed Dec 9 15:12:05 2020 +0100
Change get_constraint_index() to use pg_constraint.conindid
It was still using a scan of pg_depend instead of using the conindid
column that has been added since.
Since it is now just a catalog lookup wrapper and not related to
pg_depend, move from pg_depend.c to lsyscache.c.
Reviewed-by: Matthias van de Meent <boekewurm+postgres@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Discussion: https://www.postgresql.org/message-id/flat/4688d55c-9a2e-9a5a-d166-5f24fe0bf8db%40enterprisedb.com
/* * Subroutine for ATExecAlterColumnType: remember any clustered index. */ staticvoid RememberClusterOnForRebuilding(Oid indoid, AlteredTableInfo *tab) { if (!get_index_isclustered(indoid)) return;
if (tab->clusterOnIndex) elog(ERROR, "relation %u has multiple clustered indexes", tab->relid);
tab->clusterOnIndex = get_rel_name(indoid); }
在该函数中，它使用 tab->clusterOnIndex 来判断是否已经存在聚集索引。这个问题就出在这个聚集索引出现了两次，通过调试发现，这两次的值都是一致的，这里出现两次调用是由于 ALTER TABLE attbl ALTER COLUMN p1 SET DATA TYPE bigint; 这个语句在内部还会生成一条 ALTER TABLE public.tbref ADD CONSTRAINT fk_tbref FOREIGN KEY (c1) REFERENCES attbl(p1); 语句（尚未分析这样做的原因）。由于在 RememberClusterOnForRebuilding() 函数中没有判断 tab->clusterOnIndex 是否为同一个对象，因此，当同一个对象出现两次时，也会报如上错误，经 Michael Paquier 指出，RememberReplicaIdentityForRebuilding() 存在相似的问题。
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index dc5872f988..19c924b7df 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -189,8 +189,8 @@ typedef struct AlteredTableInfo List *changedConstraintDefs; /* string definitions of same */ List *changedIndexOids; /* OIDs of indexes to rebuild */ List *changedIndexDefs; /* string definitions of same */ - char *replicaIdentityIndex; /* index to reset as REPLICA IDENTITY */ - char *clusterOnIndex; /* index to use for CLUSTER */ + Oid replicaIdentityIndex; /* index to reset as REPLICA IDENTITY */ + Oid clusterOnIndex; /* index to use for CLUSTER */ List *changedStatisticsOids; /* OIDs of statistics to rebuild */ List *changedStatisticsDefs; /* string definitions of same */ } AlteredTableInfo; @@ -12856,10 +12856,12 @@ RememberReplicaIdentityForRebuilding(Oid indoid, AlteredTableInfo *tab) if (!get_index_isreplident(indoid)) return;
- if (tab->replicaIdentityIndex) + /* We must de-duplicate multiple requests */ + if (OidIsValid(tab->replicaIdentityIndex) && + tab->replicaIdentityIndex != indoid) elog(ERROR, "relation %u has multiple indexes marked as replica identity", tab->relid);
@@ -13135,12 +13141,13 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) /* * Queue up command to restore marking of index used for cluster. */ - if (tab->clusterOnIndex) + if (OidIsValid(tab->clusterOnIndex)) { AlterTableCmd *cmd = makeNode(AlterTableCmd);
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index feef999863..1b7e11b93e 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1126,8 +1126,13 @@ get_constraint_name(Oid conoid) * Given the OID of a unique, primary-key, or exclusion constraint, * return the OID of the underlying index. * - * Return InvalidOid if the index couldn't be found; this suggests the - * given OID is bogus, but we leave it to caller to decide what to do. + * Returns InvalidOid if the constraint could not be found or is of + * the wrong type. + * + * The intent of this function is to return the index "owned" by the + * specified constraint. Therefore we must check contype, since some + * pg_constraint entries (e.g. for foreign-key constraints) store the + * OID of an index that is referenced but not owned by the constraint. */ Oid get_constraint_index(Oid conoid) @@ -1140,7 +1145,12 @@ get_constraint_index(Oid conoid) Form_pg_constraint contup = (Form_pg_constraint) GETSTRUCT(tp); Oid result;
- result = contup->conindid; + if (contup->contype == CONSTRAINT_UNIQUE || + contup->contype == CONSTRAINT_PRIMARY || + contup->contype == CONSTRAINT_EXCLUSION) + result = contup->conindid; + else + result = InvalidOid; ReleaseSysCache(tp); return result; }