From: Craig Ringer <ringerc@ringerc.id.au>
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
Date: Wed, 28 Sep 2011 08:37:15 +0800
Message-ID: <4E826C3B.7030504@ringerc.id.au> (raw)
In-Reply-To: <3F7F288F-A4DC-4AFD-B538-FD6C9A943C65@silentmedia.com>
References: <4E80ADD7.8090008@nasa.gov>
<3F7F288F-A4DC-4AFD-B538-FD6C9A943C65@silentmedia.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ringerc@ringerc.id.au, bench@silentmedia.com
Subject: Re: postgres constraint triggers
In-Reply-To: <4E826C3B.7030504@ringerc.id.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox