Re: Transition tables for column-specific UPDATE triggers

On Wed, Oct 9, 2019 at 3:59 PM Guy Burgess <guy(at)burgess(dot)co(dot)nz> wrote:
> The manual says: https://www.postgresql.org/docs/current/sql-createtrigger.html
>
> A column list cannot be specified when requesting transition relations.
>
> And (I think the same point):
>
> 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.
>
> Are there any plans to allow transition tables to be used with column-specific UPDATE triggers? Or, is there another way for a trigger function to see the rows changed by a column-specific UPDATE trigger?

Hi Guy,

Answering an ancient message that went unanswered... I'm not aware of
anyone working on that, and there isn't another way: the transition
tables feature simply won't let you create such a trigger. The last
I've seen anyone say about that was in the following commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0f79440fb0b4c5a9baa9a95570c01828a9093802

Excerpt:

"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 code preventing it is here:

/*
* We currently don't allow column-specific triggers with
* transition tables. Per spec, that seems to require
* accumulating separate transition tables for each combination of
* columns, which is a lot of work for a rather marginal feature.
*/
if (stmt->columns != NIL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("transition tables cannot be specified
for triggers with column lists")));

In theory you could do some filtering in your trigger procedure, by
comparing rows in OLD TABLE and NEW TABLE to see which row-pairs
represent changes to the columns you care about, but that's slightly
complicated: you can join OLD and NEW using whatever keys you have
defined, but that only works if there's no danger of the keys
themselves changing. I wondered about inventing something like WITH
ORDINALITY so that you get unique ordered numbers in an extra column
that can be used to join the two transition tables without knowing
anything about the user defined keys, but among other problems I
couldn't figure out how to fit it in syntax-wise.

I suppose PostgreSQL could do this internally to make the feature you
want work: whenever you scan either table, in an UPDATE OF <columns>
trigger, it could scan both transition tables in sync and filter out
rows that didn't change your columns of interest. Or it could do that
filtering up front, before your trigger fires, to create two brand new
tuplestores just for your trigger invocation. Or there could be a
third spillable data structure, that records which triggers should be
able to see each old/new-pair, or which columns changed, and is
scanned in sync with the others. Just some first thoughts; I am not
planning to work on this any time soon.