From: Kris Kiger <kris@musicrebellion.com>
To: Pgsql-Admin (E-mail) <pgsql-admin@postgresql.org>
Subject: Re: Functions and transactions
Date: Fri, 11 Mar 2005 11:48:46 -0500
Message-ID: <4231CBEE.60109@musicrebellion.com> (raw)
In-Reply-To: <27098.1110499829@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>
<4230A6AA.6030204@musicrebellion.com>
<27098.1110499829@sss.pgh.pa.us>
Interesting. That makes sense, though. So, is there a good way to lock
a set of rows using SELECT FOR UPDATE in plpgsql? I assume using
PERFORM would yield the same problem, because it immediately discards
the results.
Thanks!
Kris
Tom Lane wrote:
>Kris Kiger <kris@musicrebellion.com> writes:
>
>
>>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?
>>
>>
>
>No, I'm saying that plpgsql's SELECT INTO operation only reads one row.
>The fact that the SELECT might have found more rows if allowed to run
>to completion doesn't enter into it. If the first row read doesn't have
>active = true then it won't conflict against concurrent UPDATEs, because
>you are carefully not UPDATEing rows with active = false. It's the
>combination of those two things that creates the hazard.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
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: <4231CBEE.60109@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> <27098.1110499829@sss.pgh.pa.us> <4231CBEE.60109@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