Re: INSERT ... ON CONFLICT DO UPDATE

Hi Rafal:

On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
> 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.

In this case I think you are mixing vouchers with voucher-numbers. IMO
you could get a better dessign by using an auxiliary table and not
nullifying the number after been consumed. Having only 6 digits I
tould try:

1.- Add a serial PK column to voucher table if needed to link it with
the rest of the system.
2.- Create an index on voucher where consumed is true.
3.- Add another table, voucher_nums, with columns voucher, order,
used. Populate it with the 10^6 vouchers and a random order value.
Also, this lets you switch to alphanumeric vouchers, or zap the ones
with two consecutive equal digits, or whatever.
4.- Make a function to select a free voucher, you can do 'select from
voucher_nums where not used order by order limit 1¡', if yout put this
into a with clause of an update-returning setting used to true to you
get a one shot way of getting a free voucher. If you add a partial
index on order where not used, you get a fast way of getting it.
5.- Make another function to free a voucher num, which sets consumed
to true on vouchers, used to false and order to a random number on
voucher_nums.

This way you keep the old voucher numbers, and you get no collisions.
If you run for some years, you can see which vouchers have been used,
so you can debug potential problems.

Francisco Olarte.