I think I probably meant to add that comment on the prior answer, about two separate statements. It was over a year ago, so I'm not totally sure anymore.
The wCTE based query doesn't really solve the problem it's supposed to, but upon reviewing it again over a year later I don't see the possibility of lost updates in the wCTE version.
(Note that all of these solutions will only work well if you try to change exactly one row with each transaction. As soon as you try to do multiple changes in one transaction things get messy because of the need for retry loops on rollbacks. At minimum you'd need to use a savepoint between each change.)
Two-statement version subject to lost updates.
The version that uses two separate statements is subject to lost updates unless the application checks the affected-row count from the
UPDATE statement and the
INSERT statement and retries if both are zero.
Imagine what happens if you have two transactions in
READ COMMITTED isolation.
- TX1 runs the
UPDATE (no effect)
- TX1 runs the
INSERT (inserts a row)
- TX2 runs the
UPDATE (no effect, row inserted by TX1 isn't visible yet)
- TX2 runs the
INSERT, *which gets a new snapshot that can see the row committed by TX1. The
EXISTS clause returns true, because TX2 can now see the row inserted by TX1.
So TX2 has no effect. Unless the app checks the rowcount from the update and the insert and retries if both report zero rows, it won't know that the transaction had no effect and will merrily carry on.
The only way it can check the affected rowcounts is to run it as two separate statements rather than a multi-statement, or use a procedure.
You can use
SERIALIZABLE isolation, but you'll still need a retry loop to deal with serialization failures.
The wCTE version protects against the lost updates issue because the
INSERT is conditional on whether the
UPDATE affects any rows, rather than on a separate query.
The wCTE doesn't eliminate unique violations
The writeable CTE version still isn't a reliable upsert.
Consider two transactions that run this concurrently.
Both execute the VALUES clause.
Now both of them execute the
UPDATE portion. Since there are no rows matching the
UPDATEs where clause, both return an empty resultset from the update and make no changes.
Now both run the
INSERT portion. Since the
UPDATE returned zero rows for both queries, both attempt to
INSERT the row.
One succeeds. One throws a unique violation and aborts.
This isn't cause for concern about data loss so long as the app checks for error results from its queries (i.e. any decently written app) and re-tries, but it makes the solution no better than the existing two-statement versions. It doesn't eliminate the need for a retry loop.
The advantage the wCTE offers over the existing two-statement version is that it uses the output of the
UPDATE to decide whether to
INSERT, instead of using a separate query against the table. That's partly an optimisation, but it partly protects against a problem with the two-statement version that causes lost updates; see below.
You can run the wCTE in
SERIALIZABLE isolation, but then you'll just get serialization failures instead of unique violations. It won't change the need for a retry loop.
The wCTE does not appear to be vulnerable to lost updates
My comment suggested that this solution could result in lost updates, but upon reviewing that I think I may have been mistaken.
It's over a year ago, and I can't recall the exact circumstances, but I think I probably missed the fact that unique indexes have a partial exception from transaction visibility rules in order to allow one inserting transaction to wait for another to insert or roll back before proceeding.
Or perhaps I missed the fact that the
INSERT in the wCTE is conditional on whether the
UPDATE affected any rows, not on whether the candidate row exists in the table.
INSERTs on a unique index wait for commit/rollback
Say that one copy of the query runs, inserting a row. The change is not yet committed. The new tuple exists in the heap and the unique index, but it isn't visible to other transactions yet, irrespective of isolation levels.
Now another copy of the query runs. The inserted row is not yet visible as the first copy hasn't committed, so the update doesn't match anything. The query will go on to attempt an insert, which will see that another in-progress transaction is inserting that same key and will block waiting for that transaction to commit or roll back.
If the first transaction commits, the second one will fail with a unique violation, per the above. If the first transaction rolls back then the second will proceed with its insert instead.
INSERT being dependent on the
UPDATE rowcount protects against lost updates
Unlike in the two-statement case, I don't think the wCTE is vulnerable to lost updates.
UPDATE has no effect, the
INSERT will always run, because it's strictly conditional on whether the
UPDATE did anything, not on the external table state. So it can still fail with a unique violation, but it can't silently fail to have any effect and lose the update entirely.