On Fri, Oct 10, 2014 at 11:05 AM, Robert Haas <robertmh...@gmail.com> wrote: > 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.
+1. It's inevitable that someone isn't going to be entirely happy with whatever we do. Let's be realistic about that, while minimizing the risk. > 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: People keep remarking that they don't like that you can (optionally) name a unique index explicitly, and I keep telling them why I've done it that way [1]. There is a trade-off here. I am willing to go another way in that trade-off, but let's have a realistic conversation about it. > 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. While I initially like that idea, now I'm not so sure about it [2]. MySQL don't allow this (with ON DUPLICATE KEY UPDATE). Their REPLACE command allows it, since it is defined as a DELETE followed by an INSERT (or something like that), but I think that in general that feature has been a disaster for them. [1] http://www.postgresql.org/message-id/CAM3SWZQpGf6_ME6D1vWqdCFadD7Nprdx2JrE=Hcf=93kxeh...@mail.gmail.com [2] http://www.postgresql.org/message-id/CAM3SWZT=vxbj7qkaidamybu40ap10udsqooqhvix3ykj7wb...@mail.gmail.com -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers