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

On Fri, May 6, 2016 at 6:39 AM, Francisco Olarte <folarte(at)peoplecall(dot)com> wrote:
>> (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.

It is. There have been several -bugs threads on this already. e.g.,
the following two:

http://www.postgresql.org/message-id/flat/CAM3SWZQMfR6Zfe3A0Nr4ddko8xZrijAuQQ=EcGjGeJSs2piAXA(at)mail(dot)gmail(dot)com#CAM3SWZQMfR6Zfe3A0Nr4ddko8xZrijAuQQ=EcGjGeJSs2piAXA@mail.gmail.com

http://www.postgresql.org/message-id/flat/CAM3SWZS2jd1Ci2TR2bjBXqZgK3JdoFin9yGbC5TQdYdF12RBuA(at)mail(dot)gmail(dot)com#CAM3SWZS2jd1Ci2TR2bjBXqZgK3JdoFin9yGbC5TQdYdF12RBuA@mail.gmail.com

--
Peter Geoghegan