If I'm not mistaken, the conclusions from posts in this thread are:
1. recognizing of a "RETRY" action, as a separate case of "ON CONFLICT"
transaction continuation is not generally appreciated.
2. I shouldn't expect any "hidden corruption/performance" obstacles when
simply re-attempting of an INSERT at the application level (should
constraint conflict arise).
3. there are methods (like cryptographic "random" sequence), which
guarantee no conflicts. So one should resort to that.
Regarding the last point. Usually, I implement one-time used vouchers as
rows in table like:
CREATE TABLE (voucher int not null, consumed bool, expire timestamp not
null default timestamp_pl_interval(now()::timestamp, '2
min'::interval),..., unique (voucher,consumed) );
with CONSUMED column NULLyfied when voucher is used. The entire row of
consumed voucher is purged after clearence and verification, which
happen significantly later.
Such short lived (when active) voucher is usually just 6-digit long, to
help people enter it.
I don't know much about cryptography, but would a generic encryption
function (like that indicated by Daniel) have the same "waking through
the entire range-space" behavior as the original when that range-space
is externally (by my application) truncated to those 6 digits? If not,
would it be as efficient in conflict avoidance as used with original
32-bit range-space?
Then again. Is it really a "good practice" to rely on a programmer to
peek "proper/correct encryption helper" instead of providing him/her
with a database-integrated tool for a "well defined" and not so rare
usage scenario as "random default" for UNIQUE/PK column?
So my conclusion from this thread is that as this usage scenario does
not seem to be foreseen by current implementation of ON CONFLICT
transaction, a workaround exists (like: cryptographic range-walker).
Being it a workaround, I'd vote for some direct supported of that
scenario in the future at database level.
-R
