On Mon, Oct 30, 2017 at 6:21 AM, Simon Riggs <si...@2ndquadrant.com> wrote: >> That's not really true. Nobody's going to be happy if MERGE has one >> behavior in one set of cases and an astonishingly different behavior >> in another set of cases. If you adopt a behavior for certain cases >> that can't be extended to other cases, then you're blocking a >> general-purpose MERGE. > > If a general purpose solution exists, please explain what it is.
For the umpteenth time, a general purpose solution is one that more or less works like an UPDATE FROM, with an outer join, whose ModifyTable node is capable of insert, update, or delete (and accepts quals for MATCHED and NOT matched cases, etc). You could still get duplicate violations due to concurrent activity in READ COMMITTED mode, but not at higher isolation levels thanks to Thomas Munro's work there. In this world, ON CONFLICT and MERGE are fairly distinct things. What's wrong with that? You haven't actually told us why you don't like that. > The problem is that nobody has ever done so, so its not like we are > discussing the difference between bad solution X and good solution Y, > we are comparing reasonable solution X with non-existent solution Y. Nobody knows what your proposal would be like when time came to remove the restrictions that you suggest could be removed later. You're the one with the information here. We need to know what those semantics would be up-front, since you're effectively committing us down that path. You keep making vague appeals to pragmatism, but, in all sincerity, I don't understand where you're coming from at all. I strongly believe that generalizing from ON CONFLICT doesn't even make the implementation easier in the short term. ISTM that you're making this difficult for yourself for reasons that are known only to you. >> And, indeed, it seems that you're proposing an implementation that >> adds no new functionality, just syntax compatibility. Do we really >> want or need two syntaxes for the same thing in core? I kinda think >> Peter might have the right idea here. Under his proposal, we'd be >> getting something that is, in a way, new. > > Partitioning looked like "just new syntax", but it has been a useful > new feature. False equivalency. Nobody, including you, ever argued that that work risked painting us into a corner. (IIRC you said something like the progress was too small to justify putting into a single release.) > MERGE provides new capabilities that we do not have and is much more > powerful than INSERT/UPDATE, in a syntax that follow what other > databases use today. Just like partitioning. But you haven't told us *how* it is more powerful. Again, the semantics of a MERGE that is a generalization of ON CONFLICT are not at all obvious, and seem like they might be very surprising and risky. There is no question that it's your job to (at a minimum) define those semantics ahead of time, since you're going to commit us to them in the long term if you continue down this path. It is most emphatically *not* just a "small matter of programming". -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers