Re: Using ctid in delete statement

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. So at worst you will try to delete the
same object twice, which in this case is no harm, no foul. That ctid will
not be able to point to some "other" object until your transaction is
completed and the old rows are vacuumed.