pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Race hazard deleting using CTID? 3+ messages / 2 participants [nested] [flat]
* Race hazard deleting using CTID? @ 2009-08-11 18:29 Peter Headland <pheadland@actuate.com> 0 siblings, 1 reply; 3+ messages in thread From: Peter Headland @ 2009-08-11 18:29 UTC (permalink / raw) To: pgsql-general@postgresql.org; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pavel Stehule <pavel.stehule@gmail.com> I believe that the following statement was originally suggested by Tom Lane; I got it from Pavel Stehule's PostgreSQL Tips page. 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)) 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)) -- Peter Headland Architect Actuate Corporation ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Race hazard deleting using CTID? @ 2009-08-11 19:00 Tom Lane <tgl@sss.pgh.pa.us> parent: Peter Headland <pheadland@actuate.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2009-08-11 19:00 UTC (permalink / raw) To: Peter Headland <pheadland@actuate.com>; +Cc: pgsql-general@postgresql.org, "Pavel Stehule" <pavel.stehule@gmail.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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Race hazard deleting using CTID? @ 2009-08-11 20:13 Peter Headland <pheadland@actuate.com> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Peter Headland @ 2009-08-11 20:13 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@postgresql.org; +Cc: Pavel Stehule <pavel.stehule@gmail.com> > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-08-11 20:13 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2009-08-11 18:29 Race hazard deleting using CTID? Peter Headland <pheadland@actuate.com> 2009-08-11 19:00 ` Tom Lane <tgl@sss.pgh.pa.us> 2009-08-11 20:13 ` Peter Headland <pheadland@actuate.com>
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