Peter Geoghegan <p...@heroku.com> wrote: > Heikki, Andres and I are against using MERGE for this, fwiw. Tom > seemed to think so too, on previous occasions. It isn't a matter > of alternative syntaxes. I have described in detail why I think > it's a bad idea - I have linked to that about 3 times in this > thread.
Yeah, I read that, and I'm not convinced. > It paints us into a corner when we go to make this do what MERGE > is supposed to do. Do you want a feature that, when fully > generalized, plays a special visibility game based on whether or > not some exact set of conditions are met? That is a non-starter, > IMV. For other queries we use different access techniques not only based on the presence of an index, but on the state of the visibility map, degree of bloat, ordering of tuples in a heap, etc. -- so sure, I'm OK with different execution styles based on whether your join conditions match a unique index on columns that can't be NULL. > The whole idea of using an arbitrary join syntax seems great, > but I need something that works backwards from would-be unique > violations. That's the only way to preserve the UPSERT guarantees > (atomicity, definite insert or update). I absolutely don't buy that it is the *only way*. It is probably (by far) the *easiest* way, and doing so gets us a frequently- requested feature; but I think limiting the initial implementation to cases where the join conditions include equality tests on all columns of some appropriate unique index is fine, and doesn't seem to me to preclude further development of the MERGE feature for additional cases. In fact, I think having something to build on is a plus. The claims that you can't get a duplicate key error with an UPSERT are completely bogus, IMV. The *best* you can do is avoid them on the index used for matching (unless you're willing to ignore problem input rows or mangle the data in surprising ways to avoid such an error on a second unique index). With a fully functional MERGE syntax you could eventually gain the ability to write exceptions like that to the location of your choice (be it a table or WARNING messages in the log). -- 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
