Search Postgresql Archives

Re: INSERT ... ON CONFLICT DO UPDATE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

W dniu 19.07.2015 o 14:10, Geoff Winkless pisze:
> On 19 July 2015 at 11:30, Rafal Pietrak <rafal@xxxxxxxxx
> <mailto:rafal@xxxxxxxxx>> 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.

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)

-R


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux