Обсуждение: [GENERAL] Using ctid in delete statement
Hi, is it safe to use ctid in following query? : 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? regards -- View this message in context: http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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.
thank you for the answer -- View this message in context: http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944441.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Vick Khera <vivek@khera.org> writes: > 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. 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
Thank you Tom for clarification. Does it mean that FOR UPDATE clause works with other operations as well? i.e. TRUNCATE, DELETE? -- View this message in context: http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944658.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 02/16/2017 02:04 AM, pinker wrote: > Thank you Tom for clarification. > Does it mean that FOR UPDATE clause works with other operations as well? > i.e. TRUNCATE, DELETE? https://www.postgresql.org/docs/9.6/static/sql-truncate.html https://www.postgresql.org/docs/9.6/static/sql-delete.html > > > > -- > View this message in context: http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944658.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > https://www.postgresql.org/docs/9.6/static/sql-truncate.html > > https://www.postgresql.org/docs/9.6/static/sql-delete.html There is nothing about FOR UPDATE clause on those pages... -- View this message in context: http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944720.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 02/16/2017 06:52 AM, pinker wrote: > Adrian Klaver-4 wrote >> https://www.postgresql.org/docs/9.6/static/sql-truncate.html >> >> https://www.postgresql.org/docs/9.6/static/sql-delete.html > > There is nothing about FOR UPDATE clause on those pages... Exactly, they do not have it whereas: https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > Exactly, they do not have it whereas: > > https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE Still not much. The documentation could be more verbose on this topic. I can only presume that since there is an example with select: SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5; it's propably possible, but there is no information when the lock is released (on commit like in oracle?) especially if there is no explicit BEGIN/END clause like in this case. Oracle documentation is much more clear about it: You can also use SELECT FOR UPDATE to lock rows that you do not want to update, as in Example 9-6. <http://docs.oracle.com/database/122/LNPLS/static-sql.htm#LNPLS00609> -- View this message in context: http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944733.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 02/16/2017 07:42 AM, pinker wrote: > Adrian Klaver-4 wrote >> Exactly, they do not have it whereas: >> >> https://www.postgresql.org/docs/9.6/static/sql-select.html#SQL-FOR-UPDATE-SHARE > > Still not much. The documentation could be more verbose on this topic. I can > only presume that since there is an example with select: > SELECT * FROM (SELECT * FROM mytable FOR UPDATE) ss WHERE col1 = 5; > it's propably possible, but there is no information when the lock is > released (on commit like in oracle?) especially if there is no explicit > BEGIN/END clause like in this case. From above section: For more information on each row-level lock mode, refer to Section 13.3.2. which takes you to: https://www.postgresql.org/docs/9.6/static/explicit-locking.html#LOCKING-ROWS " FOR UPDATE FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4. The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future. " Which has: "For further discussion see Section 13.4.": https://www.postgresql.org/docs/9.6/static/applevel-consistency.html And from there links to more information. > > > Oracle documentation is much more clear about it: > You can also use SELECT FOR UPDATE to lock rows that you do not want to > update, as in Example 9-6. > <http://docs.oracle.com/database/122/LNPLS/static-sql.htm#LNPLS00609> > > > > > > > > -- > View this message in context: http://postgresql.nabble.com/Using-ctid-in-delete-statement-tp5944434p5944733.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote
> https://www.postgresql.org/docs/9.6/static/sql-truncate. html
>
> https://www.postgresql.org/docs/9.6/static/sql-delete. html
There is nothing about FOR UPDATE clause on those pages...
Both truncate and delete are commands the inherently update tables - there is no need to have a separate FOR UPDATE clause.
Dave