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

Commit3d7a968

Browse files
committed
Obtain required table lock during cross-table updates, redux.
Commits8319e5c et al missed the fact that ATPostAlterTypeCleanupcontains three calls to ATPostAlterTypeParse, and the other twoalso need protection against passing a relid that we don't yethave lock on. Add similar logic to those code paths, and addsome test cases demonstrating the need for it.In v18 and master, the test cases demonstrate that there's abehavioral discrepancy between stored generated columns and virtualgenerated columns: we disallow changing the expression of a storedcolumn if it's used in any composite-type columns, but not that ofa virtual column. Since the expression isn't actually relevant toeither sort of composite-type usage, this prohibition seemsunnecessary; but changing it is a matter for separate discussion.For now we are just documenting the existing behavior.Reported-by: jian he <jian.universality@gmail.com>Author: jian he <jian.universality@gmail.com>Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Discussion: CACJufxGKJtGNRRSXfwMW9SqVOPEMdP17BJ7DsBf=tNsv9pWU9g@mail.gmail.comBackpatch-through: 13
1 parent0cd7fca commit3d7a968

File tree

7 files changed

+85
-0
lines changed

7 files changed

+85
-0
lines changed

‎src/backend/commands/tablecmds.c‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15488,6 +15488,14 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
1548815488
Oidrelid;
1548915489

1549015490
relid = IndexGetRelation(oldId, false);
15491+
15492+
/*
15493+
* As above, make sure we have lock on the index's table if it's not
15494+
* the same table.
15495+
*/
15496+
if (relid != tab->relid)
15497+
LockRelationOid(relid, AccessExclusiveLock);
15498+
1549115499
ATPostAlterTypeParse(oldId, relid, InvalidOid,
1549215500
(char *) lfirst(def_item),
1549315501
wqueue, lockmode, tab->rewrite);
@@ -15504,6 +15512,20 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
1550415512
Oidrelid;
1550515513

1550615514
relid = StatisticsGetRelation(oldId, false);
15515+
15516+
/*
15517+
* As above, make sure we have lock on the statistics object's table
15518+
* if it's not the same table. However, we take
15519+
* ShareUpdateExclusiveLock here, aligning with the lock level used in
15520+
* CreateStatistics and RemoveStatisticsById.
15521+
*
15522+
* CAUTION: this should be done after all cases that grab
15523+
* AccessExclusiveLock, else we risk causing deadlock due to needing
15524+
* to promote our table lock.
15525+
*/
15526+
if (relid != tab->relid)
15527+
LockRelationOid(relid, ShareUpdateExclusiveLock);
15528+
1550715529
ATPostAlterTypeParse(oldId, relid, InvalidOid,
1550815530
(char *) lfirst(def_item),
1550915531
wqueue, lockmode, tab->rewrite);

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4750,6 +4750,14 @@ create table attbl(a int);
47504750
create table atref(b attbl check ((b).a is not null));
47514751
alter table attbl alter column a type numeric; -- someday this should work
47524752
ERROR: cannot alter table "attbl" because column "atref.b" uses its row type
4753+
alter table atref drop constraint atref_b_check;
4754+
create statistics atref_stat on ((b).a is not null) from atref;
4755+
alter table attbl alter column a type numeric; -- someday this should work
4756+
ERROR: cannot alter table "attbl" because column "atref.b" uses its row type
4757+
drop statistics atref_stat;
4758+
create index atref_idx on atref (((b).a));
4759+
alter table attbl alter column a type numeric; -- someday this should work
4760+
ERROR: cannot alter table "attbl" because column "atref.b" uses its row type
47534761
drop table attbl, atref;
47544762
/* End test case for bug #18970 */
47554763
-- Test that ALTER TABLE rewrite preserves a clustered index

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1313,6 +1313,18 @@ CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c te
13131313
CREATE TABLE gtest31_2 (x int, y gtest31_1);
13141314
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
13151315
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
1316+
-- bug #18970: these cases are unsupported, but make sure they fail cleanly
1317+
ALTER TABLE gtest31_2 ADD CONSTRAINT cc CHECK ((y).b IS NOT NULL);
1318+
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1');
1319+
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
1320+
ALTER TABLE gtest31_2 DROP CONSTRAINT cc;
1321+
CREATE STATISTICS gtest31_2_stat ON ((y).b is not null) FROM gtest31_2;
1322+
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2');
1323+
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
1324+
DROP STATISTICS gtest31_2_stat;
1325+
CREATE INDEX gtest31_2_y_idx ON gtest31_2(((y).b));
1326+
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3');
1327+
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
13161328
DROP TABLE gtest31_1, gtest31_2;
13171329
-- Check it for a partitioned table, too
13181330
CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') STORED, c text) PARTITION BY LIST (a);

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1283,6 +1283,15 @@ CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c t
12831283
CREATE TABLE gtest31_2 (x int, y gtest31_1);
12841284
ALTER TABLE gtest31_1 ALTER COLUMN b TYPE varchar; -- fails
12851285
ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
1286+
-- bug #18970
1287+
ALTER TABLE gtest31_2 ADD CONSTRAINT cc CHECK ((y).b IS NOT NULL);
1288+
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello1');
1289+
ALTER TABLE gtest31_2 DROP CONSTRAINT cc;
1290+
CREATE STATISTICS gtest31_2_stat ON ((y).b is not null) FROM gtest31_2;
1291+
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2');
1292+
DROP STATISTICS gtest31_2_stat;
1293+
CREATE INDEX gtest31_2_y_idx ON gtest31_2(((y).b));
1294+
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3');
12861295
DROP TABLE gtest31_1, gtest31_2;
12871296
-- Check it for a partitioned table, too
12881297
CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text) PARTITION BY LIST (a);

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

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3074,6 +3074,14 @@ drop table attbl, atref;
30743074
createtableattbl(aint);
30753075
createtableatref(b attblcheck ((b).ais not null));
30763076
altertable attbl alter column a typenumeric;-- someday this should work
3077+
altertable atref dropconstraint atref_b_check;
3078+
3079+
create statistics atref_staton ((b).ais not null)from atref;
3080+
altertable attbl alter column a typenumeric;-- someday this should work
3081+
drop statistics atref_stat;
3082+
3083+
createindexatref_idxon atref (((b).a));
3084+
altertable attbl alter column a typenumeric;-- someday this should work
30773085
droptable attbl, atref;
30783086

