Thread: BUG #13507: INSERT into tables with SERIAL primary keys failing about half of the time

The following bug has been logged on the website:

Bug reference:      13507
Logged by:          David Richardson
Email address:      dsrich@dsrich.net
PostgreSQL version: 9.3.9
Operating system:   Ubuntu 14.04
Description:

Table:

CREATE TABLE payees
(
  payeenum serial NOT NULL,
  payeename character varying(127) NOT NULL,
  remarks text,
  CONSTRAINT payees_pkey PRIMARY KEY (payeenum),
  CONSTRAINT payees_payeename_key UNIQUE (payeename)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE payees
  OWNER TO dsrich;
GRANT ALL ON TABLE payees TO dsrich;
GRANT ALL ON TABLE payees TO users;


Insert query:

INSERT INTO payees (payeename, remarks) VALUES ('some string', 'some other
string')

This query (and three other versions  using DEFAULT and 'RETURNING payeenum'
clauses) fails about half the time with payees_pkey constraint violation.

There is nothing else going on in the database server, and the failure
occurs both when the query comes through Npgsql (where I first saw this) and
through pgAdmin.

The same database has another table with the same basic structure that also
has the same problem.

On 7/20/2015 5:39 PM, dsrich@dsrich.net wrote:
> Table:
>
> CREATE TABLE payees
> (
>    payeenum serial NOT NULL,
>    payeename character varying(127) NOT NULL,
>    remarks text,
>    CONSTRAINT payees_pkey PRIMARY KEY (payeenum),
>    CONSTRAINT payees_payeename_key UNIQUE (payeename)
> )
> WITH (
>    OIDS=FALSE
> );
> ALTER TABLE payees
>    OWNER TO dsrich;
> GRANT ALL ON TABLE payees TO dsrich;
> GRANT ALL ON TABLE payees TO users;
>
>
> Insert query:
>
> INSERT INTO payees (payeename, remarks) VALUES ('some string', 'some other
> string')
>
> This query (and three other versions  using DEFAULT and 'RETURNING payeenum'
> clauses) fails about half the time with payees_pkey constraint violation.
>
> There is nothing else going on in the database server, and the failure
> occurs both when the query comes through Npgsql (where I first saw this) and
> through pgAdmin.
>
> The same database has another table with the same basic structure that also
> has the same problem.
>

that basic sequence of operations sure seems to work for me,

$ psql
psql (9.3.9)
Type "help" for help.

pierce=# CREATE TABLE payees
pierce-# (
pierce(#   payeenum serial NOT NULL,
pierce(#   payeename character varying(127) NOT NULL,
pierce(#   remarks text,
pierce(#   CONSTRAINT payees_pkey PRIMARY KEY (payeenum),
pierce(#   CONSTRAINT payees_payeename_key UNIQUE (payeename)
pierce(# )
pierce-# WITH (
pierce(#   OIDS=FALSE
pierce(# );
CREATE TABLE
pierce=# INSERT INTO payees (payeename, remarks) VALUES ('some string',
'some other
pierce'# string');
INSERT 0 1
pierce=# INSERT INTO payees (payeename, remarks) VALUES ('anohter
string', 'some other
string');
INSERT 0 1
pierce=# INSERT INTO payees (payeename, remarks) VALUES ('yo dude',
'some other
string');
INSERT 0 1

but I didn't mess about with your ALTER OWNER, GRANT as I ran all that
from the same user.


when you create a column of pseudotype SERIAL, it creates a SEQUENCE.
altering the table owner and granting users access, might not leave
those users with rights to the sequence?



--
john r pierce, recycling bits in santa cruz

John R Pierce <pierce@hogranch.com> writes:
> On 7/20/2015 5:39 PM, dsrich@dsrich.net wrote:
>> This query (and three other versions  using DEFAULT and 'RETURNING payeenum'
>> clauses) fails about half the time with payees_pkey constraint violation.

> when you create a column of pseudotype SERIAL, it creates a SEQUENCE.

Yeah.  Have you checked whether the last_value of the sequence is above
the maximum value in the payeenum column?  It's not terribly hard to get
that out-of-sync, unfortunately, typically by loading data with
predetermined serial values.

The sequence is probably named payees_payeenum_seq, but psql's \d on
the payees table would show you for sure.

            regards, tom lane