Race hazard deleting using CTID?

From: "Peter Headland" <pheadland(at)actuate(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
Subject: Race hazard deleting using CTID?
Date: 2009-08-11 18:29:26
Message-ID: 71F491F5DA99604A80DE49424BF3D02B0C7293A0@exchange8.actuate.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-08-11 19:00:52 Re: Race hazard deleting using CTID?
Previous Message Peter Headland 2009-08-11 18:12:10 Does PERFORM hold a lock?

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2009-08-11 19:00:52 Re: Race hazard deleting using CTID?
Previous Message Peter Headland 2009-08-11 17:24:54 Re: Updating a specific number of rows in pl/pgsql