Re: MERGE SQL Statement for PG11

On 3 November 2017 at 07:46, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
> PMFJI
>
>> 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.
>
> From an end-users point of view I would prefer 3 (or 4 if that won't prevent
> this from going into 11)

Sounds reasonable approach.

> INSERT ... ON CONFLICT is great, but there are situations where the
> restrictions can get in the way and it would be nice to have an alternative
> - albeit with some (documented) drawbacks. As far as I know Oracle also
> doesn't guarantee that MERGE is safe for concurrent use - you can still wind
> up with a unique key violation.

Yes, Oracle allows some unique key violations. It's clear that we
would need to allow some also. So we clearly can't infer whether they
avoid some errors just because they allow some.

My approach will be to reduce the errors in the best way, not to try
to copy errors Oracle makes, if any. But that error avoidance can
easily be a later add-on if we prefer it that way.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services