Re: INSERT ... ON CONFLICT DO UPDATE

Hi Rafal:

On Tue, Jul 21, 2015 at 12:43 PM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
> W dniu 21.07.2015 o 09:34, Francisco Olarte pisze:
>> 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.
....
> 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.

I see your points, totally opposite opinions, so no point in
discussing it, discard my sugestions as not aplicable SVP.

....
> This looks a bit like an overkill for the above examples.

It certainly is for your style of dessign, workng target, discard it.

> But I have other thoughts on the use of cryptographic sequences here.

I wouldn't call it that, its misleading. It's just encrypted sequences.

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

Well, if you have aplication instance specific sequences, of course
you have. But in this case even plain unencrypted sequences hae them.

> So the application *will have to have* a re-issuing of an
> INSERT implemented anyway.

Of course, because the only point of using instance specific sequences
instead of serial like you normally do must be having the possibility
of collisions to justify a the existence of a re-issuing code and
exercise it.

> If so, the whole point of using cryptographic
> sequence is missing.

No. The whole point of using a global sequence ( in the db ) is
avoiding collisions, be it encrypted or plain. The whole point of
using crypto is to make it look random. If you use an application
specific cryptographic sequence is because you want colisions (
success, as told above ) which looks random ( success too ).

If you do not want colisions, use a global sequence.

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

I use to tell people there are three meaninful cardinalities in
computing, zero, one and many. And three probabilities ( NOT
possibilities ), zero, one and other==(0,1). Except in some lucky
domains you have to trat every (0,1) probability as been possible ( in
fact my three probability values map nicely to impossible, possible
and certain ).

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

What are you talking about? Where did you get that seeding idea? You
do not seed the cipher, you use the ciphered sequence as voucher. In
fact I've done this with session ids. I use a sequence for the ID and
send the user the ciphered val. When it comes back I just decipher it
and search. I did not have your 6-digit problems, so I just used 128
bit blocks, and it worked nicely. And I did not have any ciphered data
in the DB.

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

Saying OCR is a nice feature is like saying MAGIC RAINBOW OVERDRIVE is
a nice feature for a car. It does not exist, and nobody has described
it with enough detail so people can assses its usefulness or
implementation difficulty. A careful definition of the corner case
will be needed. And even with it you have the possibility of inifinite
colisions ( either due to generating too many 'vouchers' or to sheer
bad luck ( like collisions among application instances ). If you try
to write a nicely wrapped up description of the functionality maybe
someone could see the usefulness and implement it, but I think this is
possible but unlikely.

Francisco Olarte.