Re: UPSERT wiki page, and SQL MERGE syntax

On 10/9/14, 6:59 PM, Gavin Flower wrote:
> On 10/10/14 12:38, Jim Nasby wrote:
>> On 10/8/14, 5:51 PM, Peter Geoghegan wrote:
>>> On Wed, Oct 8, 2014 at 2:01 PM, Kevin Grittner<kgrittn(at)ymail(dot)com> wrote:
>>>> >Although the last go-around does suggest that there is at least one
>>>> >point of difference on the semantics. You seem to want to fire the
>>>> >BEFORE INSERT triggers before determining whether this will be an
>>>> >INSERT or an UPDATE. That seems like a bad idea to me, but if the
>>>> >consensus is that we want to do that, it does argue for your plan
>>>> >of making UPSERT a variant of the INSERT command.
>>> Well, it isn't that I'm doing it because I think that it is a great
>>> idea, with everything to recommend it. It's more like I don't see any
>>> practical alternative. We need the before row insert triggers to fire
>>> to figure out what to insert (or if we should update instead). No way
>>> around that. At the same time, those triggers are at liberty to do
>>> almost anything, and so in general we have no way of totally
>>> nullifying their effects (or side effects). Surely you see the
>>> dilemma.
>>
>> FWIW, if each row was handled in a subtransaction then an insert that turned out to be an update could be rolled back... but the performance impact of going that route might be pretty horrid. :( There's also the potential to get stuck in a loop where a BEFORE INSERT trigger turns the tuple into an UPDATE and a BEFORE UPDATE trigger turns it into an INSERT.
> Perhaps you need an UPSERT trigger?

I would think that a BEFORE UPSERT trigger would very likely want to know whether we were inserting or updating, which basically puts us back where we started.

That said, since the use case for UPSERT is different than both INSERT and UPDATE maybe it would be a good idea to have a separate trigger for them anyway.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com