Using ctid in delete statement

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt;

--
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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt;

--
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Feb 16, 2017 at 7:52 AM, pinker <pinker@onet.eu> 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...

​Both truncate and delete are commands the inherently update tables - there
is no need to have a separate FOR UPDATE clause.

Dave
​