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

Commit0f79440

Browse files
committed
Fix SQL-spec incompatibilities in new transition table feature.
The standard says that all changes of the same kind (insert, update, ordelete) caused in one table by a single SQL statement should be reportedin a single transition table; and by that, they mean to include foreign keyenforcement actions cascading from the statement's direct effects. It'salso reasonable to conclude that if the standard had wCTEs, they would saythat effects of wCTEs applying to the same table as each other or the outerstatement should be merged into one transition table. We weren't doing itlike that.Hence, arrange to merge tuples from multiple update actions into a singletransition table as much as we can. There is a problem, which is that ifthe firing of FK enforcement triggers and after-row triggers withtransition tables is interspersed, we might need to report more tuplesafter some triggers have already seen the transition table. It seems likea bad idea for the transition table to be mutable between trigger calls.There's no good way around this without a major redesign of the FK logic,so for now, resolve it by opening a new transition table each time thishappens.Also, ensure that AFTER STATEMENT triggers fire just once per statement,or once per transition table when we're forced to make more than one.Previous versions of Postgres have allowed each FK enforcement queryto cause an additional firing of the AFTER STATEMENT triggers for thereferencing table, but that's certainly not per spec. (We're stilldoing multiple firings of BEFORE STATEMENT triggers, though; is thatsomething worth changing?)Also, forbid using transition tables with column-specific UPDATE triggers.The spec requires such transition tables to show only the tuples for whichthe UPDATE trigger would have fired, which means maintaining multipletransition tables or else somehow filtering the contents at readout.Maybe someday we'll bother to support that option, but it looks like alot of trouble for a marginal feature.The transition tables are now managed by the AfterTriggers data structures,rather than being directly the responsibility of ModifyTable nodes. Thisremoves a subtransaction-lifespan memory leak introduced by my previousband-aid patch3c43595.In passing, refactor the AfterTriggers data structures to reduce themanagement overhead for them, by using arrays of structs rather thanseveral parallel arrays for per-query-level and per-subtransaction state.I failed to resist the temptation to do some copy-editing on the SGMLdocs about triggers, above and beyond merely documenting the effectsof this patch.Back-patch to v10, because we don't want the semantics of transitiontables to change post-release.Patch by me, with help and review from Thomas Munro.Discussion:https://postgr.es/m/20170909064853.25630.12825@wrigleys.postgresql.org
1 parent04b64b8 commit0f79440

File tree

11 files changed

+803
-390
lines changed

11 files changed

+803
-390
lines changed

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

Lines changed: 80 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -52,7 +52,7 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable>
5252
trigger will be associated with the specified table, view, or foreign table
5353
and will execute the specified
5454
function <replaceable class="parameter">function_name</replaceable> when
55-
certainevents occur.
55+
certainoperations are performed on that table.
5656
</para>
5757

5858
<para>
@@ -82,10 +82,7 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable>
8282
executes once for any given operation, regardless of how many rows
8383
it modifies (in particular, an operation that modifies zero rows
8484
will still result in the execution of any applicable <literal>FOR
85-
EACH STATEMENT</literal> triggers). Note that with an
86-
<command>INSERT</command> with an <literal>ON CONFLICT DO UPDATE</>
87-
clause, both <command>INSERT</command> and
88-
<command>UPDATE</command> statement level trigger will be fired.
85+
EACH STATEMENT</literal> triggers).
8986
</para>
9087

9188
<para>
@@ -174,7 +171,8 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable>
174171
<firstterm>constraint trigger</>. This is the same as a regular trigger
175172
except that the timing of the trigger firing can be adjusted using
176173
<xref linkend="SQL-SET-CONSTRAINTS">.
177-
Constraint triggers must be <literal>AFTER ROW</> triggers on tables. They
174+
Constraint triggers must be <literal>AFTER ROW</> triggers on plain
175+
tables (not foreign tables). They
178176
can be fired either at the end of the statement causing the triggering
179177
event, or at the end of the containing transaction; in the latter case they
180178
are said to be <firstterm>deferred</>. A pending deferred-trigger firing
@@ -184,18 +182,29 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable>
184182
</para>
185183

