Thread: DROPping tables with SERIALs
Hi, I have run into a problem dropping and re-creating tables with type SERIAL: CREATE TABLE t ( s SERIAL); DROP TABLE t; CREATE TABLE t ( s SERIAL); gives ERROR: t_s_seq relation already exists This looks like the implicitly created sequence t_s_seq is not dropped together with the table. I am running a current (?) cvs snapshot from anoncvs@postgresql.org. Jan
>
> Hi,
>
> I have run into a problem dropping and re-creating tables with
> type SERIAL:
>
> CREATE TABLE t ( s SERIAL);
> DROP TABLE t;
> CREATE TABLE t ( s SERIAL);
>
> gives
> ERROR: t_s_seq relation already exists
>
> This looks like the implicitly created sequence t_s_seq is not dropped
> together with the table.
>
> I am running a current (?) cvs snapshot from anoncvs@postgresql.org.
>
> Jan
>
>
Yepp. The serial type is implemented as an integer with a
default of nextval('tab_attr_seq') and the sequence itself
created on the fly.
I think we should have an additional oid field in
pg_attribute that holds the oid of the created sequence and
that is examined at drop table time to drop the serials too.
TODO for v6.5 ?
Jan :-)
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Jan Wieck wrote:
>
> Yepp. The serial type is implemented as an integer with a
> default of nextval('tab_attr_seq') and the sequence itself
> created on the fly.
>
> I think we should have an additional oid field in
> pg_attribute that holds the oid of the created sequence and
> that is examined at drop table time to drop the serials too.
>
> TODO for v6.5 ?
There is another way: let's define special SERIAL type
(actually - int4) and in DeletePgAttributeTuples()
check if atttype == SERIALOID and drop sequence.
Also note that currently SERIAL doesn't work as
ppl expect -
1. SERIAL should generate value if input value is NULL or 0;
2. value generated should be max(this_field) + 1
We should add builtin trigger function for SERIAL...
Actually, having this function we can avoid
SERIALOID: we could check in RelationRemoveTriggers
if tgfoid == ThisFuncOID and drop sequence.
On the other hand SERIALOID looks cleaner.
Vadim
Vadim Mikheev <vadim@krs.ru> writes:
> Also note that currently SERIAL doesn't work as
> ppl expect -
> 1. SERIAL should generate value if input value
> is NULL or 0;
No, I think it should *only* substitute for NULL. Why assume
zero is special?
> 2. value generated should be max(this_field) + 1
That's not quite right. If current max(serial_field) is 100, and
I INSERT a tuple that gets serial 101, and then I DELETE that tuple,
should the next insertion be given serial 101 again? No. You do need
the separate sequence object as a record of the highest serial number
ever assigned, regardless of whether that value is still present in the
table.
What you really want is that if a non-null value is inserted into the
serial field, and it is larger than the current readout of the
associated sequence generator, then the sequence should be advanced to
equal that inserted value.
Another question is whether a SERIAL field should automatically be
UNIQUE (ie, create a unique index on it to prevent mistakes in manual
insertion of values for the field). I'm not sure that that should be
forced to happen, but I think that most users would want the uniqueness
constraint. Maybe this just means a documentation change, with "SERIAL
UNIQUE" being shown as the typical usage.
regards, tom lane
Tom Lane wrote: > > Vadim Mikheev <vadim@krs.ru> writes: > > Also note that currently SERIAL doesn't work as > > ppl expect - > > 1. SERIAL should generate value if input value > > is NULL or 0; > > No, I think it should *only* substitute for NULL. Why assume > zero is special? As I remember this is how SERIAL works in Informix. Compatibility is good thing... but I have no objections. Nevertheless, currently SERIAL doesn't work if input value is NULL, only is not specified in INSERT: DEFAULT is not appropriate for SERIAL in any case. > > > 2. value generated should be max(this_field) + 1 > > That's not quite right. If current max(serial_field) is 100, and > I INSERT a tuple that gets serial 101, and then I DELETE that tuple, > should the next insertion be given serial 101 again? No. You do need > the separate sequence object as a record of the highest serial number > ever assigned, regardless of whether that value is still present in the > table. > > What you really want is that if a non-null value is inserted into the > serial field, and it is larger than the current readout of the > associated sequence generator, then the sequence should be advanced to > equal that inserted value. Yes - this is what I meant... > > Another question is whether a SERIAL field should automatically be > UNIQUE (ie, create a unique index on it to prevent mistakes in manual > insertion of values for the field). I'm not sure that that should be > forced to happen, but I think that most users would want the uniqueness > constraint. Maybe this just means a documentation change, with "SERIAL > UNIQUE" being shown as the typical usage. Once again - I would like to see SERIAL compatible with SERIAL/IDENTY in other RDBMSes. Vadim
Vadim Mikheev <vadim@krs.ru> writes:
> Tom Lane wrote:
>> No, I think it should *only* substitute for NULL. Why assume
>> zero is special?
> As I remember this is how SERIAL works in Informix.
Ah. OK, if that's what they do then I agree we ought to act the same.
>> Another question is whether a SERIAL field should automatically be
>> UNIQUE (ie, create a unique index on it to prevent mistakes in manual
>> insertion of values for the field).
> Once again - I would like to see SERIAL compatible with
> SERIAL/IDENTY in other RDBMSes.
Yes, and? What do the other ones do?
regards, tom lane
Tom Lane wrote: > > >> Another question is whether a SERIAL field should automatically be > >> UNIQUE (ie, create a unique index on it to prevent mistakes in manual > >> insertion of values for the field). > > > Once again - I would like to see SERIAL compatible with > > SERIAL/IDENTY in other RDBMSes. > > Yes, and? What do the other ones do? Ok, Sybase: http://sybooks.sybase.com:80/dynaweb/group4/srg1100e/sqlug/@Generic__BookTextView/16622;pt=15743;lang=ru Each table can include a single IDENTITY column. IDENTITY columns store sequential numbers such as invoice numbers, employee numbers, or record numbers that are generated automatically by SQL Server. The value of the IDENTITY column uniquely identifies each row in a table. Informix confuses me: http://www.informix.com/answers/english/pdf_docs/gn7382/4365.pdf The SERIAL data type is not automatically a unique column. You must apply a unique index to this column to prevent duplicate serial numbers. If you use the interactive schema editor in DB-Access to define the table, a unique index is applied automatically to a SERIAL column. http://www.informix.com/answers/english/pdf_docs/gn7382/4366.pdf You can specify a nonzero value for a serial column (as long as it does not duplicate any existing value in that column), ...^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ?!!! Vadim
> The SERIAL data type is not automatically a unique column. > You must apply a unique index to this column to prevent > duplicate serial numbers. If you use the interactive schema > editor in DB-Access to define the table, a unique index is > applied automatically to a SERIAL column. > > http://www.informix.com/answers/english/pdf_docs/gn7382/4366.pdf > > You can specify a nonzero value for a serial column > (as long as it does not duplicate any existing value in that column), ... > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ > > ?!!! You can assign a value to a serial column, as long as it is unique. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> Hi, > > I have run into a problem dropping and re-creating tables with > type SERIAL: > > CREATE TABLE t ( s SERIAL); > DROP TABLE t; > CREATE TABLE t ( s SERIAL); > > gives > ERROR: t_s_seq relation already exists > > This looks like the implicitly created sequence t_s_seq is not dropped > together with the table. > > I am running a current (?) cvs snapshot from anoncvs@postgresql.org. Added to TODO:* auto-destroy sequence on SERIAL removal -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026