Hello > I've just started to read through postgres-9.5 "what's new" ... before giving it > a try. The "insert ... on conflict do update" is particularly atractive to me; but I > was wondering why it does not cover the third usage scenario of action that a > programmer may need for a PK conflict during insert. > > In my experience, most often I generate a random value for PK, with that > random value becoming a unique ticket like a voucher (related to monetary > value). for that I: > > CREATE TABLE vouchers (a_voucher bigint PRIMARY KEY default > (random()*1000000000)::bigint, issued date default now(), .....); > > Naturally: > 1. A_VOUCHER range space is always significantly larger then currently issued > voucher count - so conflicts are rare. > 2. with current (as of 9.5) implementation I think I can always "ON CONFLICT > DO NOTHING", and retry the INSERT from application level. An UPSERT is "try an INSERT and if there is a conflict, do nothing or UPDATE some values of the existing record". The scenario that you suggest is not an UPSERT, because what you want to reach is to try a new INSERT, hoping that this works. What speak against using a sequence for the primary key column a_voucher? This would guarantee that you don't have a conflict. > But it would be immenensly more comfortable if one could: "INSERT ... ON > CONFLICT (a_voucher) DO RETRY"; with semantics of that statement being: > 1. prepare should check if there is a DFAULT for specified "conflict column" > (here: "a_voucher"), and fail if there isn't one. > 2. prepare shoud check if the default is a VOLATILE function... or fail. > 3. when all that pass, the prepared insert, when executed and with a conflict, > should be re-attempt with NEW call to that DEFAULT function of the > indicated CONFLICT column(s). > 3. and there should be a /ETC/POSTGRES.CONF parameter limiting the > number of retries for a single conflict - as a programmer I know, that if I need > to retry more then twice, the space is too dense, always. So I need to change > the DFAULT function, not increase the retry_count ... > thus haveing DDS allowing the change to the DFAULT FUNCTION means it's > not necesary to allow for change of the RETRY_CONT (during database > life) - and when the later is in the CONFIG, the less it's prone to typo errors of > application authors. > > Was the above considered for "ON CONFLICT" implementation before? > > If so, can someone pls point me to critics it received. > > If not: is it unreasonable? why? IMHO, as I mentioned, this is not an UPSERT use case, but maybe the implementors of the feature may have different arguments. You could implement that in a function instead of the application, if you prefer. Bye Charles -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
