Peter Geoghegan <p...@heroku.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
