Re: 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