Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 556D4634A37
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Tue, 11 Aug 2009 17:13:30 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 11143-06
	for <pgsql-general-postgresql.org@mail.postgresql.org>;
	Tue, 11 Aug 2009 20:13:15 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from wyattearp.actuate.com (mx1.actuate.com [69.1.81.237])
	by mail.postgresql.org (Postfix) with SMTP id E7EED63433E
	for <pgsql-general@postgresql.org>;
	Tue, 11 Aug 2009 17:13:18 -0300 (ADT)
Received: from exchange8.actuate.com (Not Verified[192.168.11.22]) by
	wyattearp.actuate.com with MailMarshal (v6, 1, 4, 441)
	id <B4a81d0b90000>; Tue, 11 Aug 2009 13:12:41 -0700
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Race hazard deleting using CTID? 
Date: Tue, 11 Aug 2009 13:13:05 -0700
Message-ID: <71F491F5DA99604A80DE49424BF3D02B0C729459@exchange8.actuate.com>
In-Reply-To: <28099.1250017252@sss.pgh.pa.us>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: Race hazard deleting using CTID? 
Thread-Index: AcoatiQ8Pxkp5516TfOgQPJtON8XQwACOvlw
References: <71F491F5DA99604A80DE49424BF3D02B0C7290CA@exchange8.actuate.com>
	<20090811062503.86993bf6.darcy@druid.net>
	<162867790908110355g3b62cc56u1c74092dd01bbd8e@mail.gmail.com>
	<71F491F5DA99604A80DE49424BF3D02B0C7292EF@exchange8.actuate.com>
	<162867790908111022y48e65d31kcf5a037c1b41857f@mail.gmail.com>
	<71F491F5DA99604A80DE49424BF3D02B0C7293A0@exchange8.actuate.com>
	<28099.1250017252@sss.pgh.pa.us>
From: "Peter Headland" <pheadland@actuate.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>,
	<pgsql-general@postgresql.org>
Cc: "Pavel Stehule" <pavel.stehule@gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.564 tagged_above=-10 required=5 tests=AWL=1.035,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200908/377
X-Sequence-Number: 151578

> Well, the CTID of a row you can see can't be changed by another
> transaction while your transaction is still live.  However, if
> someone else does modify/delete one of those rows concurrently,
> it will fail the outer WHERE check and thus silently not be
> deleted.  Net effect is that you might delete fewer than 10
> rows.  Not sure if you'd consider that a race hazard or not.

Thanks for the explanation. My actual use-case is an UPDATE, and
updating fewer rows than I wanted to is definitely not the desired
behavior.

It's a race hazard condition by definition if the behavior of a system
is non-deterministic due to timing of another process.

This is all in a pl/pgsql function.

I was trying to avoid a cursor and loop solution in the hope of better
performance. Sounds like I need to separate the SELECT into a top-level
statement and get the list of ctids into an array variable that I then
use in the DELETE (actually an UPDATE in my case, but that's beside the
point). I know this question would be better on new users, but since we
already have all the context established, I'd be grateful to know how to
get the ctids into an array variable in pl/pgsql.

--=20
Peter Headland
Architect
Actuate Corporation


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Tuesday, August 11, 2009 12:01
To: Peter Headland
Cc: pgsql-general@postgresql.org; Pavel Stehule
Subject: Re: Race hazard deleting using CTID?=20

"Peter Headland" <pheadland@actuate.com> writes:
> My question is, does this code contain a race hazard, because the list
from the SELECT might get changed by another session before the DELETE
uses it?

>   delete from del where ctid =3D any(array(select ctid from del limit
10))

Well, the CTID of a row you can see can't be changed by another
transaction while your transaction is still live.  However, if someone
else does modify/delete one of those rows concurrently, it will fail the
outer WHERE check and thus silently not be deleted.  Net effect is that
you might delete fewer than 10 rows.  Not sure if you'd consider that a
race hazard or not.

> If so, am I correct to think that adding FOR UPDATE to create the
version below would eliminate the hazard?

>   delete from del where ctid =3D any(array(select ctid from del limit =
10
for update))

If you'd bothered to try that before asking the list, you'd know the
system won't take it --- FOR UPDATE is only supported at top level.
You could probably do something equivalent using a plpgsql loop, or
pulling the CTIDs back to the client side.

			regards, tom lane