From 54d4d0ff6cd40638d026c01e46deb102e7951ba6 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 16 Sep 2017 13:20:32 -0400 Subject: Fix SQL-spec incompatibilities in new transition table feature. The standard says that all changes of the same kind (insert, update, or delete) caused in one table by a single SQL statement should be reported in a single transition table; and by that, they mean to include foreign key enforcement actions cascading from the statement's direct effects. It's also reasonable to conclude that if the standard had wCTEs, they would say that effects of wCTEs applying to the same table as each other or the outer statement should be merged into one transition table. We weren't doing it like that. Hence, arrange to merge tuples from multiple update actions into a single transition table as much as we can. There is a problem, which is that if the firing of FK enforcement triggers and after-row triggers with transition tables is interspersed, we might need to report more tuples after some triggers have already seen the transition table. It seems like a 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 this happens. 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 query to cause an additional firing of the AFTER STATEMENT triggers for the referencing table, but that's certainly not per spec. (We're still doing multiple firings of BEFORE STATEMENT triggers, though; is that something worth changing?) Also, forbid using transition tables with column-specific UPDATE triggers. The spec requires such transition tables to show only the tuples for which the UPDATE trigger would have fired, which means maintaining multiple transition tables or else somehow filtering the contents at readout. Maybe someday we'll bother to support that option, but it looks like a lot 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. This removes a subtransaction-lifespan memory leak introduced by my previous band-aid patch 3c4359521. In passing, refactor the AfterTriggers data structures to reduce the management overhead for them, by using arrays of structs rather than several parallel arrays for per-query-level and per-subtransaction state. I failed to resist the temptation to do some copy-editing on the SGML docs about triggers, above and beyond merely documenting the effects of this patch. Back-patch to v10, because we don't want the semantics of transition tables 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 --- doc/src/sgml/ref/create_trigger.sgml | 112 +++++++++++++++++++++++++---------- doc/src/sgml/trigger.sgml | 54 +++++++++-------- 2 files changed, 109 insertions(+), 57 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 18efe6a9ed7..065c8272710 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -52,7 +52,7 @@ CREATE [ CONSTRAINT ] TRIGGER name trigger will be associated with the specified table, view, or foreign table and will execute the specified function function_name when - certain events occur. + certain operations are performed on that table. @@ -82,10 +82,7 @@ CREATE [ CONSTRAINT ] TRIGGER name executes once for any given operation, regardless of how many rows it modifies (in particular, an operation that modifies zero rows will still result in the execution of any applicable FOR - EACH STATEMENT triggers). Note that with an - INSERT with an ON CONFLICT DO UPDATE - clause, both INSERT and - UPDATE statement level trigger will be fired. + EACH STATEMENT triggers). @@ -174,7 +171,8 @@ CREATE [ CONSTRAINT ] TRIGGER name constraint trigger. This is the same as a regular trigger except that the timing of the trigger firing can be adjusted using . - Constraint triggers must be AFTER ROW triggers on tables. They + Constraint triggers must be AFTER ROW triggers on plain + tables (not foreign tables). They can be fired either at the end of the statement causing the triggering event, or at the end of the containing transaction; in the latter case they are said to be deferred. A pending deferred-trigger firing @@ -184,18 +182,29 @@ CREATE [ CONSTRAINT ] TRIGGER name - The REFERENCING option is only allowed for an AFTER - trigger which is not a constraint trigger. OLD TABLE may only - be specified once, and only on a trigger which can fire on - UPDATE or DELETE. NEW TABLE may only - be specified once, and only on a trigger which can fire on - UPDATE or INSERT. + The REFERENCING option enables collection + of transition relations, which are row sets that include all + of the rows inserted, deleted, or modified by the current SQL statement. + This feature lets the trigger see a global view of what the statement did, + not just one row at a time. This option is only allowed for + an AFTER trigger that is not a constraint trigger; also, if + the trigger is an UPDATE trigger, it must not specify + a column_name list. + OLD TABLE may only be specified once, and only for a trigger + that can fire on UPDATE or DELETE; it creates a + transition relation containing the before-images of all rows + updated or deleted by the statement. + Similarly, NEW TABLE may only be specified once, and only for + a trigger that can fire on UPDATE or INSERT; + it creates a transition relation containing the after-images + of all rows updated or inserted by the statement. SELECT does not modify any rows so you cannot - create SELECT triggers. Rules and views are more - appropriate in such cases. + create SELECT triggers. Rules and views may provide + workable solutions to problems that seem to need SELECT + triggers. @@ -300,12 +309,9 @@ UPDATE OF column_name1 [, column_name2REFERENCING - This immediately precedes the declaration of one or two relations which - can be used to read the before and/or after images of all rows directly - affected by the triggering statement. An AFTER EACH ROW - trigger is allowed to use both these transition relation names and the - row names (OLD and NEW) which reference each - individual row for which the trigger fires. + This keyword immediately precedes the declaration of one or two + relation names that provide access to the transition relations of the + triggering statement. @@ -315,8 +321,9 @@ UPDATE OF column_name1 [, column_name2NEW TABLE - This specifies whether the named relation contains the before or after - images for rows affected by the statement which fired the trigger. + This clause indicates whether the following relation name is for the + before-image transition relation or the after-image transition + relation. @@ -325,7 +332,8 @@ UPDATE OF column_name1 [, column_name2transition_relation_name - The (unqualified) name to be used within the trigger for this relation. + The (unqualified) name to be used within the trigger for this + transition relation. @@ -458,6 +466,35 @@ UPDATE OF column_name1 [, column_name2 + + In some cases it is possible for a single SQL command to fire more than + one kind of trigger. For instance an INSERT with + an ON CONFLICT DO UPDATE clause may cause both insert and + update operations, so it will fire both kinds of triggers as needed. + The transition relations supplied to triggers are + specific to their event type; thus an INSERT trigger + will see only the inserted rows, while an UPDATE + trigger will see only the updated rows. + + + + Row updates or deletions caused by foreign-key enforcement actions, such + as ON UPDATE CASCADE or ON DELETE SET NULL, are + treated as part of the SQL command that caused them (note that such + actions are never deferred). Relevant triggers on the affected table will + be fired, so that this provides another way in which a SQL command might + fire triggers not directly matching its type. In simple cases, triggers + that request transition relations will see all changes caused in their + table by a single original SQL command as a single transition relation. + However, there are cases in which the presence of an AFTER ROW + trigger that requests transition relations will cause the foreign-key + enforcement actions triggered by a single SQL command to be split into + multiple steps, each with its own transition relation(s). In such cases, + any AFTER STATEMENT triggers that are present will be fired + once per creation of a transition relation, ensuring that the triggers see + each affected row once and only once. + + Modifying a partitioned table or a table with inheritance children fires statement-level triggers directly attached to that table, but not @@ -589,19 +626,30 @@ CREATE TRIGGER paired_items_update - While transition tables for AFTER triggers are specified - using the REFERENCING clause in the standard way, the row - variables used in FOR EACH ROW triggers may not be - specified in REFERENCING clause. They are available in a - manner which is dependent on the language in which the trigger function - is written. Some languages effectively behave as though there is a - REFERENCING clause containing OLD ROW AS OLD NEW - ROW AS NEW. + While transition table names for AFTER triggers are + specified using the REFERENCING clause in the standard way, + the row variables used in FOR EACH ROW triggers may not be + specified in a REFERENCING clause. They are available in a + manner that is dependent on the language in which the trigger function + is written, but is fixed for any one language. Some languages + effectively behave as though there is a REFERENCING clause + containing OLD ROW AS OLD NEW ROW AS NEW. - PostgreSQL only allows the execution + + The standard allows transition tables to be used with + column-specific UPDATE triggers, but then the set of rows + that should be visible in the transition tables depends on the + trigger's column list. This is not currently implemented by + PostgreSQL. + + + + + + PostgreSQL only allows the execution of a user-defined function for the triggered action. The standard allows the execution of a number of other SQL commands, such as CREATE TABLE, as the triggered action. This diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 950245d19a2..a16256056f0 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -41,17 +41,13 @@ On tables and foreign tables, triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, - or once per SQL statement. If an - INSERT contains an ON CONFLICT DO UPDATE - clause, it is possible that the effects of a BEFORE insert trigger and - a BEFORE update trigger can both be applied together, if a reference to - an EXCLUDED column appears. UPDATE - triggers can moreover be set to fire only if certain columns are - mentioned in the SET clause of the - UPDATE statement. Triggers can also fire for - TRUNCATE statements. If a trigger event occurs, + or once per SQL statement. + UPDATE triggers can moreover be set to fire only if + certain columns are mentioned in the SET clause of + the UPDATE statement. Triggers can also fire + for TRUNCATE statements. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the - event. Foreign tables do not support the TRUNCATE statement at all. + event. @@ -97,10 +93,7 @@ two types of triggers are sometimes called row-level triggers and statement-level triggers, respectively. Triggers on TRUNCATE may only be - defined at statement level. On views, triggers that fire before or - after may only be defined at statement level, while triggers that fire - instead of an INSERT, UPDATE, - or DELETE may only be defined at row level. + defined at statement level, not per-row. @@ -117,9 +110,9 @@ operated on, while row-level AFTER triggers fire at the end of the statement (but before any statement-level AFTER triggers). These types of triggers may only be defined on non-partitioned tables and - foreign tables. Row-level INSTEAD OF triggers may only be - defined on views, and fire immediately as each row in the view is - identified as needing to be operated on. + foreign tables, not views. INSTEAD OF triggers may only be + defined on views, and only at row level; they fire immediately as each + row in the view is identified as needing to be operated on. @@ -132,18 +125,19 @@ If an INSERT contains an ON CONFLICT - DO UPDATE clause, it is possible that the effects of all - row-level BEFORE INSERT triggers - and all row-level BEFORE UPDATE triggers can + DO UPDATE clause, it is possible that the effects of + row-level BEFORE INSERT triggers and + row-level BEFORE UPDATE triggers can both be applied in a way that is apparent from the final state of the updated row, if an EXCLUDED column is referenced. There need not be an EXCLUDED column reference for - both sets of row-level BEFORE triggers to execute, though. The + both sets of row-level BEFORE triggers to execute, + though. The possibility of surprising outcomes should be considered when there are both BEFORE INSERT and BEFORE UPDATE row-level triggers - that both affect a row being inserted/updated (this can still be - problematic if the modifications are more or less equivalent if + that change a row being inserted/updated (this can be + problematic even if the modifications are more or less equivalent, if they're not also idempotent). Note that statement-level UPDATE triggers are executed when ON CONFLICT DO UPDATE is specified, regardless of whether or not @@ -314,8 +308,18 @@ NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and DELETE triggers. - Statement-level triggers do not currently have any way to examine the - individual row(s) modified by the statement. + + + + By default, statement-level triggers do not have any way to examine the + individual row(s) modified by the statement. But an AFTER + STATEMENT trigger can request that transition tables + be created to make the sets of affected rows available to the trigger. + AFTER ROW triggers can also request transition tables, so + that they can see the total changes in the table as well as the change in + the individual row they are currently being fired for. The syntax for + examining the transition tables again depends on the programming language + that is being used. -- cgit v1.2.3