On Wed, Oct 9, 2019 at 3:59 PM Guy Burgess <guy@xxxxxxxxxxxxx> 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.
