[COMMITTERS] pgsql: Fix SQL-spec incompatibilities in new transition table feature. - Mailing list pgsql-committers

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

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/0f79440fb0b4c5a9baa9a95570c01828a9093802

Modified Files
--------------
doc/src/sgml/ref/create_trigger.sgml   | 112 +++--
doc/src/sgml/trigger.sgml              |  54 ++-
src/backend/commands/copy.c            |  10 +-
src/backend/commands/trigger.c         | 820 +++++++++++++++++++++------------
src/backend/executor/README            |   2 +-
src/backend/executor/execMain.c        |  11 +-
src/backend/executor/nodeModifyTable.c |  57 ++-
src/include/commands/trigger.h         |  29 +-
src/include/nodes/execnodes.h          |   4 +-
src/test/regress/expected/triggers.out |  52 ++-
src/test/regress/sql/triggers.sql      |  42 ++
11 files changed, 803 insertions(+), 390 deletions(-)


--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers