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
