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
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
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