pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBug: Sequence generator insert
4+ messages / 3 participants
[nested] [flat]
* Bug: Sequence generator insert
@ 2003-11-25 23:37 Burr, Colin <CBurr@herald.com>
2003-11-28 21:38 ` Re: Bug: Sequence generator insert Bruno Wolff III <bruno@wolff.to>
2003-12-01 02:27 ` Re: Bug: Sequence generator insert Iain <iain@mst.co.jp>
2003-12-01 03:08 ` Re: Bug: Sequence generator insert Iain <iain@mst.co.jp>
0 siblings, 3 replies; 4+ messages in thread
From: Burr, Colin @ 2003-11-25 23:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug: Sequence generator insert
2003-11-25 23:37 Bug: Sequence generator insert Burr, Colin <CBurr@herald.com>
@ 2003-11-28 21:38 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2003-11-28 21:38 UTC (permalink / raw)
To: Burr, Colin <CBurr@herald.com>; +Cc: pgsql-sql
On Tue, Nov 25, 2003 at 18:37:41 -0500,
"Burr, Colin" <CBurr@herald.com> wrote:
>
> 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.
That isn't how sequences work. If you need a sequence of numbers without
gaps then you need to use something else. Note that the something else
will need to have a way to let only one transaction at a time update
the counter which may be a performance problem.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug: Sequence generator insert
2003-11-25 23:37 Bug: Sequence generator insert Burr, Colin <CBurr@herald.com>
@ 2003-12-01 02:27 ` Iain <iain@mst.co.jp>
2 siblings, 0 replies; 4+ messages in thread
From: Iain @ 2003-12-01 02:27 UTC (permalink / raw)
To: Burr, Colin <CBurr@herald.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bug: Sequence generator insert
2003-11-25 23:37 Bug: Sequence generator insert Burr, Colin <CBurr@herald.com>
@ 2003-12-01 03:08 ` Iain <iain@mst.co.jp>
2 siblings, 0 replies; 4+ messages in thread
From: Iain @ 2003-12-01 03:08 UTC (permalink / raw)
To: Burr, Colin <CBurr@herald.com>; pgsql-sql
I just re-read your message, and it occurred to me that for the second
insert you may have provided an explicit value for the 'id', is that right?
You may need to give more detail about the inserts. I'd be mildly surprised,
but not concerned if the insert that failed used an explicit value for the
id (ir didn't use the default) but still used (and therefore incremented)
the sequence.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-12-01 03:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-25 23:37 Bug: Sequence generator insert Burr, Colin <CBurr@herald.com>
2003-11-28 21:38 ` Bruno Wolff III <bruno@wolff.to>
2003-12-01 02:27 ` Iain <iain@mst.co.jp>
2003-12-01 03:08 ` Iain <iain@mst.co.jp>
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