186184
<para>
187-
The <literal>REFERENCING</> option is only allowed for an <literal>AFTER</>
188-
trigger which is not a constraint trigger. <literal>OLD TABLE</> may only
189-
be specified once, and only on a trigger which can fire on
190-
<literal>UPDATE</> or <literal>DELETE</>. <literal>NEW TABLE</> may only
191-
be specified once, and only on a trigger which can fire on
192-
<literal>UPDATE</> or <literal>INSERT</>.
185+
The <literal>REFERENCING</> option enables collection
186+
of <firstterm>transition relations</>, which are row sets that include all
187+
of the rows inserted, deleted, or modified by the current SQL statement.
188+
This feature lets the trigger see a global view of what the statement did,
189+
not just one row at a time. This option is only allowed for
190+
an <literal>AFTER</> trigger that is not a constraint trigger; also, if
191+
the trigger is an <literal>UPDATE</> trigger, it must not specify
192+
a <replaceable class="parameter">column_name</replaceable> list.
193+
<literal>OLD TABLE</> may only be specified once, and only for a trigger
194+
that can fire on <literal>UPDATE</> or <literal>DELETE</>; it creates a
195+
transition relation containing the <firstterm>before-images</> of all rows
196+
updated or deleted by the statement.
197+
Similarly, <literal>NEW TABLE</> may only be specified once, and only for
198+
a trigger that can fire on <literal>UPDATE</> or <literal>INSERT</>;
199+
it creates a transition relation containing the <firstterm>after-images</>
200+
of all rows updated or inserted by the statement.
193201
</para>
194202

195203
<para>
196204
<command>SELECT</command> does not modify any rows so you cannot
197-
create <command>SELECT</command> triggers. Rules and views are more
198-
appropriate in such cases.
205+
create <command>SELECT</command> triggers. Rules and views may provide
206+
workable solutions to problems that seem to need <command>SELECT</command>
207+
triggers.
199208
</para>
200209

201210
<para>
@@ -300,12 +309,9 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
300309
<term><literal>REFERENCING</literal></term>
301310
<listitem>
302311
<para>
303-
This immediately precedes the declaration of one or two relations which
304-
can be used to read the before and/or after images of all rows directly
305-
affected by the triggering statement. An <literal>AFTER EACH ROW</>
306-
trigger is allowed to use both these transition relation names and the
307-
row names (<literal>OLD</> and <literal>NEW</>) which reference each
308-
individual row for which the trigger fires.
312+
This keyword immediately precedes the declaration of one or two
313+
relation names that provide access to the transition relations of the
314+
triggering statement.
309315
</para>
310316
</listitem>
311317
</varlistentry>
@@ -315,8 +321,9 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
315321
<term><literal>NEW TABLE</literal></term>
316322
<listitem>
317323
<para>
318-
This specifies whether the named relation contains the before or after
319-
images for rows affected by the statement which fired the trigger.
324+
This clause indicates whether the following relation name is for the
325+
before-image transition relation or the after-image transition
326+
relation.
320327
</para>
321328
</listitem>
322329
</varlistentry>
@@ -325,7 +332,8 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
325332
<term><replaceable class="PARAMETER">transition_relation_name</replaceable></term>
326333
<listitem>
327334
<para>
328-
The (unqualified) name to be used within the trigger for this relation.
335+
The (unqualified) name to be used within the trigger for this
336+
transition relation.
329337
</para>
330338
</listitem>
331339
</varlistentry>
@@ -458,6 +466,35 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</
458466
rows.
459467
</para>
460468

