Thread: BUG #14909: nextval() bug

The following bug has been logged on the website:

Bug reference:      14909
Logged by:          Max Rys
Email address:      personal@rysmax.com
PostgreSQL version: 10.1
Operating system:   macOS 10.12.6
Description:

I run this simple queries on PostgeSQL 9.6 and 10.1:

DROP TABLE IF EXISTS "user";
CREATE TABLE "user" (
  id serial primary key not null,
  email varchar(64) not null
);
INSERT INTO "user" (id, email) VALUES ('1', 'admin@example.com');
INSERT INTO "user" (id, email) VALUES ('2', 'user@example.com');

All done without errors.



When i try to run the query bellow:

INSERT INTO "user" (email) VALUES ('test@example.com');

and got the next error in pgAdmin and from the PHP:

ERROR:  duplicate key value violates unique constraint "user_pkey"
DETAIL:  Key (id)=(1) already exists.

but when i try to run this query 2 times the query executed without errors
on 3rd time.



I think this is a bug in nextval() function.

On Wed, Nov 15, 2017 at 10:51:36AM +0000, personal@rysmax.com wrote:
> CREATE TABLE "user" (
>   id serial primary key not null,
>   email varchar(64) not null
> );
> INSERT INTO "user" (id, email) VALUES ('1', 'admin@example.com');
> INSERT INTO "user" (id, email) VALUES ('2', 'user@example.com');
> 
> All done without errors.
> 
> 
> 
> When i try to run the query bellow:
> 
> INSERT INTO "user" (email) VALUES ('test@example.com');
> 
> and got the next error in pgAdmin and from the PHP:
> 
> ERROR:  duplicate key value violates unique constraint "user_pkey"
> DETAIL:  Key (id)=(1) already exists.
> 
> but when i try to run this query 2 times the query executed without errors
> on 3rd time.
> I think this is a bug in nextval() function.


No, it's not.

when you insert providing value for id column, default (nextval) is not
called, so sequence is not updated.

So, when you finally try to insert without id, nextval is called and
returns first unused (from the point of view of sequence) value - 1.
Which already is in the table because you "forcibly" inserted it.

To avoid this problem you have to either:

1. run setval(...) after insertiing data with some forced ids
2. always use nextval/default for id column.

Best regards,

depesz