From: Iain <iain@mst.co.jp>
To: Burr, Colin <CBurr@herald.com>
To: pgsql-sql@postgresql.org
Subject: Re: Bug: Sequence generator insert
Date: Mon, 1 Dec 2003 11:27:13 +0900
Message-ID: <020101c3b7b2$a1a913e0$7201a8c0@mst1x5r347kymb> (raw)
References: <7D8D2AE3E5A9DB4C8D78DE0F950ABB510BDC3F56@miaxch01.herald.com>
Hi Colin,
You have done a nice job of demonstrating the documented behaviour of
sequences.
Sequences are designed for speed and high concurrency. They do not guarantee
contiguous numbers and are not included in any transaction as I understand
it.
regards
Iain
----- Original Message -----
From: "Burr, Colin" <CBurr@herald.com>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, November 26, 2003 8:37 AM
Subject: [SQL] Bug: Sequence generator insert
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iain@mst.co.jp, CBurr@herald.com
Subject: Re: Bug: Sequence generator insert
In-Reply-To: <020101c3b7b2$a1a913e0$7201a8c0@mst1x5r347kymb>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox