On 2 November 2017 at 17:06, Robert Haas <robertmh...@gmail.com> wrote: > On Tue, Oct 31, 2017 at 5:14 PM, Simon Riggs <si...@2ndquadrant.com> wrote: >> I've proposed a SQL Standard compliant implementation that would do >> much more than be new syntax over what we already have. >> >> So these two claims aren't accurate: "radical difference" and "syntax >> sugar over a capability we have". > > I think those claims are pretty accurate.
No, because there is misunderstanding on some technical points. They key point is at the end - what do we do next? > The design of INSERT .. ON CONFLICT UPDATE and the supporting > machinery only work if there is a unique index. It provides radically > different behavior than what you get with any other DML statement, > with completely novel concurrency behavior, and there is no reasonable > way to emulate that behavior in cases where no relevant unique index > exists. Agreed > Therefore, if MERGE eventually uses INSERT .. ON CONFLICT > UPDATE when a relevant unique index exists and does something else, > such as your proposal of taking a strong lock, or Peter's proposal of > doing this in a concurrency-oblivious manner, in other cases, then > those two cases will behave very differently. The *only* behavioural difference I have proposed would be the *lack* of an ERROR in (some) concurrent cases. We have a way to avoid some concurrency errors during MERGE, while still maintaining exact SQL Standard behaviour. Peter has pointed out that we could not catch errors in certain cases; that does nothing to the cases where it will work well. It would be fallacious to imagine it is an all or nothing situation. > And if, in the meantime, MERGE can only handle the cases where there > is a unique index, I haven't proposed that MERGE would be forever limited to cases with a unique index, only that MERGE-for-unique-indexes would be the version in PG11, for good reason. > then it can only handle the cases INSERT .. ON > CONFLICT UPDATE can cover, which makes it, as far as I can see, > syntactic sugar over what we already have. SQL:2011 is greatly enhanced and this is no longer true; it was in earlier versions but is not now. MERGE allows you do DELETE, as well as conditional UPDATE and INSERT. It is very clearly much more than UPSERT. > Maybe it's not entirely - > you might be planning to make some minor functional enhancements - but > it's not clear what those are, and I feel like whatever it is could be > done with less work and more elegance by just extending the INSERT .. > ON CONFLICT UPDATE syntax. The differences are clearly apparent in the Standard and in my submitted docs. But I am interested in submitting a SQL Standard compliant feature. > And it does seem to be your intention to only handle the cases which > the INSERT .. ON CONFLICT UPDATE infrastructure can cover, because > upthread you wrote this: "I didn't say it but my intention was to just > throw an ERROR if no single unique index can be identified." I don't > think anybody's putting words into your mouth here. We're just > reading what you wrote. Happy to have written it because that covers the common use case I was hoping to deliver in PG11. Incremental development. My proposal was to implement the common case, while avoiding concurrency errors. Peter proposes that I cover both the common case and more general cases, without avoiding concurrency errors. All I have at the moment is that a few people disagree, but that doesn't help determine the next action. We seem to have a few options for PG11 1. Do nothing, we reject MERGE 2. Implement MERGE for unique index situations only, attempting to avoid errors (Simon OP) 3. Implement MERGE, but without attempting to avoid concurrent ERRORs (Peter) 4. Implement MERGE, while attempting to avoid concurrent ERRORs in cases where that is possible. Stephen, Robert, please say which option you now believe we should pick. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
