Re: postgres constraint triggers

On 09/27/2011 12:54 PM, Ben Chobot wrote:
> On Sep 26, 2011, at 10:52 AM, Maria L. Wilson wrote:
>
>> Our first try to solve this problem has been to convert these triggers
>> into a constraint trigger which allows for DEFERRABLE INITIALLY
>> DEFERRED flags. This, we are finding, is forcing the trigger function
>> to run after the triggering transaction is completed. We believe this
>> will fix our locking problem and hopefully speed up our inserts again.
>>
>> Any comments or past experiences would certainly be helpful!
>
> My memory is fuzzy but as I recall, a possible downside to using
> deferred constraints was increased memory usage

That's right. PostgreSQL doesn't currently support spilling of pending
constraint information to disk; it has to keep it in RAM, and with
sufficiently huge deferred updates/inserts/deletes it's possible for the
backend to run out of RAM to use.

> though I cannot see how at the moment.

A list of which triggers to run, and on which tuples, must be maintained
until those triggers are fired. That list has to be kept somewhere.

--
Craig Ringer