On Wed, Feb 15, 2017 at 10:32 AM, pinker <pinker@onet.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.
