Peter Geoghegan <pg@heroku.com> wrote:
> On Fri, Oct 3, 2014 at 1:16 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>> 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, ...
> ... always getting an insert or update, never an error ...
I've never seen "atomic" used to mean "you never get an error"
before. Perhaps it would be clearer to replace "atomic" with
"error-free" or some such. It certainly would be less confusing
for me. "Atomic" in most cases is a property that can be enforced
by generating an error where it would otherwise be violated.
For example: http://en.wikipedia.org/wiki/Atomicity_%28database_systems%29
"Although implementations vary depending on factors such as concurrency issues, the principle of atomicity — i.e.
complete success or complete failure — remain."
When you are saying "atomic" you mean something quite different.
> My complaint is quite straightforward, really. I don't want to
> have to tell users to do this: http://stackoverflow.com/a/22777749
I think you are confusing syntax with semantics. I grant that a
reasonable person could have concerns about using the MERGE syntax
to implement the semantics you want in the special case that an
appropriate unique index exists, but pretending that the semantics
can't be achieved with that syntax is just silly.
> At the same time, I also don't want to have to live with the
> consequences of implementing a MERGE that does not exhibit that
> behavior. Which is to say, the consequences of doing something
> like selectively using different types of snapshots (MVCC or
> dirty - the two different ideas of "each row" that are in
> tension) based on the exact clauses used. That seems like asking
> for trouble, TBH.
Now *that* is getting more to a real issue. We routinely pick very
different plans based on the presence or absence of an index, and
we use special snapshots in the course of executing many DML
statements (if FK triggers are fired), but this would be the first
place I can think of that the primary DML statement behaves that
way. You and a couple others have expressed concern about that,
but it seems pretty vague and hand-wavey. If a different execution
node is used for the special behavior, and that node is generated
based on the unique index being used, I'm really having problems
seeing where the problem lies.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
