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
Appearance settings

Commit 16f6906

Browse filesBrowse files
alvherrealvherre
committed
Remove bogus restriction from BEFORE UPDATE triggers
In trying to protect the user from inconsistent behavior, commit 487e986 "Enable BEFORE row-level triggers for partitioned tables" tried to prevent BEFORE UPDATE FOR EACH ROW triggers from moving the row from one partition to another. However, it turns out that the restriction is wrong in two ways: first, it fails spuriously, preventing valid situations from working, as in bug #16794; and second, they don't protect from any misbehavior, because tuple routing would cope anyway. Fix by removing that restriction. We keep the same restriction on BEFORE INSERT FOR EACH ROW triggers, though. It is valid and useful there. In the future we could remove it by having tuple reroute work for inserts as it does for updates. Backpatch to 13. Author: Álvaro Herrera <alvherre@alvh.no-ip.org> Reported-by: Phillip Menke <pg@pmenke.de> Discussion: https://postgr.es/m/16794-350a655580fbb9ae@postgresql.org
1 parent 7f1921c commit 16f6906
Copy full SHA for 16f6906

File tree

Expand file treeCollapse file tree

4 files changed

+77
-22
lines changed
Filter options
Expand file treeCollapse file tree

4 files changed

+77
-22
lines changed

‎doc/src/sgml/ddl.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ddl.sgml
+2-2Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4026,8 +4026,8 @@ ALTER INDEX measurement_city_id_logdate_key
40264026

40274027
<listitem>
40284028
<para>
4029-
<literal>BEFORE ROW</literal> triggers cannot change which partition
4030-
is the final destination for a new row.
4029+
<literal>BEFORE ROW</literal> triggers on <literal>INSERT</literal>
4030+
cannot change which partition is the final destination for a new row.
40314031
</para>
40324032
</listitem>
40334033

‎src/backend/commands/trigger.c

