Coping with huge deferred-trigger lists 
Author Message
 Coping with huge deferred-trigger lists

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

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

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.


                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

Mon, 27 Oct 2003 00:14:00 GMT
 [ 1 post ] 

 Relevant Pages 

1. Coping with huge deferred-trigger lists

2. Any Way to Defer Triggers?

3. Fix disabled triggers with deferred constraints

4. Why are triggers semi-deferred?

5. disabled, deferred triggers

6. disabled, deferred triggers

7. Fix disabled triggers with deferred constraints

8. Fix disabled triggers with deferred constraints

9. Is Patch Ok for deferred trigger disk queue?

10. Deferred name resolution in stored procs and triggers but not views

11. disabled, deferred triggers

12. Fix disabled triggers with deferred constraints

Powered by phpBB® Forum Software