Message-ID: <4E849BAD.1090405@nasa.gov>
Date: Thu, 29 Sep 2011 12:24:13 -0400
From: "Maria L. Wilson" <Maria.L.Wilson-1@nasa.gov>
Reply-To: <Maria.L.Wilson-1@nasa.gov>
Organization: SSAI
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.6;
	rv:6.0.2) Gecko/20110902 Thunderbird/6.0.2
MIME-Version: 1.0
To: Craig Ringer <ringerc@ringerc.id.au>
CC: "Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS AND APPLICATIONS, INC]"
	<m.l.wilson@nasa.gov>, "pgsql-admin@postgresql.org"
	<pgsql-admin@postgresql.org>
Subject: Re: constraint triggers
References: <4E83190B.3070604@nasa.gov> <4E83DA7B.7000101@ringerc.id.au>
In-Reply-To: <4E83DA7B.7000101@ringerc.id.au>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

thanks Craig - we are doing some testing with some of your ideas 
now....  Hopefully we can get this solved so using triggers doesn't 
cause so much contention.  I also understand that foreign keys can also 
cause some of these issues.  Do you have any thoughts on that?

thanks again,  Maria Wilson

On 9/28/11 10:39 PM, Craig Ringer wrote:
> On 09/28/2011 08:54 PM, Maria L. Wilson wrote:
>
>> UPDATE dataset
>> SET gracount = gracount + 1
>> WHERE dataset.inv_id = NEW.inv_id;
> That'll serialize all transactions that touch the same inv_id, so only
> one may run at once. The second and subsequent ones will block waiting
> for an update lock on the `dataset' tuple for `inv_id'.
>
> When you think about it that's necessary to prevent a wrong result when
> transaction A then B run this statement, transaction B commits, and
> transaction A rolls back. What's the correct answer?
>
> To fix this you'll need to change how you maintain your `dataset' table.
> Exactly how depends on your requirements.
>
> You can trade read performance off against write performance by
> INSERTing new rows instead of UPDATEing them, so you do a:
>
>     SELECT count(inv_id) FROM dataset WHERE dataset.inv_id = ?
>
> instead of a
>
>     SELECT gracount FROM dataset WHERE dataset.inv_id = ?
>
> to retrieve your count.
>
> You can have both fast reads and fast writes if you accept potentially
> out-of-date `gracount' data, maintaining `gracount' as a materialized
> view that you refresh using LISTEN/NOTIFY *after* a transaction commits.
> It's possible for it to be a little out of date, but writers no longer
> interfere with each other and readers no longer have to re-do the
> counting/aggregation work.
>
> You can live with serializing writes like you currently do in exchange
> for the greater read performance of maintaining counts. This may not be
> so bad once you understand what's happening and can keep transactions
> that trigger this problem short, preventing them from blocking others
> while they do unrelated work.
>
> In the end, this looks like a simple problem but it isn't when you
> consider the possibility of transactions rolling back.
>
>> 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.
> That should help, but it's a form of trading timeliness off against
> performance. Queries within the same transaction won't see the updated
> `dataset' values, so if you're relying on them for correct operation
> later in the transaction you could have issues.
>
> --
> Craig Ringer