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