Re: INSERT ... ON CONFLICT DO UPDATE

On 07/19/2015 06:47 AM, Rafal Pietrak wrote:
> Hi,
>
> W dniu 19.07.2015 o 14:10, Geoff Winkless pisze:
>> On 19 July 2015 at 11:30, Rafal Pietrak <rafal@ztk-rp.eu
>> <mailto:rafal@ztk-rp.eu>> wrote:
>>
>>      when I have to invent/introduce additional
>>      features/columns/attributes (like a key in addition to a sequence),
>>      which are not required by the design, but necessary for implementation)
>>      is a workaround (almost by definition).
>>
>>
>> I'm sorry that you feel defensive about this, and apologies for
>> repeating myself, but the fact that the random key can be duplicated
>> means it should not be used as a primary key, so using a sequence as a
>> primary key is not a workaround, it's a correction to the design.
>
> OK. I think I need to apology myself, too. I hope my defense wasn't too
> fierce.
>
> But I need to clearify one thing:
>
> Although "a random" can duplicate its previous values, "my random(s)"
> (which are created for this application purpose) cannot be duplicated
> when it's stored in the database as "live active data". I understand,
> that UNIQUE constraint is precisely the RDBMS tool to guarantee that.

 From my perspective the issue is, you are using a 'unique' key
generator that you know is not creating unique keys and then asking the
database to make it right. Sort of like making a square peg fit a round
hole by shaving the corners. It is possible but has sort of a messy feel
to it.

>
> Naturally, if I put a UNIQUE constraint on that column, or make it a PK,
> is just a matter of choice here. That shouldn't rise concern. I just use
> tools RDBMS provides for "semantics" the application needs.
>
>
>>
>> Notwithstanding that, the reason UPSERT is required is because it's
>> possible that two competing transactions can end up fighting over an
>> INSERT and the workarounds that are required are either highly complex
>> or not 100% successful (eg
>> http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/).
>>
>
> I knew that Depesz publication before.
>
> Actually it was the reason I've brought up "my usage scenario" here now.
> I'm not as competent as Depesz, so:
>
> 1. I worry, that while restarting a failed INSERT transaction at
> application level I miss something important (you people know by heart)
> and unwillingly corrupt and/or "suboptimise" my application/data. (much
> to the point Depesz described).
>
> 2. But, since the majority of the hard work of postgresql UPSERT
> implementation is already done; I wanted to check out if the usage
> scenario I point out falls into it as a "case", or is covered by it by
> some "indiomatic SQL sequence", or otherwise. From current discussion I
> gather: "its otherwise" - it isn't considered as applicable. (so I
> concluded: I'll live with manual re-attempt of failed insert)

As noted upstream, what you want is not an UPSERT. An UPSERT is based on
the premise that if you try an INSERT where the unique constraint
already exists then the INSERT is turned into an UPDATE.

To be fair:

http://www.postgresql.org/docs/9.5/static/sql-insert.html
"
  ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome -
provided there is no independent error, one of those two outcomes is
guaranteed, even under high concurrency. This feature is also known as
UPSERT"

So an UPSERT is just one feature of ON CONFLICT. The other being DO
NOTHING. Therefore I could see an argument made for adding other ON
CONFLICT clauses. How difficult/plausible that would be is above my
level of expertise.


>
> -R
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com