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

Commit487e986

Browse files
committed
Enable BEFORE row-level triggers for partitioned tables
... with the limitation that the tuple must remain in the samepartition.Reviewed-by: Ashutosh BapatDiscussion:https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql
1 parentb029395 commit487e986

File tree

8 files changed

+161
-23
lines changed

8 files changed

+161
-23
lines changed

‎doc/src/sgml/ref/create_trigger.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -526,7 +526,7 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
526526
Creating a row-level trigger on a partitioned table will cause identical
527527
triggers to be created in all its existing partitions; and any partitions
528528
created or attached later will contain an identical trigger, too.
529-
Triggers on partitioned tables mayonly be <literal>AFTER</literal>.
529+
Triggers on partitioned tables maynot be <literal>INSTEAD OF</literal>.
530530
</para>
531531

532532
<para>

‎doc/src/sgml/trigger.sgml

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -116,8 +116,7 @@
116116
operated on, while row-level <literal>AFTER</literal> triggers fire at the end of
117117
the statement (but before any statement-level <literal>AFTER</literal> triggers).
118118
These types of triggers may only be defined on tables and
119-
foreign tables, not views; <literal>BEFORE</literal> row-level triggers may not
120-
be defined on partitioned tables.
119+
foreign tables, not views.
121120
<literal>INSTEAD OF</literal> triggers may only be
122121
defined on views, and only at row level; they fire immediately as each
123122
row in the view is identified as needing to be operated on.

‎src/backend/commands/tablecmds.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16546,7 +16546,8 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
1654616546
/*
1654716547
* Complain if we find an unexpected trigger type.
1654816548
*/
16549-
if (!TRIGGER_FOR_AFTER(trigForm->tgtype))
16549+
if (!TRIGGER_FOR_BEFORE(trigForm->tgtype) &&
16550+
!TRIGGER_FOR_AFTER(trigForm->tgtype))
1655016551
elog(ERROR, "unexpected trigger \"%s\" found",
1655116552
NameStr(trigForm->tgname));
1655216553

‎src/backend/commands/trigger.c

Lines changed: 28 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -221,18 +221,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
221221
*/
222222
if (stmt->row)
223223
{
224-
/*
225-
* BEFORE triggers FOR EACH ROW are forbidden, because they would
226-
* allow the user to direct the row to another partition, which
227-
* isn't implemented in the executor.
228-
*/
229-
if (stmt->timing!=TRIGGER_TYPE_AFTER)
230-
ereport(ERROR,
231-
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
232-
errmsg("\"%s\" is a partitioned table",
233-
RelationGetRelationName(rel)),
234-
errdetail("Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.")));
235-
236224
/*
237225
* Disallow use of transition tables.
238226
*
@@ -1658,6 +1646,7 @@ RelationBuildTriggers(Relation relation)
16581646
build->tgtype=pg_trigger->tgtype;
16591647
build->tgenabled=pg_trigger->tgenabled;
16601648
build->tgisinternal=pg_trigger->tgisinternal;
1649+
build->tgisclone=OidIsValid(pg_trigger->tgparentid);
16611650
build->tgconstrrelid=pg_trigger->tgconstrrelid;
16621651
build->tgconstrindid=pg_trigger->tgconstrindid;
16631652
build->tgconstraint=pg_trigger->tgconstraint;
@@ -1961,6 +1950,8 @@ equalTriggerDescs(TriggerDesc *trigdesc1, TriggerDesc *trigdesc2)
19611950
return false;
19621951
if (trig1->tgisinternal!=trig2->tgisinternal)
19631952
return false;
1953+
if (trig1->tgisclone!=trig2->tgisclone)
1954+
return false;
19641955
if (trig1->tgconstrrelid!=trig2->tgconstrrelid)
19651956
return false;
19661957
if (trig1->tgconstrindid!=trig2->tgconstrindid)
@@ -2247,6 +2238,21 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo *relinfo,
22472238
{
22482239
ExecForceStoreHeapTuple(newtuple,slot, false);
22492240

2241+
/*
2242+
* After a tuple in a partition goes through a trigger, the user
2243+
* could have changed the partition key enough that the tuple
2244+
* no longer fits the partition. Verify that.
2245+
*/
2246+
if (trigger->tgisclone&&
2247+
!ExecPartitionCheck(relinfo,slot,estate, false))
2248+
ereport(ERROR,
2249+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2250+
errmsg("moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported"),
2251+
errdetail("Before executing trigger \"%s\", the row was to be in partition \"%s.%s\".",
2252+
trigger->tgname,
2253+
get_namespace_name(RelationGetNamespace(relinfo->ri_RelationDesc)),
2254+
RelationGetRelationName(relinfo->ri_RelationDesc))));
2255+
22502256
if (should_free)
22512257
heap_freetuple(oldtuple);
22522258

