Oracle Restart Update. Is there something similar in Postgresql?

Question

I'm reading the book "Oracle Database Transactions and Locking Revealed" - Chapter 4 "Concurrency and Multiversioning". In Oracle we have Restart Update (Oracle will restart the Update statement from scratch). Is there something similar in Postgresql?

Oracle Example:

Tx1: Update table set col1=0 (old value was col1=2);
<no commit>;
Tx2: Update table set col2=... where col1>0;
Tx1: commit;
Tx2: the update is restared by Oracle Database;

Here is a link describing what "restart of an update" means.

Answer 1

This "restarted update" seems to be Oracle's way of dealing with that problem that in READ COMMITTED isolation level, the row we want to update could get modified by a concurrent transaction before we get to lock it.

PostgreSQL has to deal with the same problem, but it does not solve it by starting the complete UPDATE statement from scratch. Instead, it only retrieves the row that has changed again, checks if it still satisfies the WHERE condition and uses that updated row for further processing.

The documentation phrases that as follows:

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). [...] If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

This technique goes by the internal name of "EvalPlanQual" in PostgreSQL. If you want to know more details, I recommend reading the corresponding section in the executor README.

The Oracle way of doing this seems laborious and expensive (rerun the whole statement), while PostgreSQL is more efficient in re-evaluating only those rows that have changed. On the down side, the PostgreSQL technique can lead to surprising anomalies, as described in this article.