Dear Sir,
I found a sequence generator software bug associated with duplicate key
inserts that may be of interest to you.
I first created a table with a primary key based on a sequence generator.
The following script provides an example.
CREATE SEQUENCE "id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1
cache 1;
REVOKE ALL on "id_seq" from PUBLIC;
GRANT ALL on "id_seq" to "administrator";
CREATE TABLE "example" ( "id" integer DEFAULT nextval('id_seq'::text) NOT NULL, "colum_name" character
varying(15)NOT NULL, Constraint "ex_pkey" Primary Key ("id")
);
REVOKE ALL on "example" from PUBLIC;
GRANT ALL on "example" to "administrator";
When I tried to insert a record into the table where a duplicate record
already exists, predictably, the insert fails and the following error is
generated:
'ERROR: Cannot insert a duplicate key into unique index ex_pkey'
However, even though the new record failed to be inserted into the table,
the sequence generator was still updated.
The sequence generator should only be updated if the record is successfully
inserted into the table, and should not be updated if the insert fails.
Sincerely yours,
Colin Burr