Re: INSERT ... ON CONFLICT DO UPDATE - Mailing list pgsql-general

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
scenariothat 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