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

Franscisco,

W dniu 21.07.2015 o 09:34, Francisco Olarte pisze:
> Hi Rafal:
>
> On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak <rafal@ztk-rp.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

Hmmm. I don't think so.

1. I'm not nullifying the number, just the CONSUMED flag. The row stays
otherwise pretty much untouched untill clearing time, when it's removed
from the table.

2. And I don't thing I mix vouchers with voucher-numbers.... since there
is no distinction.

Bringing some real live examples of "vouchers" to back that later
statement, we have:

1) a 6-digit authorization code (a voucher) used by payment system to
confirm payment authorization.

2) 4-8digit one-time PIN delivered by SMS used to open "some accounts".

3) 6-digit SMS confirmation code used by internet banking.

4) 14-digit voucher used to topup mobile pre-paied accounts.

5) 4-8 digit vouchers used as lunch tickets at conferences. (this could
possibly used as printed qr-code of UUID, since cafeterias usually have
bar-code readers; but having it as "human-size" 6-digit pin has it's
benefits too).

In all those cases "the physical problem" needs just a single N-digit
number (a voucher), which is as short as it's lifespan/population allows
for while keeping it relatively safe.

The application just needs to create a unique (for a period of time)
number, and "consume" it at certain point. Everything else would be
"implementation burden", which should be kept to minimum.

> 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 looks a bit like an overkill for the above examples.

But I have other thoughts on the use of cryptographic sequences here. It
has the pitfall of being sensitive to out-of-the-sequence poisoning, I
mean: When another instance of an application starts issuing another
sequence of vouchers, at certain point those sequences collide and
applications despite using "guaranteed  lack of collisions" will have a
collision. So the application *will have to have* a re-issuing of an
INSERT implemented anyway. If so, the whole point of using cryptographic
sequence is missing. So, even though this collision is not statistically
significant, but just its possibility results in that application have
to take care of re-issuing of an INSERT.

Using database.sequence() function to seed the cypher is not secure enough.

On the other hand, the "ON CONFLICT RETRY" has a nice feature for an
application programmer (like myself) that it leaves us free of the
implementation of the re-issue of an INSERT. One database-schema
designer does that for all of us.

But knowing if that usage scenario is too rare to match the heavy
lifting the implementation required, is beyond my experience.

-R