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

On 7/20/2015 5:39 PM, dsrich(at)dsrich(dot)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