pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kris Kiger <kris@musicrebellion.com>
To: Pgsql-Admin (E-mail) <pgsql-admin@postgresql.org>
Subject: Re: Functions and transactions
Date: Thu, 10 Mar 2005 14:57:30 -0500
Message-ID: <4230A6AA.6030204@musicrebellion.com> (raw)
In-Reply-To: <15242.1110469332@sss.pgh.pa.us>
References: <421CF15C.6010306@turtle-entertainment.de>
	<421CF1CE.3000800@turtle-entertainment.de>
	<422F6203.1090800@musicrebellion.com>
	<7701.1110407641@sss.pgh.pa.us>
	<42305968.10100@musicrebellion.com>
	<15242.1110469332@sss.pgh.pa.us>

In your second paragraph, I think that you are saying that SELECT FOR 
UPDATE only locks one row, even though the select itself may return 
many.  Am I mis-interpreting you?  Also, what do you mean by seizing on 
a non-active row? 

Your assumption about pkey_id is right, I meant for that to mean partial 
key, bad naming on my part ;-). 

In my case, the third paragraph applies to this situation, because I can 
assume that there will always be an entry in the table that will be 
active with that pkey_id (lets call this partialKey_id from now on, to 
avoid further confusion).

The alternative you offer is a good idea, I didn't realize that I had 
the option to create a unique index on a subset of data within the 
table.  Unfortunately, it will not work in this situation.  I don't have 
the option to report failure to the front-end application.  I suppose, 
i'm looking for a method to only allow one invocation of this function, 
per partialKey_id, at a time.

If you have any other alternatives or suggestions, I'm all ears, err 
eyes...  Anyway, thank you ;-)

Kris


Tom Lane wrote:

>Mmm.  This might work as you expect in 8.0, but it surely won't in any
>prior release, because before 8.0 we didn't advance the transaction
>snapshot between statements of a function.
>
>Another issue is that your SELECT FOR UPDATE locks only one of the
>rows having the target pkey_id (I assume that column is misnamed and
>isn't actually a primary key?).  If it happened to seize on a non-active
>row then it might not accomplish your goal of blocking until other
>updaters of the same row set commit.  That would allow the UPDATE to
>start and set its snapshot, which would mean you lose because it
>wouldn't see the newly inserted row from the other transaction.
>
>Even more to the point, if there aren't yet any committed rows at all of
>the target pkey_id, there is nothing for the SELECT FOR UPDATE to block
>on at all.  You could fix the first problem in various ways but I see no
>way around this one.  Unless you can guarantee that there will always be
>a suitable row already in existence, I think you have to abandon the
>idea of using a SELECT FOR UPDATE for locking.
>
>One possibility is to create a unique partial index:
>
>CREATE UNIQUE INDEX foo ON table1(pkey_id) WHERE active ;
>
>This will provide an enforcement that you don't have more than one
>active row at a time.  Now you just simplify the trigger to
>    update table1 set active = false where NEW.pkey_id = pkey_id and active;
>    NEW.active := true;
>Race conditions will end up causing unique-key errors, which you can just
>retry.
>
>			regards, tom lane
>  
>




view thread (11+ messages)  latest in thread

Message-ID: <4230A6AA.6030204@musicrebellion.com>
Permalink:  ../4230A6AA.6030204@musicrebellion.com/
Also on:    postgresql.org/message-id/4230A6AA.6030204@musicrebellion.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: kris@musicrebellion.com
  Subject: Re: Functions and transactions
  In-Reply-To: <4230A6AA.6030204@musicrebellion.com>
  References: <421CF15C.6010306@turtle-entertainment.de> <421CF1CE.3000800@turtle-entertainment.de> <422F6203.1090800@musicrebellion.com> <7701.1110407641@sss.pgh.pa.us> <42305968.10100@musicrebellion.com> <15242.1110469332@sss.pgh.pa.us> <4230A6AA.6030204@musicrebellion.com>

* 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