Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit641f3df

Browse files
committed
Restore the previous semantics of get_constraint_index().
Commit8b069ef changed this function to look at pg_constraint.conindidrather than searching pg_depend. That was a good performance improvement,but it failed to preserve the exact semantics. The old code would onlyreturn an index that was "owned by" (internally dependent on) thespecified constraint, whereas the new code will also return indexes thatare just referenced by foreign key constraints. This confuses ALTERTABLE, which was implicitly expecting the previous semantics, intofailing with errors like ERROR: relation 146621 has multiple clustered indexesor ERROR: "pk_attbl" is not an index for table "atref"We can fix this without reverting the performance improvement by addinga contype check in get_constraint_index(). Another way could be tomake ALTER TABLE check it, but I'm worried that extension code couldalso 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
1 parentd6f1cde commit641f3df

File tree

3 files changed

+45
-3
lines changed

3 files changed

+45
-3
lines changed

‎src/backend/utils/cache/lsyscache.c

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1126,8 +1126,13 @@ get_constraint_name(Oid conoid)
11261126
*Given the OID of a unique, primary-key, or exclusion constraint,
11271127
*return the OID of the underlying index.
11281128
*
1129-
* Return InvalidOid if the index couldn't be found; this suggests the
1130-
* given OID is bogus, but we leave it to caller to decide what to do.
1129+
* Returns InvalidOid if the constraint could not be found or is of
1130+
* the wrong type.
1131+
*
1132+
* The intent of this function is to return the index "owned" by the
1133+
* specified constraint. Therefore we must check contype, since some
1134+
* pg_constraint entries (e.g. for foreign-key constraints) store the
1135+
* OID of an index that is referenced but not owned by the constraint.
11311136
*/
11321137
Oid
11331138
get_constraint_index(Oidconoid)
@@ -1140,7 +1145,12 @@ get_constraint_index(Oid conoid)
11401145
Form_pg_constraintcontup= (Form_pg_constraint)GETSTRUCT(tp);
11411146
Oidresult;
11421147

1143-
result=contup->conindid;
1148+
if (contup->contype==CONSTRAINT_UNIQUE||
1149+
contup->contype==CONSTRAINT_PRIMARY||
1150+
contup->contype==CONSTRAINT_EXCLUSION)
1151+
result=contup->conindid;
1152+
else
1153+
result=InvalidOid;
11441154
ReleaseSysCache(tp);
11451155
returnresult;
11461156
}

‎src/test/regress/expected/alter_table.out

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4502,6 +4502,20 @@ create trigger xtrig
45024502
update bar1 set a = a + 1;
45034503
INFO: a=1, b=1
45044504
/* End test case for bug #16242 */
4505+
/* Test case for bug #17409 */
4506+
create table attbl (p1 int constraint pk_attbl primary key);
4507+
create table atref (c1 int references attbl(p1));
4508+
cluster attbl using pk_attbl;
4509+
alter table attbl alter column p1 set data type bigint;
4510+
alter table atref alter column c1 set data type bigint;
4511+
drop table attbl, atref;
4512+
create table attbl (p1 int constraint pk_attbl primary key);
4513+
alter table attbl replica identity using index pk_attbl;
4514+
create table atref (c1 int references attbl(p1));
4515+
alter table attbl alter column p1 set data type bigint;
4516+
alter table atref alter column c1 set data type bigint;
4517+
drop table attbl, atref;
4518+
/* End test case for bug #17409 */
45054519
-- Test that ALTER TABLE rewrite preserves a clustered index
45064520
-- for normal indexes and indexes on constraints.
45074521
create table alttype_cluster (a int);

‎src/test/regress/sql/alter_table.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2958,6 +2958,24 @@ update bar1 set a = a + 1;
29582958

29592959
/* End test case for bug #16242*/
29602960

2961+
/* Test case for bug #17409*/
2962+
2963+
createtableattbl (p1intconstraint pk_attblprimary key);
2964+
createtableatref (c1intreferences attbl(p1));
2965+
cluster attbl using pk_attbl;
2966+
altertable attbl alter column p1set data typebigint;
2967+
altertable atref alter column c1set data typebigint;
2968+
droptable attbl, atref;
2969+
2970+
createtableattbl (p1intconstraint pk_attblprimary key);
2971+
altertable attbl replica identity using index pk_attbl;
2972+
createtableatref (c1intreferences attbl(p1));
2973+
altertable attbl alter column p1set data typebigint;
2974+
altertable atref alter column c1set data typebigint;
2975+
droptable attbl, atref;
2976+
2977+
/* End test case for bug #17409*/
2978+
29612979
-- Test that ALTER TABLE rewrite preserves a clustered index
29622980
-- for normal indexes and indexes on constraints.
29632981
createtablealttype_cluster (aint);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp