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

From: cwire4(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14126: INSERT ON CONFLICT DO NOTHING auto increments serial primary key when no insert happens.
Date: 2016-05-06 06:55:28
Message-ID: 20160506065528.2693.64808@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14126
Logged by: Casey Wireman
Email address: cwire4(at)gmail(dot)com
PostgreSQL version: 9.5.2
Operating system: Windows 7
Description:

It's unclear to me if this is desired behavior or not, but when doing the
following:

insert into table(column) values (value) on conflict DO NOTHING

the first time a new entry is inserted it works as expected and
autoincrements the serial primary key as normal, however if I try to insert
this again the on conflict correctly detects that the value already exists,
but internally seems to autoincrement the serial key as many times as you
try to do the failed insert since on the next insert of a new value the key
value is

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next 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.
Previous Message Piotr Stefaniak 2016-05-06 05:38:09 Re: [BUGS] Breakage with VACUUM ANALYSE + partitions