On Fri, Oct 3, 2014 at 4:16 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>> ... 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?
I think the problem is that it's not possible to respect the "usual
guarantees" even at READ COMMITTED level when performing an INSERT OR
UPDATE operation (however spelled). You may find that there's a tuple
with the same PK which is committed but not visible to the snapshot
you took at the beginning of the statement. An INSERT would fail; an
UPDATE would see no rows and do nothing. IOW, *neither* operation
succeeds according to its classic semantics; to succeed, the INSERT OR
UPDATE has to do something altogether novel.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company