On 1 October 2014 14:31, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 1 October 2014 13:43, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
>
>>> That does sound interesting, but I am concerned the semantics may cause
>>> issues.
>>>
>>> If I go to insert a row for 'UK' and find an existing row for
>>> 'Europe', do we really want to update the population of Europe to be
>>> the population of the UK, simply because the UK and Europe have an
>>> exclusion conflict?
>>
>> Clearly not, but you might want to insert the tuple to another table
>> instead, or skip it altogether. Or you might want to UPDATE Europe into
>> Continental Europe, and then insert the row for UK.
Sorry, let me explain more clearly - neither of those two use cases
matches the upsert case.
If you wish to skip an insert that fails on a uniqueness constraint,
that is already possible. Just wrap in a subtransaction and trap the
error. The only reason we need UPSERT is if we intend to update. If we
just intend to ignore, then we could add such a check to any index AM
that supports unique/exclusion constraints, but without pursuing the
full locking needed for upsert path.
I wasn't aware that you could do both an INSERT and an UPDATE at same
time using the proposed feature.
There is no feature option to refer to the conflicting row that is
already in the table. The only thing we have at present is the ability
to refer to the incoming data row using CONFLICTING(). Update triggers
allow you to refer to OLD and NEW, but with an exclusion constraint
the row values don't match, so using OLD and NEW would not be
appropriate - that isn't how an update trigger works now.
So AFAICS neither of those use cases matches.
> Not trying to catch you out, just trying to make sure we don't make
> technical decisions based upon unachievable ideas.
>
> I can't see value in having upsert work against exclusion constraint
> indexes; thus this only needs to work for btrees, or similar exact
> indexes.
-- Simon Riggs http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
