To: "Peter Headland" <pheadland@actuate.com>
cc: pgsql-general@postgresql.org, "Pavel Stehule" <pavel.stehule@gmail.com>
Subject: Re: Race hazard deleting using CTID? 
In-reply-to: <71F491F5DA99604A80DE49424BF3D02B0C7293A0@exchange8.actuate.com> 
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>
Comments: In-reply-to "Peter Headland" <pheadland@actuate.com>
	message dated "Tue, 11 Aug 2009 11:29:26 -0700"
Date: Tue, 11 Aug 2009 15:00:52 -0400
Message-ID: <28099.1250017252@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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