Re: [PERFORM] Foreign key performance

Kevin Brown <kevin(at)sysexperts(dot)com> writes:
> Any chance of backporting these changes to 7_3_STABLE (when you're
> satisfied they don't break anything)? Just looking at the CVS log for
> trigger.c, it appears there have been enough changes since then that
> it might not be easy to do (and since it's not necessarily a "bug fix"
> as such, it might not qualify for backporting to a stable version).

I'd be pretty hesitant to make such a change in the stable branch ---
at least not without a lot of testing. If you and others want to
provide such testing, go to it. The patch appears to apply cleanly
enough to 7.3, but here's an adjusted patch if fuzz makes you nervous...

regards, tom lane

*** trigger.c~ Sun Apr 20 20:28:55 2003
--- trigger.c Sun Apr 20 20:29:13 2003
***************
*** 1461,1472 ****
--- 1461,1478 ----
* Because this can grow pretty large, we don't use separate List nodes,
* but instead thread the list through the dte_next fields of the member
* nodes. Saves just a few bytes per entry, but that adds up.
+ *
+ * deftrig_events_imm holds the tail pointer as of the last
+ * deferredTriggerInvokeEvents call; we can use this to avoid rescanning
+ * entries unnecessarily. It is NULL if deferredTriggerInvokeEvents
+ * hasn't run since the last state change.
*
* XXX Need to be able to shove this data out to a file if it grows too
* large...
* ----------
*/
static DeferredTriggerEvent deftrig_events;
+ static DeferredTriggerEvent deftrig_events_imm;
static DeferredTriggerEvent deftrig_event_tail;


***************
*** 1680,1686 ****
deferredTriggerInvokeEvents(bool immediate_only)
{
DeferredTriggerEvent event,
! prev_event = NULL;
MemoryContext per_tuple_context;
Relation rel = NULL;
TriggerDesc *trigdesc = NULL;
--- 1686,1692 ----
deferredTriggerInvokeEvents(bool immediate_only)
{
DeferredTriggerEvent event,
! prev_event;
MemoryContext per_tuple_context;
Relation rel = NULL;
TriggerDesc *trigdesc = NULL;
***************
*** 1692,1704 ****
* are going to discard the whole event queue on return anyway, so no
* need to bother with "retail" pfree's.
*
! * In a scenario with many commands in a transaction and many
! * deferred-to-end-of-transaction triggers, it could get annoying to
! * rescan all the deferred triggers at each command end. To speed this
! * up, we could remember the actual end of the queue at EndQuery and
! * examine only events that are newer. On state changes we simply
! * reset the saved position to the beginning of the queue and process
! * all events once with the new states.
*/

/* Make a per-tuple memory context for trigger function calls */
--- 1698,1709 ----
* are going to discard the whole event queue on return anyway, so no
* need to bother with "retail" pfree's.
*
! * If immediate_only is true, we need only scan from where the end of
! * the queue was at the previous deferredTriggerInvokeEvents call;
! * any non-deferred events before that point are already fired.
! * (But if the deferral state changes, we must reset the saved position
! * to the beginning of the queue, so as to process all events once with
! * the new states. See DeferredTriggerSetState.)
*/

/* Make a per-tuple memory context for trigger function calls */
***************
*** 1709,1715 ****
ALLOCSET_DEFAULT_INITSIZE,
ALLOCSET_DEFAULT_MAXSIZE);

! event = deftrig_events;
while (event != NULL)
{
bool still_deferred_ones = false;
--- 1714,1735 ----
ALLOCSET_DEFAULT_INITSIZE,
ALLOCSET_DEFAULT_MAXSIZE);

! /*
! * If immediate_only is true, then the only events that could need firing
! * are those since deftrig_events_imm. (But if deftrig_events_imm is
! * NULL, we must scan the entire list.)
! */
! if (immediate_only && deftrig_events_imm != NULL)
! {
! prev_event = deftrig_events_imm;
! event = prev_event->dte_next;
! }
! else
! {
! prev_event = NULL;
! event = deftrig_events;
! }
!
while (event != NULL)
{
bool still_deferred_ones = false;
***************
*** 1830,1835 ****
--- 1850,1858 ----
/* Update list tail pointer in case we just deleted tail event */
deftrig_event_tail = prev_event;

+ /* Set the immediate event pointer for next time */
+ deftrig_events_imm = prev_event;
+
/* Release working resources */
if (rel)
heap_close(rel, NoLock);
***************
*** 1917,1922 ****
--- 1940,1946 ----
MemoryContextSwitchTo(oldcxt);

deftrig_events = NULL;
+ deftrig_events_imm = NULL;
deftrig_event_tail = NULL;
}

***************
*** 2146,2153 ****
* CONSTRAINTS command applies retroactively. This happens "for free"
* since we have already made the necessary modifications to the
* constraints, and deferredTriggerEndQuery() is called by
! * finish_xact_command().
*/
}


--- 2170,2180 ----
* CONSTRAINTS command applies retroactively. This happens "for free"
* since we have already made the necessary modifications to the
* constraints, and deferredTriggerEndQuery() is called by
! * finish_xact_command(). But we must reset deferredTriggerInvokeEvents'
! * tail pointer to make it rescan the entire list, in case some deferred
! * events are now immediately invokable.
*/
+ deftrig_events_imm = NULL;
}