> 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.
--
Peter Headland
Architect
Actuate Corporation
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, August 11, 2009 12:01
To: Peter Headland
Cc: pgsql-general@postgresql.org; Pavel Stehule
Subject: Re: Race hazard deleting using CTID?
"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
