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




view thread (11+ messages)  latest in thread

Message-ID: <4231CBEE.60109@musicrebellion.com>
Permalink:  ../4231CBEE.60109@musicrebellion.com/
Also on:    postgresql.org/message-id/4231CBEE.60109@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: <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