On 10/01/2014 04:46 PM, Simon Riggs wrote:
> 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.
Uh, if that's an option, couldn't you just use a subtransaction always,
and forget about this patch? However, a subtransaction is a lot more
expensive; you'll consume an XID for every inserted or updated row, for
starters.
> 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.
I'm not actually sure if the proposed syntax would allow that, I haven't
been paying much attention to that part.
- Heikki