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

Commit0b6edb9

Browse files
committed
Fix ALTER COLUMN TYPE failure with a partial exclusion constraint.
ATExecAlterColumnType failed to consider the possibility that an indexthat needs to be rebuilt might be a child of a constraint that needs to berebuilt. We missed this so far because usually a constraint index doesn'thave a direct dependency on its table, just on the constraint object.But if there's a WHERE clause, then dependency analysis of the WHEREclause results in direct dependencies on the column(s) mentioned in WHERE.This led to trying to drop and rebuild both the constraint and itsunderlying index.In v11/HEAD, we successfully drop both the index and the constraint,and then try to rebuild both, and of course the second rebuild hits aduplicate-index-name problem. Before v11, it fails with obscure messagesabout a missing relation OID, due to trying to drop the index twice.This is essentially the same kind of problem noted in commit20bef2c: the possible dependency linkages are broader than whatATExecAlterColumnType was designed for. It was probably OK whenwritten, but it's certainly been broken since the introduction ofpartial exclusion constraints. Fix by adding an explicit checkfor whether any of the indexes-to-be-rebuilt belong to any of theconstraints-to-be-rebuilt, and ignoring any that do.In passing, fix a latent bug introduced by commit8b08f7d: inget_constraint_index() we must "continue" not "break" when rejectinga relation of a wrong relkind. This is harmless today because we don'texpect that code path to be taken anyway; but if there ever were anyrelations to be ignored, the existing coding would have an extremelyundesirable dependency on the order of pg_depend entries.Also adjust a couple of obsolete comments.Per bug #15835 from Yaroslav Schekin. Back-patch to all supportedbranches.Discussion:https://postgr.es/m/15835-32d9b7a76c06a7a9@postgresql.org
1 parentfa5f3a4 commit0b6edb9

File tree

4 files changed

+118
-13
lines changed

4 files changed

+118
-13
lines changed

‎src/backend/catalog/pg_depend.c

Lines changed: 10 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -616,8 +616,8 @@ getOwnedSequence(Oid relid, AttrNumber attnum)
616616

617617
/*
618618
* get_constraint_index
619-
*Given the OID of a unique orprimary-key constraint, return the
620-
*OID of the underlying unique index.
619+
*Given the OID of a unique,primary-key, or exclusion constraint,
620+
*return theOID of the underlying index.
621621
*
622622
* Return InvalidOid if the index couldn't be found; this suggests the
623623
* given OID is bogus, but we leave it to caller to decide what to do.
@@ -664,10 +664,13 @@ get_constraint_index(Oid constraintId)
664664
{
665665
charrelkind=get_rel_relkind(deprec->objid);
666666

667-
/* This is pure paranoia; there shouldn't be any such */
667+
/*
668+
* This is pure paranoia; there shouldn't be any other relkinds
669+
* dependent on a constraint.
670+
*/
668671
if (relkind!=RELKIND_INDEX&&
669672
relkind!=RELKIND_PARTITIONED_INDEX)
670-
break;
673+
continue;
671674

672675
indexId=deprec->objid;
673676
break;
@@ -682,8 +685,9 @@ get_constraint_index(Oid constraintId)
682685

683686
/*
684687
* get_index_constraint
685-
*Given the OID of an index, return the OID of the owning unique or
686-
*primary-key constraint, or InvalidOid if no such constraint.
688+
*Given the OID of an index, return the OID of the owning unique,
689+
*primary-key, or exclusion constraint, or InvalidOid if there
690+
*is no owning constraint.
687691
*/
688692
Oid
689693
get_index_constraint(OidindexId)

‎src/backend/commands/tablecmds.c

Lines changed: 51 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -9785,6 +9785,9 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
97859785
SysScanDescscan;
97869786
HeapTupledepTup;
97879787
ObjectAddressaddress;
9788+
ListCell*lc;
9789+
ListCell*prev;
9790+
ListCell*next;
97889791

