Обсуждение: BUG #13507: INSERT into tables with SERIAL primary keys failing about half of the time
BUG #13507: INSERT into tables with SERIAL primary keys failing about half of the time
От
dsrich@dsrich.net
Дата:
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.
Re: BUG #13507: INSERT into tables with SERIAL primary keys failing about half of the time
От
John R Pierce
Дата:
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