From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Headland <pheadland@actuate.com>
Cc: pgsql-general@postgresql.org, "Pavel Stehule" <pavel.stehule@gmail.com>
Subject: Re: Race hazard deleting using CTID?
Date: Tue, 11 Aug 2009 15:00:52 -0400
Message-ID: <28099.1250017252@sss.pgh.pa.us> (raw)
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>
"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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, pheadland@actuate.com, pavel.stehule@gmail.com
Subject: Re: Race hazard deleting using CTID?
In-Reply-To: <28099.1250017252@sss.pgh.pa.us>
* 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