Re: [HACKERS] MERGE SQL Statement for PG11

On 21 March 2018 at 19:45, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:

>>> * We never actually get around to saying that MERGE is good with bulk
>>> loading, ETL, and so on. I think that we should remark on that in
>>> passing.
>>
>>
>> Suggestion?
>
> How about adding this sentence after "MERGE ... a task that would
> otherwise require multiple procedural language statements":
>
> MERGE can synchronize two tables by modifying one table based on
> differences between it and some other table.
>
> The point here is that we're primarily talking about two whole tables.
> That deserves such prominent placement, as that suggests where users
> might really find MERGE useful, but without being too prescriptive.

The information I have is that many people are expecting MERGE to work
for OLTP since that is how it is used in other databases, not solely
as an ETL command.

So we're not primarily talking about two whole tables.

> Also, instead of saying "There are a variety of differences and
> restrictions between the two statement types [MERGE and INSERT ... ON
> CONFLICT DO UPDATE] and they are not interchangeable", you could
> instead be specific, and say:
>
> MERGE is well suited to synchronizing two tables using multiple
> complex conditions. Using INSERT with ON CONFLICT DO UPDATE works well
> when requirements are simpler. Only ON CONFLICT provides an atomic
> INSERT or UPDATE outcome in READ COMMITTED mode.
>
> BTW, the docs should be clear on the fact that "INSERT ... ON
> CONFLICT" isn't a statement. INSERT is. ON CONFLICT is a clause.

I think it would be better if you wrote a separate additional doc
patch to explain all of this, perhaps in Performance Tips section or
otherwise.

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