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

On 05/06/16 13:39, Francisco Olarte wrote:
> On Fri, May 6, 2016 at 8:55 AM,  <cwire4@gmail.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.

A bit more explanation: default value for serial columns are gotten from
a sequence not at saving tuple in a storage, but _before_ tuple tries to
pass triggers, checks (constraints, PKs, uniqueness, not nulls etc.) and
inserts it to a table.

And, as Francisco mentioned, sequence being increased doesn't reverts if
transaction rolls back or insert does nothing. That's why its value
monotonically increased even if number of real insertions is not changed.

--
Best regards,
Vitaly Burovoy