New addition to the merge sql standard

From: Nick DeCoursin <ndecoursin(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: New addition to the merge sql standard
Date: 2023-11-16 16:35:12
Message-ID: CAAbvdHnipKTeAZsQ0Q2ytSSxPemPFDDOaMRyO-wmxL+MBN=rpw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear Postgres Administrators,

There was a great article of `merge` by Lukas Fittl here:
https://pganalyze.com/blog/5mins-postgres-15-merge-vs-insert-on-conflict

In his article, he highlights one of the severe disadvantages to merge:

The comment that he essentially made is that the downside of MERGE's
> handling of concurrency is that when you concurrently INSERT, so at the
> same time as you're executing the MERGE statement, there is another INSERT
> going on, then MERGE might not notice that. MERGE would go into its INSERT
> logic, and then it would get a unique violation.

This means that any individual row insert during the insert logic of the
merge query can cause a unique violation and tank the entire merge query.

I explained this in more detail here:
https://stackoverflow.com/questions/77479975/postgres-merge-silently-ignore-unique-constraint-violation

In my opinion, it would be better for merge to offer the functionality to
simply ignore the rows that cause unique violation exceptions instead of
tanking the whole query.

Thank you,
Nick

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2023-11-16 17:13:14 Re: New addition to the merge sql standard
Previous Message Laurenz Albe 2023-11-16 07:39:10 Re: db size difference on primary and standby