Re: Coping with huge deferred-trigger lists

Tom Lane wrote:
> I had a thought just now about how to deal with the TODO item about
> coping with deferred trigger lists that are so long as to overrun
> main memory. This might be a bit harebrained, but I offer it for
> consideration:
>
> What we need to do, at the end of a transaction in which deferred
> triggers were fired, is to find each tuple that was inserted or
> updated in the current transaction in each table that has such
> triggers. Well, we know where those tuples are: to a first
> approximation, they're all near the end of the table. Perhaps instead
> of storing each and every trigger-related tuple in memory, we only need
> to store one value per affected table: the lowest CTID of any tuple
> that we need to revisit for deferred-trigger purposes. At the end of
> the transaction, scan forward from that point to the end of the table,
> looking for tuples that were inserted by the current xact. Process each
> one using the table's list of deferred triggers.
>
> Instead of a list of all tuples subject to deferred triggers, we now
> need only a list of all tables subject to deferred triggers, which
> should pose no problems for memory consumption. It might be objected
> that this means more disk activity --- but in an xact that hasn't
> inserted very many tuples, most likely the disk blocks containing 'em
> are still in memory and won't need a physical re-read. Once we get to
> inserting so many tuples that that's not true, this approach should
> require less disk activity overall than the previous idea of writing
> (and re-reading) a separate disk file for the tuple list.
>
> I am not sure exactly what the "triggered data change violation" test
> does or is good for, but if we want to keep it, I *think* that in these
> terms we'd just need to signal error if we come across a tuple that was
> both inserted and deleted by the current xact. I'm a bit fuzzy on this
> though.

The check came from my possible wrong understanding of the
SQL3 specs. The idea I had is that the SUMMARY of all
changes during a transaction counts. If you INSERT a row into
a table and have immediate triggers invoked, a later DELETE
cannot undo the triggers. So the question is did this row
ever exist?

>
> An interesting property of this approach is that if the set of triggers
> for the table changes during the xact (which could only happen if this
> same xact created or deleted triggers; no other xact can, since changing
> triggers requires an exclusive lock on the table), the set of triggers
> applied to a tuple is the set that exists at the end of the xact, not
> the set that existed when the tuple was modified. Offhand I think this
> is a good change.
>
> Comments?

Giving you have two separate, named, deferred constraints on
one table. Now after a couple of INSERTs and UPDATEs you SET
one of them to IMMEDIATE and back to DEFERRED. This has to
run the triggers for one and only one of the constraints now.
If you don't worry about the need of running the checks later
again, it's OK.

The detail I'm wondering about most is how you'd know in an
UPDATE case which two tuples (one deleted during this XACT
and one inserted) are the two for OLD and NEW in the call to
the trigger. Note that the referential action ON UPDATE SET
NULL for example doesn't have to take place if the user
didn't change the referenced key fields.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com