Thread: [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.

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