Peter Geoghegan <p...@heroku.com> wrote: > On Mon, Oct 6, 2014 at 8:35 AM, Robert Haas <robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers