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

Commit8c55244

Browse files
committed
Fix transition tables for ON CONFLICT.
We now disallow having triggers with both transition tables and ONINSERT OR UPDATE (which was a PG extension to the spec anyway),because in this case it's not at all clear how the transition tablesshould work for an INSERT ... ON CONFLICT query. Separate ON INSERTand ON UPDATE triggers with transition tables are allowed, and thetransition tables for these reflect only the inserted and only theupdated tuples respectively.Patch by Thomas MunroDiscussion:https://postgr.es/m/CAEepm%3D11KHQ0JmETJQihSvhZB5mUZL2xrqHeXbCeLhDiqQ39%3Dw%40mail.gmail.com
1 parentc46c0e5 commit8c55244

File tree

6 files changed

+155
-13
lines changed

6 files changed

+155
-13
lines changed

‎src/backend/commands/trigger.c

Lines changed: 52 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -401,6 +401,23 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
401401
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
402402
errmsg("TRUNCATE triggers with transition tables are not supported")));
403403

404+
/*
405+
* We currently don't allow multi-event triggers ("INSERT OR
406+
* UPDATE") with transition tables, because it's not clear how to
407+
* handle INSERT ... ON CONFLICT statements which can fire both
408+
* INSERT and UPDATE triggers. We show the inserted tuples to
409+
* INSERT triggers and the updated tuples to UPDATE triggers, but
410+
* it's not yet clear what INSERT OR UPDATE trigger should see.
411+
* This restriction could be lifted if we can decide on the right
412+
* semantics in a later release.
413+
*/
414+
if (((TRIGGER_FOR_INSERT(tgtype) ?1 :0)+
415+
(TRIGGER_FOR_UPDATE(tgtype) ?1 :0)+
416+
(TRIGGER_FOR_DELETE(tgtype) ?1 :0))!=1)
417+
ereport(ERROR,
418+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
419+
errmsg("Transition tables cannot be specified for triggers with more than one event")));
420+
404421
if (tt->isNew)
405422
{
406423
if (!(TRIGGER_FOR_INSERT(tgtype)||
@@ -2128,8 +2145,10 @@ MakeTransitionCaptureState(TriggerDesc *trigdesc)
21282145
CurrentResourceOwner=CurTransactionResourceOwner;
21292146
if (trigdesc->trig_delete_old_table||trigdesc->trig_update_old_table)
21302147
state->tcs_old_tuplestore=tuplestore_begin_heap(false, false,work_mem);
2131-
if (trigdesc->trig_insert_new_table||trigdesc->trig_update_new_table)
2132-
state->tcs_new_tuplestore=tuplestore_begin_heap(false, false,work_mem);
2148+
if (trigdesc->trig_insert_new_table)
2149+
state->tcs_insert_tuplestore=tuplestore_begin_heap(false, false,work_mem);
2150+
if (trigdesc->trig_update_new_table)
2151+
state->tcs_update_tuplestore=tuplestore_begin_heap(false, false,work_mem);
21332152
}
21342153
PG_CATCH();
21352154
{
@@ -2147,8 +2166,10 @@ MakeTransitionCaptureState(TriggerDesc *trigdesc)
21472166
void
21482167
DestroyTransitionCaptureState(TransitionCaptureState*tcs)
21492168
{
2150-
if (tcs->tcs_new_tuplestore!=NULL)
2151-
tuplestore_end(tcs->tcs_new_tuplestore);
2169+
if (tcs->tcs_insert_tuplestore!=NULL)
2170+
tuplestore_end(tcs->tcs_insert_tuplestore);
2171+
if (tcs->tcs_update_tuplestore!=NULL)
2172+
tuplestore_end(tcs->tcs_update_tuplestore);
21522173
if (tcs->tcs_old_tuplestore!=NULL)
21532174
tuplestore_end(tcs->tcs_old_tuplestore);
21542175
pfree(tcs);
@@ -3993,7 +4014,29 @@ AfterTriggerExecute(AfterTriggerEvent event,
39934014
if (LocTriggerData.tg_trigger->tgoldtable)
39944015
LocTriggerData.tg_oldtable=transition_capture->tcs_old_tuplestore;
39954016
if (LocTriggerData.tg_trigger->tgnewtable)
3996-
LocTriggerData.tg_newtable=transition_capture->tcs_new_tuplestore;
4017+
{
4018+
/*
4019+
* Currently a trigger with transition tables may only be defined
4020+
* for a single event type (here AFTER INSERT or AFTER UPDATE, but
4021+
* not AFTER INSERT OR ...).
4022+
*/
4023+
Assert((TRIGGER_FOR_INSERT(LocTriggerData.tg_trigger->tgtype)!=0) ^
4024+
(TRIGGER_FOR_UPDATE(LocTriggerData.tg_trigger->tgtype)!=0));
4025+
4026+
/*
4027+
* Show either the insert or update new tuple images, depending on
4028+
* which event type the trigger was registered for. A single
4029+
* statement may have produced both in the case of INSERT ... ON
4030+
* CONFLICT ... DO UPDATE, and in that case the event determines
4031+
* which tuplestore the trigger sees as the NEW TABLE.
4032+
*/
4033+
if (TRIGGER_FOR_INSERT(LocTriggerData.tg_trigger->tgtype))
4034+
LocTriggerData.tg_newtable=
4035+
transition_capture->tcs_insert_tuplestore;
4036+
else
4037+
LocTriggerData.tg_newtable=
4038+
transition_capture->tcs_update_tuplestore;
4039+
}
39974040
}
39984041

39994042
/*
@@ -5241,7 +5284,10 @@ AfterTriggerSaveEvent(EState *estate, ResultRelInfo *relinfo,
52415284
Tuplestorestate*new_tuplestore;
52425285

52435286
Assert(newtup!=NULL);
5244-
new_tuplestore=transition_capture->tcs_new_tuplestore;
5287+
if (event==TRIGGER_EVENT_INSERT)
5288+
new_tuplestore=transition_capture->tcs_insert_tuplestore;
5289+
else
5290+
new_tuplestore=transition_capture->tcs_update_tuplestore;
52455291

52465292
if (original_insert_tuple!=NULL)
52475293
tuplestore_puttuple(new_tuplestore,original_insert_tuple);

‎src/include/commands/trigger.h

Lines changed: 11 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -73,9 +73,17 @@ typedef struct TransitionCaptureState
7373
*/
7474
HeapTupletcs_original_insert_tuple;
7575

76-
/* The tuplestores backing the transition tables. */
77-
Tuplestorestate*tcs_old_tuplestore;
78-
Tuplestorestate*tcs_new_tuplestore;
76+
/*
77+
* The tuplestores backing the transition tables. We use separate
78+
* tuplestores for INSERT and UPDATE, because INSERT ... ON CONFLICT
79+
* ... DO UPDATE causes INSERT and UPDATE triggers to fire and needs a way
80+
* to keep track of the new tuple images resulting from the two cases
81+
* separately. We only need a single old image tuplestore, because there
82+
* is no statement that can both update and delete at the same time.
83+
*/
84+
Tuplestorestate*tcs_old_tuplestore;/* for DELETE and UPDATE old images */
85+
Tuplestorestate*tcs_insert_tuplestore;/* for INSERT new images */
86+
Tuplestorestate*tcs_update_tuplestore;/* for UPDATE new images */
7987
}TransitionCaptureState;
8088

8189
/*

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

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5847,8 +5847,13 @@ AS $$
58475847
RETURN NULL;
58485848
END;
58495849
$$;
5850-
CREATE TRIGGER transition_table_level2_ri_child_insupd_trigger
5851-
AFTER INSERT OR UPDATE ON transition_table_level2
5850+
CREATE TRIGGER transition_table_level2_ri_child_ins_trigger
5851+
AFTER INSERT ON transition_table_level2
5852+
REFERENCING NEW TABLE AS i
5853+
FOR EACH STATEMENT EXECUTE PROCEDURE
5854+
transition_table_level2_ri_child_insupd_func();
5855+
CREATE TRIGGER transition_table_level2_ri_child_upd_trigger
5856+
AFTER UPDATE ON transition_table_level2
58525857
REFERENCING NEW TABLE AS i
58535858
FOR EACH STATEMENT EXECUTE PROCEDURE
58545859
transition_table_level2_ri_child_insupd_func();

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

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2214,6 +2214,44 @@ NOTICE: trigger = table2_trig, new table = ("hello world")
22142214
NOTICE: trigger = table1_trig, new table = (42)
22152215
drop table table1;
22162216
drop table table2;
2217+
--
2218+
-- Verify behavior of INSERT ... ON CONFLICT DO UPDATE ... with
2219+
-- transition tables.
2220+
--
2221+
create table my_table (a int primary key, b text);
2222+
create trigger my_table_insert_trig
2223+
after insert on my_table referencing new table as new_table
2224+
for each statement execute procedure dump_insert();
2225+
create trigger my_table_update_trig
2226+
after update on my_table referencing old table as old_table new table as new_table
2227+
for each statement execute procedure dump_update();
2228+
-- inserts only
2229+
insert into my_table values (1, 'AAA'), (2, 'BBB')
2230+
on conflict (a) do
2231+
update set b = my_table.b || ':' || excluded.b;
2232+
NOTICE: trigger = my_table_update_trig, old table = <NULL>, new table = <NULL>
2233+
NOTICE: trigger = my_table_insert_trig, new table = (1,AAA), (2,BBB)
2234+
-- mixture of inserts and updates
2235+
insert into my_table values (1, 'AAA'), (2, 'BBB'), (3, 'CCC'), (4, 'DDD')
2236+
on conflict (a) do
2237+
update set b = my_table.b || ':' || excluded.b;
2238+
NOTICE: trigger = my_table_update_trig, old table = (1,AAA), (2,BBB), new table = (1,AAA:AAA), (2,BBB:BBB)
2239+
NOTICE: trigger = my_table_insert_trig, new table = (3,CCC), (4,DDD)
2240+
-- updates only
2241+
insert into my_table values (3, 'CCC'), (4, 'DDD')
2242+
on conflict (a) do
2243+
update set b = my_table.b || ':' || excluded.b;
2244+
NOTICE: trigger = my_table_update_trig, old table = (3,CCC), (4,DDD), new table = (3,CCC:CCC), (4,DDD:DDD)
2245+
NOTICE: trigger = my_table_insert_trig, new table = <NULL>
2246+
--
2247+
-- Verify that you can't create a trigger with transition tables for
2248+
-- more than one event.
2249+
--
2250+
create trigger my_table_multievent_trig
2251+
after insert or update on my_table referencing new table as new_table
2252+
for each statement execute procedure dump_insert();
2253+
ERROR: Transition tables cannot be specified for triggers with more than one event
2254+
drop table my_table;
22172255
-- cleanup
22182256
drop function dump_insert();
22192257
drop function dump_update();

‎src/test/regress/sql/plpgsql.sql

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4639,8 +4639,14 @@ AS $$
46394639
END;
46404640
$$;
46414641

4642-
CREATETRIGGERtransition_table_level2_ri_child_insupd_trigger
4643-
AFTER INSERTORUPDATEON transition_table_level2
4642+
CREATETRIGGERtransition_table_level2_ri_child_ins_trigger
4643+
AFTER INSERTON transition_table_level2
4644+
REFERENCING NEW TABLEAS i
4645+
FOR EACH STATEMENT EXECUTE PROCEDURE
4646+
transition_table_level2_ri_child_insupd_func();
4647+
4648+
CREATETRIGGERtransition_table_level2_ri_child_upd_trigger
4649+
AFTERUPDATEON transition_table_level2
46444650
REFERENCING NEW TABLEAS i
46454651
FOR EACH STATEMENT EXECUTE PROCEDURE
46464652
transition_table_level2_ri_child_insupd_func();

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

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1725,6 +1725,45 @@ with wcte as (insert into table1 values (42))
17251725
droptable table1;
17261726
droptable table2;
17271727

1728+
--
1729+
-- Verify behavior of INSERT ... ON CONFLICT DO UPDATE ... with
1730+
-- transition tables.
1731+
--
1732+
1733+
createtablemy_table (aintprimary key, btext);
1734+
createtriggermy_table_insert_trig
1735+
after inserton my_table referencing new tableas new_table
1736+
for each statement execute procedure dump_insert();
1737+
createtriggermy_table_update_trig
1738+
afterupdateon my_table referencing old tableas old_table new tableas new_table
1739+
for each statement execute procedure dump_update();
1740+
1741+
-- inserts only
1742+
insert into my_tablevalues (1,'AAA'), (2,'BBB')
1743+
on conflict (a) do
1744+
updateset b=my_table.b||':'||excluded.b;
1745+
1746+
-- mixture of inserts and updates
1747+
insert into my_tablevalues (1,'AAA'), (2,'BBB'), (3,'CCC'), (4,'DDD')
1748+
on conflict (a) do
1749+
updateset b=my_table.b||':'||excluded.b;
1750+
1751+
-- updates only
1752+
insert into my_tablevalues (3,'CCC'), (4,'DDD')
1753+
on conflict (a) do
1754+
updateset b=my_table.b||':'||excluded.b;
1755+
1756+
--
1757+
-- Verify that you can't create a trigger with transition tables for
1758+
-- more than one event.
1759+
--
1760+
1761+
createtriggermy_table_multievent_trig
1762+
after insertorupdateon my_table referencing new tableas new_table
1763+
for each statement execute procedure dump_insert();
1764+
1765+
droptable my_table;
1766+
17281767
-- cleanup
17291768
dropfunction dump_insert();
17301769
dropfunction dump_update();

0 commit comments

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