Copy file name to clipboardExpand all lines: src/backend/commands/trigger.c
-10Lines changed: 0 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -2726,16 +2726,6 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
27262726
{
27272727
ExecForceStoreHeapTuple(newtuple, newslot, false);
27282728

2729-
if (trigger->tgisclone &&
2730-
!ExecPartitionCheck(relinfo, newslot, estate, false))
2731-
ereport(ERROR,
2732-
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2733-
errmsg("moving row to another partition during a BEFORE trigger is not supported"),
2734-
errdetail("Before executing trigger \"%s\", the row was to be in partition \"%s.%s\".",
2735-
trigger->tgname,
2736-
get_namespace_name(RelationGetNamespace(relinfo->ri_RelationDesc)),
2737-
RelationGetRelationName(relinfo->ri_RelationDesc))));
2738-
27392729
/*
27402730
* If the tuple returned by the trigger / being stored, is the old
27412731
* row version, and the heap tuple passed to the trigger was

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/triggers.out
+48-9Lines changed: 48 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -2359,8 +2359,8 @@ insert into parted values (1, 1, 'uno uno v2'); -- fail
23592359
ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
23602360
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".
23612361
update parted set c = c || 'v3'; -- fail
2362-
ERROR: moving row to another partition during a BEFORE trigger is not supported
2363-
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".
2362+
ERROR: no partition of relation "parted" found for row
2363+
DETAIL: Partition key of the failing row contains (a) = (2).
23642364
create or replace function parted_trigfunc() returns trigger language plpgsql as $$
23652365
begin
23662366
new.b = new.b + 1;
@@ -2371,23 +2371,62 @@ insert into parted values (1, 1, 'uno uno v4'); -- fail
23712371
ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
23722372
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".
23732373
update parted set c = c || 'v5'; -- fail
2374-
ERROR: moving row to another partition during a BEFORE trigger is not supported
2375-
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".
2374+
ERROR: no partition of relation "parted_1" found for row
2375+
DETAIL: Partition key of the failing row contains (b) = (2).
23762376
create or replace function parted_trigfunc() returns trigger language plpgsql as $$
23772377
begin
2378-
new.c = new.c || ' and so';
2378+
new.c = new.c || ' did '|| TG_OP;
23792379
return new;
23802380
end;
23812381
$$;
23822382
insert into parted values (1, 1, 'uno uno'); -- works
23832383
update parted set c = c || ' v6'; -- works
23842384
select tableoid::regclass, * from parted;
2385-
tableoid | a | b | c
2386-
------------+---+---+--------------------------
2387-
parted_1_1 | 1 | 1 | uno uno v1 v6 and so
2388-
parted_1_1 | 1 | 1 | uno uno and so v6 and so
2385+
tableoid | a | b | c
2386+
------------+---+---+----------------------------------
2387+
parted_1_1 | 1 | 1 | uno uno v1 v6 did UPDATE
2388+
parted_1_1 | 1 | 1 | uno uno did INSERT v6 did UPDATE
23892389
(2 rows)
23902390

2391+
-- update itself moves tuple to new partition; trigger still works
2392+
truncate table parted;
2393+
create table parted_2 partition of parted for values in (2);
2394+
insert into parted values (1, 1, 'uno uno v5');
2395+
update parted set a = 2;
2396+
select tableoid::regclass, * from parted;
2397+
tableoid | a | b | c
2398+
----------+---+---+---------------------------------------------
2399+
parted_2 | 2 | 1 | uno uno v5 did INSERT did UPDATE did INSERT
2400+
(1 row)
2401+
2402+
-- both trigger and update change the partition
2403+
create or replace function parted_trigfunc2() returns trigger language plpgsql as $$
2404+
begin
2405+
new.a = new.a + 1;
2406+
return new;
2407+
end;
2408+
$$;
2409+
create trigger t2 before update on parted
2410+
for each row execute function parted_trigfunc2();
2411+
truncate table parted;
2412+
insert into parted values (1, 1, 'uno uno v6');
2413+
create table parted_3 partition of parted for values in (3);
2414+
update parted set a = a + 1;
2415+
select tableoid::regclass, * from parted;
2416+
tableoid | a | b | c
2417+
----------+---+---+---------------------------------------------
2418+
parted_3 | 3 | 1 | uno uno v6 did INSERT did UPDATE did INSERT
2419+
(1 row)
2420+
2421+
-- there's no partition for a=0, but this update works anyway because
2422+
-- the trigger causes the tuple to be routed to another partition
2423+
update parted set a = 0;
2424+
select tableoid::regclass, * from parted;
2425+
tableoid | a | b | c
2426+
------------+---+---+-------------------------------------------------------------------
2427+
parted_1_1 | 1 | 1 | uno uno v6 did INSERT did UPDATE did INSERT did UPDATE did INSERT
2428+
(1 row)
2429+
23912430
drop table parted;
23922431
create table parted (a int, b int, c text) partition by list ((a + b));
23932432
create or replace function parted_trigfunc() returns trigger language plpgsql as $$

‎src/test/regress/sql/triggers.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/triggers.sql
+27-1Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1633,14 +1633,40 @@ insert into parted values (1, 1, 'uno uno v4'); -- fail
16331633
update parted set c = c || 'v5'; -- fail
16341634
create or replace function parted_trigfunc() returns trigger language plpgsql as $$
16351635
begin
1636-
new.c = new.c || ' and so';
1636+
new.c = new.c || ' did '|| TG_OP;
16371637
return new;
16381638
end;
16391639
$$;
16401640
insert into parted values (1, 1, 'uno uno'); -- works
16411641
update parted set c = c || ' v6'; -- works
16421642
select tableoid::regclass, * from parted;
16431643

1644+
-- update itself moves tuple to new partition; trigger still works
1645+
truncate table parted;
1646+
create table parted_2 partition of parted for values in (2);
1647+
insert into parted values (1, 1, 'uno uno v5');
1648+
update parted set a = 2;
1649+
select tableoid::regclass, * from parted;
1650+
1651+
-- both trigger and update change the partition
1652+
create or replace function parted_trigfunc2() returns trigger language plpgsql as $$
1653+
begin
1654+
new.a = new.a + 1;
1655+
return new;
1656+
end;
1657+
$$;
1658+
create trigger t2 before update on parted
1659+
for each row execute function parted_trigfunc2();
1660+
truncate table parted;
1661+
insert into parted values (1, 1, 'uno uno v6');
1662+
create table parted_3 partition of parted for values in (3);
1663+
update parted set a = a + 1;
1664+
select tableoid::regclass, * from parted;
1665+
-- there's no partition for a=0, but this update works anyway because
1666+
-- the trigger causes the tuple to be routed to another partition
1667+
update parted set a = 0;
1668+
select tableoid::regclass, * from parted;
1669+
16441670
drop table parted;
16451671
create table parted (a int, b int, c text) partition by list ((a + b));
16461672
create or replace function parted_trigfunc() returns trigger language plpgsql as $$

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.