Re: UPSERT wiki page, and SQL MERGE syntax

Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Mon, Oct 6, 2014 at 8:35 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

>> 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.
>
> Can you please comment on this, Kevin? It would be nice to converge on
> an agreement on syntax here

Robert said "however spelled" -- which I take to mean that he at
least sees that the MERGE-like UPSERT syntax can be turned into the
desired semantics. I have no idea why anyone would think otherwise.

Although the last go-around does suggest that there is at least one
point of difference on the semantics. You seem to want to fire the
BEFORE INSERT triggers before determining whether this will be an
INSERT or an UPDATE. That seems like a bad idea to me, but if the
consensus is that we want to do that, it does argue for your plan
of making UPSERT a variant of the INSERT command. That doesn't
seem as clean to me as saying (for example):

UPSERT targettable t
USING (VALUES (123, 100)) x(id, quantity)
ON t.id = x.id
WHEN NOT MATCHED
INSERT (id, quantity, inserted_at) VALUES (x.id, x.quantity, now())
WHEN MATCHED
UPDATE SET quantity = t.quantity + x.quantity, updated_at = now();

As I understand it you are proposing that would be:

INSERT INTO targettable(key, quantity, inserted_at)
VALUES(123, quantity, now())
ON CONFLICT WITHIN targettable_pkey
UPDATE SET quantity = quantity + CONFLICTING(quantity), updated_at = now();

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