Re: UPSERT wiki page, and SQL MERGE syntax
От | Robert Haas |
---|---|
Тема | Re: UPSERT wiki page, and SQL MERGE syntax |
Дата | |
Msg-id | CA+TgmoZN=2AJKi1n4Jz5BkmYi8r_CPUDW+DtoppmTeLVmsOoqw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: UPSERT wiki page, and SQL MERGE syntax (Kevin Grittner <kgrittn@ymail.com>) |
Ответы |
Re: UPSERT wiki page, and SQL MERGE syntax
(Peter Geoghegan <pg@heroku.com>)
Re: UPSERT wiki page, and SQL MERGE syntax (Kevin Grittner <kgrittn@ymail.com>) |
Список | pgsql-hackers |
On Wed, Oct 8, 2014 at 5:01 PM, Kevin Grittner <kgrittn@ymail.com> wrote: > Peter Geoghegan <pg@heroku.com> wrote: >> On Mon, Oct 6, 2014 at 8:35 AM, Robert Haas <robertmhaas@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. OK, I have a comment on this. Anything we do about triggers will by definition be novel. Right now, we have INSERT, UPDATE, and DELETE. If we add a new operation, whether it's called UPSERT or MERGE or FROB, or if we add a flag to insert that makes it possibly do something other than inserting (e.g. INSERT OR UPDATE), or if we add a flag to update that makes it do something other than updating (e.g. UPDATE or INSERT), then some people's triggers are going to get broken by that change no matter how we do it. When the new operation is invoked, we can fire the insert triggers, the update triggers, some new kind of trigger, or no trigger at all - and any decision we make there will not in all cases be backward-compatible. We can try to minimize the damage (and we probably should) but we can't make it go to zero. > 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(); I actually like this syntax reasonably well in some ways, but I don't like that we're mentioning the index name, and the CONFLICTING() notation is decidedly odd. Maybe something like this: INSERT INTO targettable(key, quantity, inserted_at) VALUES(123, quantity, now()) ON DUPLICATE (key) UPDATE SET quantity= quantity + OLD.quantity, updated_at = now(); (Perhaps OLD should reference the tuple already in the table, and NEW the value from the VALUES clause. That would be snazzy indeed.) Also, how about making the SET clause optional, with the semantics that we just update all of the fields for which a value is explicitly specified: INSERT INTO overwrite_with_abandon (key, value) VALUES (42, 'meaning of life') ON DUPLICATE (key) UPDATE; While the ability to specify a SET clause there explicitly is useful, I bet a lot of key-value store users would love the heck out of a syntax that let them omit it when they want to overwrite. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Connor WolfДата:
Сообщение: Re: [GENERAL] Understanding and implementing a GiST Index