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

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs