Date: Wed, 11 Jan 2006 13:40:37 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: Summary table trigger example race condition
In-reply-to: <20060111000951.GQ3902@pervasive.com>
To: "Jim C. Nasby" <jnasby@pervasive.com>
Cc: pgsql-patches@postgresql.org, pgsql-docs@postgresql.org
Message-id: <43C45405.40002@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <20060105213645.GY43311@pervasive.com>
	<43BDC132.90007@paradise.net.nz> <20060106030415.GL43311@pervasive.com>
	<43BDE812.5000103@paradise.net.nz> <43C0833D.4040607@paradise.net.nz>
	<20060111000951.GQ3902@pervasive.com>

Jim C. Nasby wrote:
> On Sun, Jan 08, 2006 at 04:13:01PM +1300, Mark Kirkwood wrote:
> 
>>After re-examining the original code, it looks like it was not actually 
>>vulnerable to a race condition! (it does the UPDATE, then if not found 
>>will do an INSERT, and handle unique violation with a repeat of the same 
>>UPDATE - i.e three DML statements, which are enough to handle the race 
>>in this case).
> 
> 
> What happens if someone deletes the row between the failed insert and
> the second update? :)
> 

In this example the rows in the summary table never get deleted by 
DELETE operations on that main one - the trigger just decrements the 
various amounts - i.e DELETE becomes UPDATE, so no problem there.

> AFAICT, example 36-1 is the only way to handle this without creating a
> race condition.
> 

For the general case indeed you are correct - a good reason for this 
change :-). In addition, that fact that it is actually quite difficult 
to be sure that any race condition is actually being handled makes 
(another) good reason for using the most robust method in the 'official' 
examples.

Best wishes

Mark