30793087
/* End test case for bug #18970*/

‎src/test/regress/sql/generated_stored.sql‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -595,6 +595,19 @@ ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
595595
CREATETABLEgtest31_1 (aint, btext GENERATED ALWAYSAS ('hello') STORED, ctext);
596596
CREATETABLEgtest31_2 (xint, y gtest31_1);
597597
ALTERTABLE gtest31_1 ALTER COLUMN b TYPEvarchar;-- fails
598+
599+
-- bug #18970: these cases are unsupported, but make sure they fail cleanly
600+
ALTERTABLE gtest31_2 ADDCONSTRAINT ccCHECK ((y).bIS NOT NULL);
601+
ALTERTABLE gtest31_1 ALTER COLUMN bSET EXPRESSIONAS ('hello1');
602+
ALTERTABLE gtest31_2 DROPCONSTRAINT cc;
603+
604+
CREATE STATISTICS gtest31_2_statON ((y).bis not null)FROM gtest31_2;
605+
ALTERTABLE gtest31_1 ALTER COLUMN bSET EXPRESSIONAS ('hello2');
606+
DROP STATISTICS gtest31_2_stat;
607+
608+
CREATEINDEXgtest31_2_y_idxON gtest31_2(((y).b));
609+
ALTERTABLE gtest31_1 ALTER COLUMN bSET EXPRESSIONAS ('hello3');
610+
598611
DROPTABLE gtest31_1, gtest31_2;
599612

600613
-- Check it for a partitioned table, too

‎src/test/regress/sql/generated_virtual.sql‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -646,6 +646,19 @@ ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
646646
CREATETABLEgtest31_1 (aint, btext GENERATED ALWAYSAS ('hello') VIRTUAL, ctext);
647647
CREATETABLEgtest31_2 (xint, y gtest31_1);
648648
ALTERTABLE gtest31_1 ALTER COLUMN b TYPEvarchar;-- fails
649+
650+
-- bug #18970
651+
ALTERTABLE gtest31_2 ADDCONSTRAINT ccCHECK ((y).bIS NOT NULL);
652+
ALTERTABLE gtest31_1 ALTER COLUMN bSET EXPRESSIONAS ('hello1');
653+
ALTERTABLE gtest31_2 DROPCONSTRAINT cc;
654+
655+
CREATE STATISTICS gtest31_2_statON ((y).bis not null)FROM gtest31_2;
656+
ALTERTABLE gtest31_1 ALTER COLUMN bSET EXPRESSIONAS ('hello2');
657+
DROP STATISTICS gtest31_2_stat;
658+
659+
CREATEINDEXgtest31_2_y_idxON gtest31_2(((y).b));
660+
ALTERTABLE gtest31_1 ALTER COLUMN bSET EXPRESSIONAS ('hello3');
661+
649662
DROPTABLE gtest31_1, gtest31_2;
650663

651664
-- Check it for a partitioned table, too

0 commit comments

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