Thread: probably a bug of data-type serial
Hi there,
recently I tried the following using my psql-client:
select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.95.2
(1 row)
create table test_serial (number serial,atext varchar (10) not null);
insert into test_serial (atext) values ('A');
insert into test_serial (atext) values ('B');
insert into test_serial (atext) values (null);
insert into test_serial (atext) values ('C');
SELECT * from test_serial ;
zahl | eintext
------+---------
1 | A
2 | B
4 | C
(3 rows)
\ds
List of relations
Name | Type | Owner
---------------------------------+----------+----------
test_serial_number_seq | sequence | postgres
drop table test_serial;
select * from test_serial ;
ERROR: Relation 'test_serial' does not exist
\ds
List of relations
Name | Type | Owner
---------------------------------+----------+----------
test_serial_number_seq | sequence | postgres
************************************************************************************************************************************
First question:
The example shows that the column number of type serial is incremented
as if the preceding insert-statement was successfull, even if it was
not.
Are the values for the column number of type serial inserted into the
corresponding
sequence before the attempt to insert into the table and are not deleted
if the
latter fails ?
But maybe this behaviour was intended to log succcessless attempts to
insert into the table.
Second question:
The SEQUENCE corresponding to a column of type serial is not beeing
droped
automatically when the table containing the serial-column is droped.
Does it make sense to store a sequence without a corresponding table ?
with best regards
Ralf Miller
--
Wir würden uns freuen, Sie vom 15. bis 19. Oktober 2001 auf der Systems
in München, Halle B6 Stand 321, begrüßen zu dürfen.
---------------------------------------------------------------------------
Ralf Miller
Topnet-AG
Telefon 0211-53087-244
On Wed, 10 Oct 2001, Ralf Miller wrote: > First question: > The example shows that the column number of type serial is incremented > as if the preceding insert-statement was successfull, even if it was > not. > Are the values for the column number of type serial inserted into the > corresponding > sequence before the attempt to insert into the table and are not deleted > if the > latter fails ? > But maybe this behaviour was intended to log succcessless attempts to > insert into the table. This is intentional. If the sequence number were to roll back, then a later transaction trying to get the next sequence number would have to wait to determine if you've rolled back before getting the number. Sequences are defined to try to give unique, increasing numbers, not actually sequential ones. > Second question: > The SEQUENCE corresponding to a column of type serial is not beeing > droped > automatically when the table containing the serial-column is droped. > Does it make sense to store a sequence without a corresponding table ? The serial sequence probably should go away, but there isn't anything that would stop you from referencing the sequence somewhere else using nextval or currval.
Ralf Miller <rm@topnet.de> writes:
> The example shows that the column number of type serial is incremented
> as if the preceding insert-statement was successfull, even if it was
> not.
This is intentional. Sequence nextval() operations do not roll back.
> The SEQUENCE corresponding to a column of type serial is not beeing
> droped
> automatically when the table containing the serial-column is droped.
This is an acknowledged problem; it'll be fixed someday.
regards, tom lane
Ralf Miller writes: > The example shows that the column number of type serial is incremented > as if the preceding insert-statement was successfull, even if it was > not. The serial type does not provide consecutive numbers, only unique ones. > The SEQUENCE corresponding to a column of type serial is not beeing > droped automatically when the table containing the serial-column is > droped. Does it make sense to store a sequence without a corresponding > table ? Possibly you want to use it for more than one table. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> First question: > The example shows that the column number of type serial is incremented > as if the preceding insert-statement was successfull, even if it was > not. > Are the values for the column number of type serial inserted into the > corresponding > sequence before the attempt to insert into the table and are not deleted > if the > latter fails ? > But maybe this behaviour was intended to log succcessless attempts to > insert into the table. For performance reasons, failed INSERT's stull use a sequence number. That way, multiple users can use the sequence without waiting to see if the transactions commit. This is intended. > Second question: > The SEQUENCE corresponding to a column of type serial is not beeing > droped > automatically when the table containing the serial-column is droped. > Does it make sense to store a sequence without a corresponding table ? For SERIAL, it should drop the sequence but currently doesn't. As for using sequences, you can use a sequence for multiple tables so automatic dropping is probalby not what we want, _except_ for SERIAL. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026