Re: [HACKERS] MERGE SQL Statement for PG11

On 29 January 2018 at 17:18, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>
> 2018-01-29 18:08 GMT+01:00 Simon Riggs <simon(at)2ndquadrant(dot)com>:
>>
>> On 29 January 2018 at 16:23, Chapman Flack <chap(at)anastigmatix(dot)net> wrote:
>> > On 01/29/2018 11:13 AM, Simon Riggs wrote:
>> >> On 29 January 2018 at 15:44, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> >>> Uh, if we know we are going to get question on this, the patch had
>> >>> better have an explanation of when to use it. Pushing the problem to
>> >>> later doesn't seem helpful.
>> >>
>> >> What problem are you referring to?
>> >>
>> >> INSERT ON CONFLICT UPDATE does ...
>> >>
>> >> MERGE allows you to ...
>> > In my reading of Pavel and Bruce, the only 'problem' being suggested
>> > is that the patch hasn't added a bit of documentation somewhere that
>> > lays out the relationship between these two things, more or less as
>> > you just did.
>>
>> I am happy to write docs as requested.
>>
>> There are currently no docs saying when INSERT ON CONFLICT UPDATE
>> should be used other than the ref page for that command. There is no
>> mention of it in the "Data Manipulation" section of the docs.
>>
>> I've included docs for MERGE so it is mentioned in concurrency and
>> reference sections, so it follows the same model.
>>
>> Where would people like me to put these docs?
>
>
> Depends on size - small note can be placed in MERGE docs and link from
> INSERT ON CONFLICT DO.

I've put in cross-referencing comments in those two places.

v14 attached, with minor additions as requested or notes

Changes
* Add: X-ref docs
* Add: New self-referencing test case
* Add: EXPLAIN ANALYZE in the same style as INSERT .. ON CONFLICT
* Add: Allow Oids to be used in WHEN AND conditions
* Add: Prevent WHEN AND clause from writing data to db, per SQL spec

I'll set up a wiki page to track open items.

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