> 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@xxxxxxxxxxxxx] Sent: Tuesday, August 11, 2009 12:01 To: Peter Headland Cc: pgsql-general@xxxxxxxxxxxxxx; Pavel Stehule Subject: Re: Race hazard deleting using CTID? "Peter Headland" <pheadland@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