469+
<para>
470+
In some cases it is possible for a single SQL command to fire more than
471+
one kind of trigger. For instance an <command>INSERT</command> with
472+
an <literal>ON CONFLICT DO UPDATE</> clause may cause both insert and
473+
update operations, so it will fire both kinds of triggers as needed.
474+
The transition relations supplied to triggers are
475+
specific to their event type; thus an <command>INSERT</command> trigger
476+
will see only the inserted rows, while an <command>UPDATE</command>
477+
trigger will see only the updated rows.
478+
</para>
479+
480+
<para>
481+
Row updates or deletions caused by foreign-key enforcement actions, such
482+
as <literal>ON UPDATE CASCADE</> or <literal>ON DELETE SET NULL</>, are
483+
treated as part of the SQL command that caused them (note that such
484+
actions are never deferred). Relevant triggers on the affected table will
485+
be fired, so that this provides another way in which a SQL command might
486+
fire triggers not directly matching its type. In simple cases, triggers
487+
that request transition relations will see all changes caused in their
488+
table by a single original SQL command as a single transition relation.
489+
However, there are cases in which the presence of an <literal>AFTER ROW</>
490+
trigger that requests transition relations will cause the foreign-key
491+
enforcement actions triggered by a single SQL command to be split into
492+
multiple steps, each with its own transition relation(s). In such cases,
493+
any <literal>AFTER STATEMENT</> triggers that are present will be fired
494+
once per creation of a transition relation, ensuring that the triggers see
495+
each affected row once and only once.
496+
</para>
497+
461498
<para>
462499
Modifying a partitioned table or a table with inheritance children fires
463500
statement-level triggers directly attached to that table, but not
@@ -589,19 +626,30 @@ CREATE TRIGGER paired_items_update
589626
<itemizedlist>
590627
<listitem>
591628
<para>
592-
While transitiontablesfor <literal>AFTER</> triggers are specified
593-
using the <literal>REFERENCING</> clause in the standard way, the row
594-
variables used in <literal>FOR EACH ROW</> triggers may not be
595-
specified in <literal>REFERENCING</> clause. They are available in a
596-
mannerwhich is dependent on the language in which the trigger function
597-
is written. Some languages effectively behave as though there is a
598-
<literal>REFERENCING</> clause containing <literal>OLD ROW AS OLD NEW
599-
ROW AS NEW</>.
629+
While transitiontable namesfor <literal>AFTER</> triggers are
630+
specifiedusing the <literal>REFERENCING</> clause in the standard way,
631+
the rowvariables used in <literal>FOR EACH ROW</> triggers may not be
632+
specified ina<literal>REFERENCING</> clause. They are available in a
633+
mannerthat is dependent on the language in which the trigger function
634+
is written, but is fixed for any one language. Some languages
635+
effectively behave as though there is a<literal>REFERENCING</> clause
636+
containing <literal>OLD ROW AS OLD NEWROW AS NEW</>.
600637
</para>
601638
</listitem>
602639

603640
<listitem>
604-
<para><productname>PostgreSQL</productname> only allows the execution
641+
<para>
642+
The standard allows transition tables to be used with
643+
column-specific <literal>UPDATE</> triggers, but then the set of rows
644+
that should be visible in the transition tables depends on the
645+
trigger's column list. This is not currently implemented by
646+
<productname>PostgreSQL</productname>.
647+
</para>
648+
</listitem>
649+
650+
<listitem>
651+
<para>
652+
<productname>PostgreSQL</productname> only allows the execution
605653
of a user-defined function for the triggered action. The standard
606654
allows the execution of a number of other SQL commands, such as
607655
<command>CREATE TABLE</command>, as the triggered action. This

‎doc/src/sgml/trigger.sgml

Lines changed: 29 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -41,17 +41,13 @@
4141
On tables and foreign tables, triggers can be defined to execute either
4242
before or after any <command>INSERT</command>, <command>UPDATE</command>,
4343
or <command>DELETE</command> operation, either once per modified row,
44-
or once per <acronym>SQL</acronym> statement. If an
45-
<command>INSERT</command> contains an <literal>ON CONFLICT DO UPDATE</>
46-
clause, it is possible that the effects of a BEFORE insert trigger and
47-
a BEFORE update trigger can both be applied together, if a reference to
48-
an <varname>EXCLUDED</> column appears. <command>UPDATE</command>
49-
triggers can moreover be set to fire only if certain columns are
50-
mentioned in the <literal>SET</literal> clause of the
51-
<command>UPDATE</command> statement. Triggers can also fire for
52-
<command>TRUNCATE</command> statements. If a trigger event occurs,
44+
or once per <acronym>SQL</acronym> statement.
45+
<command>UPDATE</command> triggers can moreover be set to fire only if
46+
certain columns are mentioned in the <literal>SET</literal> clause of
47+
the <command>UPDATE</command> statement. Triggers can also fire
48+
for <command>TRUNCATE</command> statements. If a trigger event occurs,
5349
the trigger's function is called at the appropriate time to handle the
54-
event. Foreign tables do not support the TRUNCATE statement at all.
50+
event.
5551
</para>
5652

5753
<para>
@@ -97,10 +93,7 @@
9793
two types of triggers are sometimes called <firstterm>row-level</>
9894
triggers and <firstterm>statement-level</> triggers,
9995
respectively. Triggers on <command>TRUNCATE</command> may only be
100-
defined at statement level. On views, triggers that fire before or
101-
after may only be defined at statement level, while triggers that fire
102-
instead of an <command>INSERT</command>, <command>UPDATE</command>,
103-
or <command>DELETE</command> may only be defined at row level.
96+
defined at statement level, not per-row.
10497
</para>
10598

