On 09/30/2014 05:57 AM, Kevin Grittner wrote:
> Peter Geoghegan <pg@heroku.com> wrote:
>
>> I think the fact that no MERGE implementation does what you want
>> should be convincing. It is *horrifically* complicated to make
>> what you want work, if indeed it is technically feasible at all.
>> Isn't this already complicated enough?
>
> What about the MERGE syntax I posted makes it hard to implement the
> statement validation and execution code you already have? (I'm
> asking about for the UPSERT case only, not an implementation of all
> aspects of the standard syntax.)
As I understand it, it isn't the syntax that's hard, it's the logic
behind it.
FWIW I'm pretty persuaded by the argument that:
* Other RDBMSes's MERGE implementations don't behave this way;
* MERGE is a join-based operation, it's not really the same as an upsert (though a join on a values-list is
similar-ish);
* Making MERGE work for the concurrency-safe upsert case would render it harder to then support the rest of MERGE for
theOLAP/data merging cases it's really specified for.
I also have a serious usability concern about re-purposing MERGE for
this. I think it'll be confusing to have a MERGE that's usable as a
concurrency-safe upsert and also as a non-concurrency-safe data merging
operation with slightly different options.
Borrowing from / closely following the MERGE syntax likely makes sense,
but special-casing a subset of MERGE would IMO be a regrettable
long-term decision.
> If we later expand the MERGE statement to more general cases, I
> don't see why statements of this form could not be treated as a
> special case.
Please, no.
That's basically having two different kinds of statement with subtly
different syntax differentiating them.
Upsert is full of confusing and subtle behaviour. Any implementation
needs to focus on making it easy to get right, and I don't think having
something where small syntax variations can cause you to silently trip
out of the concurrency-safe mode of operation would meet that need.
-- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services