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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
