Thread

Sequence/constraint bug

Milo Hyson <milo@cyberlifeservices.com> — 2000-02-25T04:55:57Z

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name		: Milo Hyson
Your email address	: milo@cyberlifeservices.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)  	: Intel Pentium Pro

  Operating System (example: Linux 2.0.26 ELF) 	: Linux 2.2.14 ELF

  PostgreSQL version (example: PostgreSQL-6.5.3):   PostgreSQL-6.5.3

  Compiler used (example:  gcc 2.8.0)		: egcs 1.1.2


Please enter a FULL description of your problem:
------------------------------------------------

When inserting new rows into a table with a serial field, if a constraint
check fails, the sequence for the serial field is still incremented. As a
result you lose a number.



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

1) CREATE TABLE dummy (id serial, other int4 NOT NULL CHECK(other <> 0));

2) INSERT INTO dummy (other) VALUES(1);

3) INSERT INTO dummy (other) VALUES(2);

4) INSERT INTO dummy (other) VALUES(0);

5) INSERT INTO dummy (other) VALUES(3);

6) SELECT * FROM dummy;

You'll see the following:

id other
-- -----
 1     1
 2     2
 4     3