Re: BUG #14808: V10-beta4, backend abort

I wrote:
> Note the "uniquely identified" bit --- you aren't allowed to have multiple
> SCs for the same table and same kind of event, at least up to the bit
> about column lists. I've not fully wrapped my head around the column list
> part of it, but certainly all effects of a particular RI constraint will
> have the same column list.

After further study of the standard, it seems that the column list
business is meant to allow identification of which UPDATE triggers
with column lists ("AFTER UPDATE OF columnList" syntax) are supposed
to be triggered. The spec is confusing because they describe this in
a way that would be impossibly inefficient if implemented verbatim.
They say that, given a row UPDATE affecting a certain set of columns,
you're supposed to create state changes labeled with every possibly
relevant trigger column set:

— Let OC be the set of column names identifying the columns [being updated]
— Let PSC be the set consisting of the empty set and every subset of the
set of column names of [the target table] that has at least one column
that is in OC
- [ create a state change for each element of PSC ]

Then an update trigger is triggered by a particular state change if its
column list exactly matches that state change's list. This seems like a
remarkably stupid way to go about it; you'd end up with many state changes
that correspond to no existing trigger and are never of any use.

However, if I'm reading it right, there is a property of this that is
very significant in the context of RI updates. For an ordinary UPDATE
SQL command, all the row updates have the same set of target columns,
but that's not necessarily true for all the RI updates that a single SQL
command could trigger. If there's more than one RI constraint between
two tables then an update on the referenced table could trigger sets of
updates that affect overlapping, but not identical, sets of rows in the
referencing tables --- and those sets would have different sets of target
columns. So a given column-specific trigger might be interested in some
or all of the RI updates. And if it is interested, and is a statement
trigger, it is supposed to be fired just once with a transition table
containing all the rows it is interested in.

In other words, UPDATE triggers with different column lists potentially
need to see different transition tables, and any given row that was
updated might need to appear in some subset of those tables.

This seems like rather a mess to implement. I wonder whether I'm
reading it right, and whether other DBMSes actually implement it
like that.

I think that what might be a good short-term solution is to refuse
creation of column-specific triggers with transition tables (ie,
if you ask for a transition table then you can only say AFTER UPDATE
not AFTER UPDATE OF columnList). Then, all TT-using triggers are
interested in all modified rows and we don't have to distinguish
different column lists for the purposes of transition tables.
Then the problem reduces to one TCS per target table and event type,
which doesn't seem too hard to do.

regards, tom lane