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