Re: MERGE SQL Statement for PG11

On Fri, Oct 27, 2017 at 9:00 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Fri, Oct 27, 2017 at 4:41 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> I didn't say it but my intention was to just throw an ERROR if no
>> single unique index can be identified.
>>
>> It could be possible to still run MERGE in that situaton but we would
>> need to take a full table lock at ShareRowExclusive. It's quite likely
>> that such statements would throw duplicate update errors, so I
>> wouldn't be aiming to do anything with that for PG11.
>
> Like Peter, I think taking such a strong lock for a DML statement
> doesn't sound like a very desirable way forward.  It means, for
> example, that you can only have one MERGE in progress on a table at
> the same time, which is quite limiting.  It could easily be the case
> that you have multiple MERGE statements running at once but they touch
> disjoint groups of rows and therefore everything works.  I think the
> code should be able to cope with concurrent changes, if nothing else
> by throwing an ERROR, and then if the user wants to ensure that
> doesn't happen by taking ShareRowExclusiveLock they can do that via an
> explicit LOCK TABLE statement -- or else they can prevent concurrency
> by any other means they see fit.

+1, I would suspect users to run this query in parallel of the same
table for multiple data sets.

Peter has taken some time to explain me a bit his arguments today, and
I agree that it does not sound much appealing to have constraint
limitations for MERGE. Particularly using the existing ON CONFLICT
structure gets the feeling of having twice a grammar for what's
basically the same feature, with pretty much the same restrictions.

By the way, this page sums up nicely the situation about many
implementations of UPSERT taken for all systems:
https://en.wikipedia.org/wiki/Merge_(SQL)
-- 
Michael