Re: UPSERT wiki page, and SQL MERGE syntax

Peter Geoghegan <pg(at)heroku(dot)com> wrote:

> The page is: https://wiki.postgresql.org/wiki/UPSERT

Thanks!

I have two questions I hope you can clarify. I'm having trouble
parsing what this statement means:

> ... the SQL standard does not require that MERGE be atomic in the
> sense of atomically providing either an INSERT or UPDATE, ...

My understanding is that the standard logically requires (without
concern for implementation details) that the second specified table
(via table name or subquery -- which could be a VALUES statement)
is scanned, and for each row it attempts to match a row in the
target table. That will either match or not, according to the
boolean expression in the ON clause. You can have one WHEN MATCHED
THEN UPDATE clause and/or one WHEN NOT MATCHED THEN INSERT clause.
If both clauses are present, I believe that it is guaranteed that
one or the other (but not both) will fire for each row in the
second table. The usual guarantees for each isolation level must
not be violated (although an implementation is not required to
generate anomalies which could not happen with serial execution).
So as usual for a transaction involving multiple tables,
serialization anomalies are possible if the transaction isolation
level is REPEATABLE READ or less. Is that what you're getting at,
or something else?

Regarding this example:

> -- Predicate within UPDATE auxiliary statement
> -- (row is still locked when the UPDATE predicate
> -- isn't satisfied):
> INSERT INTO upsert(key, val) VALUES(1, 'insert')
> -- CONFLICTING() carries forward effects of both INSERT and UPDATE BEFORE row-level triggers
> ON CONFLICT UPDATE SET val = CONFLICTING(val)
> -- Predicate has interesting semantics visibility-wise:
> WHERE val != 'delete';

Can you explain what the WHERE clause there does?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company