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

Commite4baecf

Browse files
committed
Fix SET CONSTRAINTS .. DEFERRED on partitioned tables
SET CONSTRAINTS ... DEFERRED failed on partitioned tables, because of asanity check that ensures that the affected constraints have triggers.On partitioned tables, the triggers are in the leaf partitions, not inthe partitioned relations themselves, so the sanity check fails.Removing the sanity check solves the problem, because the code needed tosupport the case is already there.Backpatch to 11.Note: deferred unique constraints are not affected by this bug, becausethey do have triggers in the parent partitioned table. I did not add atest for this scenario.Discussion:https://postgr.es/m/20191105212915.GA11324@alvherre.pgsql
1 parentb49b7f9 commite4baecf

File tree

3 files changed

+38
-13
lines changed

3 files changed

+38
-13
lines changed

‎src/backend/commands/trigger.c‎

Lines changed: 0 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -5519,13 +5519,10 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
55195519
foreach(lc,conoidlist)
55205520
{
55215521
Oidconoid=lfirst_oid(lc);
5522-
boolfound;
55235522
ScanKeyDataskey;
55245523
SysScanDesctgscan;
55255524
HeapTuplehtup;
55265525

5527-
found= false;
5528-
55295526
ScanKeyInit(&skey,
55305527
Anum_pg_trigger_tgconstraint,
55315528
BTEqualStrategyNumber,F_OIDEQ,
@@ -5547,16 +5544,9 @@ AfterTriggerSetState(ConstraintsSetStmt *stmt)
55475544
if (pg_trigger->tgdeferrable)
55485545
tgoidlist=lappend_oid(tgoidlist,
55495546
HeapTupleGetOid(htup));
5550-
5551-
found= true;
55525547
}
55535548

55545549
systable_endscan(tgscan);
5555-
5556-
/* Safety check: a deferrable constraint should have triggers */
5557-
if (!found)
5558-
elog(ERROR,"no triggers found for constraint with OID %u",
5559-
conoid);
55605550
}
55615551

55625552
heap_close(tgrel,AccessShareLock);

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

Lines changed: 19 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1926,6 +1926,23 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
19261926
alter table fkpart2.fk_part_1 drop constraint fkey;-- ok
19271927
alter table fkpart2.fk_part_1_1 drop constraint my_fkey;-- doesn't exist
19281928
ERROR: constraint "my_fkey" of relation "fk_part_1_1" does not exist
1929+
-- verify constraint deferrability
1930+
create schema fkpart3
1931+
create table pkey (a int primary key)
1932+
create table fk_part (a int, constraint fkey foreign key (a) references fkpart3.pkey deferrable initially immediate) partition by list (a)
1933+
create table fk_part_1 partition of fkpart3.fk_part for values in (1) partition by list (a)
1934+
create table fk_part_1_1 partition of fkpart3.fk_part_1 for values in (1)
1935+
create table fk_part_2 partition of fkpart3.fk_part for values in (2);
1936+
begin;
1937+
set constraints fkpart3.fkey deferred;
1938+
insert into fkpart3.fk_part values (1);
1939+
insert into fkpart3.pkey values (1);
1940+
commit;
1941+
begin;
1942+
set constraints fkpart3.fkey deferred;
1943+
delete from fkpart3.pkey;
1944+
delete from fkpart3.fk_part;
1945+
commit;
19291946
-- ensure we check partitions are "not used" when dropping constraints
19301947
CREATE SCHEMA fkpart8
19311948
CREATE TABLE tbl1(f1 int PRIMARY KEY)
@@ -1938,6 +1955,6 @@ ALTER TABLE fkpart8.tbl2 DROP CONSTRAINT tbl2_f1_fkey;
19381955
ERROR: cannot ALTER TABLE "tbl2_p1" because it has pending trigger events
19391956
COMMIT;
19401957
\set VERBOSITY terse\\ -- suppress cascade details
1941-
drop schema fkpart0, fkpart1, fkpart2, fkpart8 cascade;
1942-
NOTICE: drop cascades to10 other objects
1958+
drop schema fkpart0, fkpart1, fkpart2,fkpart3,fkpart8 cascade;
1959+
NOTICE: drop cascades to12 other objects
19431960
\set VERBOSITY default

‎src/test/regress/sql/foreign_key.sql‎

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1380,6 +1380,24 @@ alter table fkpart2.fk_part detach partition fkpart2.fk_part_1;
13801380
altertablefkpart2.fk_part_1 dropconstraint fkey;-- ok
13811381
altertablefkpart2.fk_part_1_1 dropconstraint my_fkey;-- doesn't exist
13821382

1383+
-- verify constraint deferrability
1384+
createschemafkpart3
1385+
createtablepkey (aintprimary key)
1386+
createtablefk_part (aint,constraint fkeyforeign key (a)referencesfkpart3.pkey deferrable initially immediate) partition by list (a)
1387+
createtablefk_part_1 partition offkpart3.fk_part forvaluesin (1) partition by list (a)
1388+
createtablefk_part_1_1 partition offkpart3.fk_part_1 forvaluesin (1)
1389+
createtablefk_part_2 partition offkpart3.fk_part forvaluesin (2);
1390+
begin;
1391+
set constraintsfkpart3.fkey deferred;
1392+
insert intofkpart3.fk_partvalues (1);
1393+
insert intofkpart3.pkeyvalues (1);
1394+
commit;
1395+
begin;
1396+
set constraintsfkpart3.fkey deferred;
1397+
deletefromfkpart3.pkey;
1398+
deletefromfkpart3.fk_part;
1399+
commit;
1400+
13831401
-- ensure we check partitions are "not used" when dropping constraints
13841402
CREATESCHEMAfkpart8
13851403
CREATETABLEtbl1(f1intPRIMARY KEY)
@@ -1392,5 +1410,5 @@ ALTER TABLE fkpart8.tbl2 DROP CONSTRAINT tbl2_f1_fkey;
13921410
COMMIT;
13931411

13941412
\set VERBOSITY terse\\-- suppress cascade details
1395-
dropschema fkpart0, fkpart1, fkpart2, fkpart8 cascade;
1413+
dropschema fkpart0, fkpart1, fkpart2,fkpart3,fkpart8 cascade;
13961414
\set VERBOSITY default

0 commit comments

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