@@ -2741,6 +2747,16 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
27412747
{
27422748
ExecForceStoreHeapTuple(newtuple,newslot, false);
27432749

2750+
if (trigger->tgisclone&&
2751+
!ExecPartitionCheck(relinfo,newslot,estate, false))
2752+
ereport(ERROR,
2753+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2754+
errmsg("moving row to another partition during a BEFORE trigger is not supported"),
2755+
errdetail("Before executing trigger \"%s\", the row was to be in partition \"%s.%s\".",
2756+
trigger->tgname,
2757+
get_namespace_name(RelationGetNamespace(relinfo->ri_RelationDesc)),
2758+
RelationGetRelationName(relinfo->ri_RelationDesc))));
2759+
27442760
/*
27452761
* If the tuple returned by the trigger / being stored, is the old
27462762
* row version, and the heap tuple passed to the trigger was

‎src/backend/partitioning/partdesc.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -307,7 +307,7 @@ CreatePartitionDirectory(MemoryContext mcxt)
307307
*
308308
* The purpose of this function is to ensure that we get the same
309309
* PartitionDesc for each relation every time we look it up. In the
310-
* face ofcurrent DDL, different PartitionDescs may be constructed with
310+
* face ofconcurrent DDL, different PartitionDescs may be constructed with
311311
* different views of the catalog state, but any single particular OID
312312
* will always get the same PartitionDesc for as long as the same
313313
* PartitionDirectory is used.

‎src/include/utils/reltrigger.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@ typedef struct Trigger
2929
int16tgtype;
3030
chartgenabled;
3131
booltgisinternal;
32+
booltgisclone;
3233
Oidtgconstrrelid;
3334
Oidtgconstrindid;
3435
Oidtgconstraint;

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

Lines changed: 74 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1958,10 +1958,6 @@ drop table my_table;
19581958
create table parted_trig (a int) partition by list (a);
19591959
create function trigger_nothing() returns trigger
19601960
language plpgsql as $$ begin end; $$;
1961-
create trigger failed before insert or update or delete on parted_trig
1962-
for each row execute procedure trigger_nothing();
1963-
ERROR: "parted_trig" is a partitioned table
1964-
DETAIL: Partitioned tables cannot have BEFORE / FOR EACH ROW triggers.
19651961
create trigger failed instead of update on parted_trig
19661962
for each row execute procedure trigger_nothing();
19671963
ERROR: "parted_trig" is a table
@@ -2246,6 +2242,80 @@ NOTICE: aasvogel <- woof!
22462242
NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)
22472243
NOTICE: trigger parted_trig_odd on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)
22482244
drop table parted_irreg_ancestor;
2245+
-- Before triggers and partitions
2246+
create table parted (a int, b int, c text) partition by list (a);
2247+
create table parted_1 partition of parted for values in (1)
2248+
partition by list (b);
2249+
create table parted_1_1 partition of parted_1 for values in (1);
2250+
create function parted_trigfunc() returns trigger language plpgsql as $$
2251+
begin
2252+
new.a = new.a + 1;
2253+
return new;
2254+
end;
2255+
$$;
2256+
insert into parted values (1, 1, 'uno uno v1'); -- works
2257+
create trigger t before insert or update or delete on parted
2258+
for each row execute function parted_trigfunc();
2259+
insert into parted values (1, 1, 'uno uno v2'); -- fail
2260+
ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
2261+
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".
2262+
update parted set c = c || 'v3'; -- fail
2263+
ERROR: moving row to another partition during a BEFORE trigger is not supported
2264+
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".
2265+
create or replace function parted_trigfunc() returns trigger language plpgsql as $$
2266+
begin
2267+
new.b = new.b + 1;
2268+
return new;
2269+
end;
2270+
$$;
2271+
insert into parted values (1, 1, 'uno uno v4'); -- fail
2272+
ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
2273+
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".
2274+
update parted set c = c || 'v5'; -- fail
2275+
ERROR: moving row to another partition during a BEFORE trigger is not supported
2276+
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1_1".
2277+
create or replace function parted_trigfunc() returns trigger language plpgsql as $$
2278+
begin
2279+
new.c = new.c || ' and so';
2280+
return new;
2281+
end;
2282+
$$;
2283+
insert into parted values (1, 1, 'uno uno'); -- works
2284+
update parted set c = c || ' v6'; -- works
2285+
select tableoid::regclass, * from parted;
2286+
tableoid | a | b | c
2287+
------------+---+---+--------------------------
2288+
parted_1_1 | 1 | 1 | uno uno v1 v6 and so
2289+
parted_1_1 | 1 | 1 | uno uno and so v6 and so
2290+
(2 rows)
2291+
2292+
drop table parted;
2293+
create table parted (a int, b int, c text) partition by list ((a + b));
2294+
create or replace function parted_trigfunc() returns trigger language plpgsql as $$
2295+
begin
2296+
new.a = new.a + new.b;
2297+
return new;
2298+
end;
2299+
$$;
2300+
create table parted_1 partition of parted for values in (1, 2);
2301+
create table parted_2 partition of parted for values in (3, 4);
2302+
create trigger t before insert or update on parted
2303+
for each row execute function parted_trigfunc();
2304+
insert into parted values (0, 1, 'zero win');
2305+
insert into parted values (1, 1, 'one fail');
2306+
ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
2307+
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_1".
2308+
insert into parted values (1, 2, 'two fail');
2309+
ERROR: moving row to another partition during a BEFORE FOR EACH ROW trigger is not supported
2310+
DETAIL: Before executing trigger "t", the row was to be in partition "public.parted_2".
2311+
select * from parted;
2312+
a | b | c
2313+
---+---+----------
2314+
1 | 1 | zero win
2315+
(1 row)
2316+
2317+
drop table parted;
2318+
drop function parted_trigfunc();
22492319
--
22502320
-- Constraint triggers and partitioned tables
22512321
create table parted_constr_ancestor (a int, b text)

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

Lines changed: 53 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1348,8 +1348,6 @@ drop table my_table;
13481348
createtableparted_trig (aint) partition by list (a);
13491349
createfunctiontrigger_nothing() returns trigger
13501350
language plpgsqlas $$begin end; $$;
1351-
createtriggerfailed before insertorupdateordeleteon parted_trig
1352-
for each row execute procedure trigger_nothing();
13531351
createtriggerfailed instead ofupdateon parted_trig
13541352
for each row execute procedure trigger_nothing();
13551353
createtriggerfailed afterupdateon parted_trig
@@ -1561,6 +1559,59 @@ insert into parted1_irreg values ('aardwolf', 2);
15611559
insert into parted_irreg_ancestorvalues ('aasvogel',3);
15621560
droptable parted_irreg_ancestor;
15631561

1562+
-- Before triggers and partitions
1563+
createtableparted (aint, bint, ctext) partition by list (a);
1564+
createtableparted_1 partition of parted forvaluesin (1)
1565+
partition by list (b);
1566+
createtableparted_1_1 partition of parted_1 forvaluesin (1);
1567+
createfunctionparted_trigfunc() returns trigger language plpgsqlas $$
1568+
begin
1569+
new.a=new.a+1;
1570+
return new;
1571+
end;
1572+
$$;
1573+
insert into partedvalues (1,1,'uno uno v1');-- works
1574+
createtriggert before insertorupdateordeleteon parted
1575+
for each row execute function parted_trigfunc();
1576+
insert into partedvalues (1,1,'uno uno v2');-- fail
1577+
update partedset c= c||'v3';-- fail
1578+
create or replacefunctionparted_trigfunc() returns trigger language plpgsqlas $$
1579+
begin
1580+
new.b=new.b+1;
1581+
return new;
1582+
end;
1583+
$$;
1584+
insert into partedvalues (1,1,'uno uno v4');-- fail
1585+
update partedset c= c||'v5';-- fail
1586+
create or replacefunctionparted_trigfunc() returns trigger language plpgsqlas $$
1587+
begin
1588+
new.c=new.c||' and so';
1589+
return new;
1590+
end;
1591+
$$;
1592+
insert into partedvalues (1,1,'uno uno');-- works
1593+
update partedset c= c||' v6';-- works
1594+
select tableoid::regclass,*from parted;
1595+
1596+
droptable parted;
1597+
createtableparted (aint, bint, ctext) partition by list ((a+ b));
1598+
create or replacefunctionparted_trigfunc() returns trigger language plpgsqlas $$
1599+
begin
1600+
new.a=new.a+new.b;
1601+
return new;
1602+
end;
1603+
$$;
1604+
createtableparted_1 partition of parted forvaluesin (1,2);
1605+
createtableparted_2 partition of parted forvaluesin (3,4);
1606+
createtriggert before insertorupdateon parted
1607+
for each row execute function parted_trigfunc();
1608+
insert into partedvalues (0,1,'zero win');
1609+
insert into partedvalues (1,1,'one fail');
1610+
insert into partedvalues (1,2,'two fail');
1611+
select*from parted;
1612+
droptable parted;
1613+
dropfunction parted_trigfunc();
1614+
15641615
--
15651616
-- Constraint triggers and partitioned tables
15661617
createtableparted_constr_ancestor (aint, btext)

0 commit comments

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