10699
<para>
@@ -117,9 +110,9 @@
117110
operated on, while row-level <literal>AFTER</> triggers fire at the end of
118111
the statement (but before any statement-level <literal>AFTER</> triggers).
119112
These types of triggers may only be defined on non-partitioned tables and
120-
foreign tables. Row-level <literal>INSTEAD OF</> triggers may only be
121-
defined on views, andfire immediately as each row in the view is
122-
identified as needing to be operated on.
113+
foreign tables, not views. <literal>INSTEAD OF</> triggers may only be
114+
defined on views, andonly at row level; they fire immediately as each
115+
row in the view isidentified as needing to be operated on.
123116
</para>
124117

125118
<para>
@@ -132,18 +125,19 @@
132125

133126
<para>
134127
If an <command>INSERT</command> contains an <literal>ON CONFLICT
135-
DO UPDATE</> clause, it is possible that the effects of all
136-
row-level <literal>BEFORE</> <command>INSERT</command> triggers
137-
and allrow-level <literal>BEFORE</literal> <command>UPDATE</command> triggers can
128+
DO UPDATE</> clause, it is possible that the effects of
129+
row-level <literal>BEFORE</> <command>INSERT</command> triggers and
130+
row-level <literal>BEFORE</literal> <command>UPDATE</command> triggers can
138131
both be applied in a way that is apparent from the final state of
139132
the updated row, if an <varname>EXCLUDED</> column is referenced.
140133
There need not be an <varname>EXCLUDED</> column reference for
141-
both sets of row-level <literal>BEFORE</literal> triggers to execute, though. The
134+
both sets of row-level <literal>BEFORE</literal> triggers to execute,
135+
though. The
142136
possibility of surprising outcomes should be considered when there
143137
are both <literal>BEFORE</> <command>INSERT</command> and
144138
<literal>BEFORE</> <command>UPDATE</command> row-level triggers
145-
thatboth affecta row being inserted/updated (this can still be
146-
problematic if the modifications are more or less equivalent if
139+
thatchangea row being inserted/updated (this can be
140+
problematicevenif the modifications are more or less equivalent, if
147141
they're not also idempotent). Note that statement-level
148142
<command>UPDATE</command> triggers are executed when <literal>ON
149143
CONFLICT DO UPDATE</> is specified, regardless of whether or not
@@ -314,8 +308,18 @@
314308
<varname>NEW</varname> row for <command>INSERT</command> and
315309
<command>UPDATE</command> triggers, and/or the <varname>OLD</varname> row
316310
for <command>UPDATE</command> and <command>DELETE</command> triggers.
317-
Statement-level triggers do not currently have any way to examine the
318-
individual row(s) modified by the statement.
311+
</para>
312+
313+
<para>
314+
By default, statement-level triggers do not have any way to examine the
315+
individual row(s) modified by the statement. But an <literal>AFTER
316+
STATEMENT</> trigger can request that <firstterm>transition tables</>
317+
be created to make the sets of affected rows available to the trigger.
318+
<literal>AFTER ROW</> triggers can also request transition tables, so
319+
that they can see the total changes in the table as well as the change in
320+
the individual row they are currently being fired for. The syntax for
321+
examining the transition tables again depends on the programming language
322+
that is being used.
319323
</para>
320324

321325
</sect1>

‎src/backend/commands/copy.c

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2432,12 +2432,17 @@ CopyFrom(CopyState cstate)
24322432
/* Triggers might need a slot as well */
24332433
estate->es_trig_tuple_slot=ExecInitExtraTupleSlot(estate);
24342434

2435+
/* Prepare to catch AFTER triggers. */
2436+
AfterTriggerBeginQuery();
2437+
24352438
/*
24362439
* If there are any triggers with transition tables on the named relation,
24372440
* we need to be prepared to capture transition tuples.
24382441
*/
24392442
cstate->transition_capture=
2440-
MakeTransitionCaptureState(cstate->rel->trigdesc);
2443+
MakeTransitionCaptureState(cstate->rel->trigdesc,
2444+
RelationGetRelid(cstate->rel),
2445+
CMD_INSERT);
24412446

24422447
/*
24432448
* If the named relation is a partitioned table, initialize state for
@@ -2513,9 +2518,6 @@ CopyFrom(CopyState cstate)
25132518
bufferedTuples=palloc(MAX_BUFFERED_TUPLES*sizeof(HeapTuple));
25142519
}
25152520

2516-
/* Prepare to catch AFTER triggers. */
2517-
AfterTriggerBeginQuery();
2518-
25192521
/*
25202522
* Check BEFORE STATEMENT insertion triggers. It's debatable whether we
25212523
* should do this for COPY, since it's not really an "INSERT" statement as

0 commit comments

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