Re: BUG #14126: INSERT ON CONFLICT DO NOTHING auto increments serial primary key when no insert happens.

On Fri, May 6, 2016 at 8:55 AM, <cwire4(at)gmail(dot)com> wrote:
> It's unclear to me if this is desired behavior or not, but when doing the
> following:
....
> (previous successful key value + number of failed inserts)
> It seems to me that intuitively the key should only increment on a
> successful insert, not just an insert statement being executed. For one,
> this unnecessarily reduces the keyspace available for the column.

I suspect this is "working as designed". The same things happen if you
do a lot of inserts and then rollback a transaction. This is because
serial use sequences, which are not mean to generate exact correlative
values, but to a mean to generate unique keys with very high
concurrency.

What sequences do is grab a chunk of values ( may be of 1 or more, it
depends ) per backend needing them and each time you ask for one they
give you a unique value and burn it. This is great for concurrency,
and it is fast. The fact they normally use correlative numbers is
normally a by-product of being the easier way of generating different
ids, but their purpose is not to do it exactly.

Think of it, if they needed to generate exact correlative amounts
anytime someone needed a number they would need to be locked until the
operation using it commits or rolls back, and informs everyone. It can
be done this way, but is much slower and normally not needed.

Francisco Olarte.