Message-ID: <4E826C3B.7030504@ringerc.id.au>
Date: Wed, 28 Sep 2011 08:37:15 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:6.0.2) Gecko/20110906 Thunderbird/6.0.2
MIME-Version: 1.0
To: Ben Chobot <bench@silentmedia.com>
CC: Maria.L.Wilson-1@nasa.gov,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: postgres constraint triggers
References: <4E80ADD7.8090008@nasa.gov>
	<3F7F288F-A4DC-4AFD-B538-FD6C9A943C65@silentmedia.com>
In-Reply-To: <3F7F288F-A4DC-4AFD-B538-FD6C9A943C65@silentmedia.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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