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. 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? -R -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