97899792
/*
97909793
* Clear all the missing values if we're rewriting the table, since this
@@ -9915,14 +9918,20 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
99159918
if (relKind==RELKIND_INDEX||
99169919
relKind==RELKIND_PARTITIONED_INDEX)
99179920
{
9921+
/*
9922+
* Indexes that are directly dependent on the table
9923+
* might be regular indexes or constraint indexes.
9924+
* Constraint indexes typically have only indirect
9925+
* dependencies; but there are exceptions, notably
9926+
* partial exclusion constraints. Hence we must check
9927+
* whether the index depends on any constraint that's
9928+
* due to be rebuilt, which we'll do below after we've
9929+
* found all such constraints.
9930+
*/
99189931
Assert(foundObject.objectSubId==0);
9919-
if (!list_member_oid(tab->changedIndexOids,foundObject.objectId))
9920-
{
9921-
tab->changedIndexOids=lappend_oid(tab->changedIndexOids,
9922-
foundObject.objectId);
9923-
tab->changedIndexDefs=lappend(tab->changedIndexDefs,
9924-
pg_get_indexdef_string(foundObject.objectId));
9925-
}
9932+
tab->changedIndexOids=
9933+
list_append_unique_oid(tab->changedIndexOids,
9934+
foundObject.objectId);
99269935
}
99279936
elseif (relKind==RELKIND_SEQUENCE)
99289937
{
@@ -10073,6 +10082,41 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
1007310082

1007410083
systable_endscan(scan);
1007510084

10085+
/*
10086+
* Check the collected index OIDs to see which ones belong to the
10087+
* constraint(s) of the table, and drop those from the list of indexes
10088+
* that we need to process; rebuilding the constraints will handle them.
10089+
*/
10090+
prev=NULL;
10091+
for (lc=list_head(tab->changedIndexOids);lc;lc=next)
10092+
{
10093+
Oidindexoid=lfirst_oid(lc);
10094+
Oidconoid;
10095+
10096+
next=lnext(lc);
10097+
10098+
conoid=get_index_constraint(indexoid);
10099+
if (OidIsValid(conoid)&&
10100+
list_member_oid(tab->changedConstraintOids,conoid))
10101+
tab->changedIndexOids=list_delete_cell(tab->changedIndexOids,
10102+
lc,prev);
10103+
else
10104+
prev=lc;
10105+
}
10106+
10107+
/*
10108+
* Now collect the definitions of the indexes that must be rebuilt. (We
10109+
* could merge this into the previous loop, but it'd be more complicated
10110+
* for little gain.)
10111+
*/
10112+
foreach(lc,tab->changedIndexOids)
10113+
{
10114+
Oidindexoid=lfirst_oid(lc);
10115+
10116+
tab->changedIndexDefs=lappend(tab->changedIndexDefs,
10117+
pg_get_indexdef_string(indexoid));
10118+
}
10119+
1007610120
/*
1007710121
* Now scan for dependencies of this column on other things. The only
1007810122
* thing we should find is the dependency on the column datatype, which we

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

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1990,6 +1990,46 @@ select * from anothertab;
19901990
f | IT WAS NULL!
19911991
(3 rows)
19921992

1993+
drop table anothertab;
1994+
-- Test alter table column type with constraint indexes (cf. bug #15835)
1995+
create table anothertab(f1 int primary key, f2 int unique, f3 int, f4 int);
1996+
alter table anothertab
1997+
add exclude using btree (f3 with =);
1998+
alter table anothertab
1999+
add exclude using btree (f4 with =) where (f4 is not null);
2000+
\d anothertab
2001+
Table "public.anothertab"
2002+
Column | Type | Collation | Nullable | Default
2003+
--------+---------+-----------+----------+---------
2004+
f1 | integer | | not null |
2005+
f2 | integer | | |
2006+
f3 | integer | | |
2007+
f4 | integer | | |
2008+
Indexes:
2009+
"anothertab_pkey" PRIMARY KEY, btree (f1)
2010+
"anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
2011+
"anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
2012+
"anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
2013+
2014+
alter table anothertab alter column f1 type bigint;
2015+
alter table anothertab
2016+
alter column f2 type bigint,
2017+
alter column f3 type bigint,
2018+
alter column f4 type bigint;
2019+
\d anothertab
2020+
Table "public.anothertab"
2021+
Column | Type | Collation | Nullable | Default
2022+
--------+--------+-----------+----------+---------
2023+
f1 | bigint | | not null |
2024+
f2 | bigint | | |
2025+
f3 | bigint | | |
2026+
f4 | bigint | | |
2027+
Indexes:
2028+
"anothertab_pkey" PRIMARY KEY, btree (f1)
2029+
"anothertab_f2_key" UNIQUE CONSTRAINT, btree (f2)
2030+
"anothertab_f3_excl" EXCLUDE USING btree (f3 WITH =)
2031+
"anothertab_f4_excl" EXCLUDE USING btree (f4 WITH =) WHERE (f4 IS NOT NULL)
2032+
19932033
drop table anothertab;
19942034
create table another (f1 int, f2 text);
19952035
insert into another values(1, 'one');

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

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1357,6 +1357,23 @@ select * from anothertab;
13571357

13581358
droptable anothertab;
13591359

1360+
-- Test alter table column type with constraint indexes (cf. bug #15835)
1361+
createtableanothertab(f1intprimary key, f2int unique, f3int, f4int);
1362+
altertable anothertab
1363+
add exclude using btree (f3 with=);
1364+
altertable anothertab
1365+
add exclude using btree (f4 with=)where (f4is not null);
1366+
1367+
\d anothertab
1368+
altertable anothertab alter column f1 typebigint;
1369+
altertable anothertab
1370+
alter column f2 typebigint,
1371+
alter column f3 typebigint,
1372+
alter column f4 typebigint;
1373+
\d anothertab
1374+
1375+
droptable anothertab;
1376+
13601377
createtableanother (f1int, f2text);
13611378

13621379
insert into anothervalues(1,'one');

0 commit comments

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