Vick Khera <vivek@xxxxxxxxx> writes: > On Wed, Feb 15, 2017 at 10:32 AM, pinker <pinker@xxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
