Re: Using ctid in delete statement

Vick Khera <vivek(at)khera(dot)org> writes:
> On Wed, Feb 15, 2017 at 10:32 AM, pinker <pinker(at)onet(dot)eu> wrote:
>> DELETE FROM table_name WHERE ctid = any ( array ( select tn.ctid from
>> table_name tn JOIN items i on tn.itemid=i.itemid WHERE tn.clock < extract (
>> epoch FROM now() - i.history * interval '10 day')::int + 6 limit 100));
>>
>> Could I be sure that ctid will not change during the execution or will not
>> do any harm to other transactions?

> It will be safe for two reasons: 1) your statement is running in its own
> implicit transaction, and 2) the rows selected from the subquery are
> visible to your transaction and thus will not have been "cleaned up" for
> re-use by any other transaction.

I think it would be a lot safer with the inner SELECT changed to SELECT
FOR UPDATE. As you say, the ctid seen by a plain SELECT couldn't get
recycled for use by a new tuple while the transaction is still alive,
but as-is there's certainly a hazard that the row is updated by another
transaction. Then the ctid would point to an already-dead tuple so the
DELETE wouldn't do anything, which is unlikely to be the desired result.
With SELECT FOR UPDATE, you'd have a tuple lock preventing such race
conditions.

regards, tom lane