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
>
>
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