From: | Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> |
---|---|
To: | cwire4(at)gmail(dot)com |
Cc: | Francisco Olarte <folarte(at)peoplecall(dot)com>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #14126: INSERT ON CONFLICT DO NOTHING auto increments serial primary key when no insert happens. |
Date: | 2016-05-06 18:10:39 |
Message-ID: | 572CDE1F.2010703@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On 05/06/16 13:39, Francisco Olarte wrote:
> 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.
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2016-05-06 18:18:24 | Re: BUG #14126: INSERT ON CONFLICT DO NOTHING auto increments serial primary key when no insert happens. |
Previous Message | Francisco Olarte | 2016-05-06 13:39:40 | Re: BUG #14126: INSERT ON CONFLICT DO NOTHING auto increments serial primary key when no insert happens. |