Thread: DROPping tables with SERIALs

DROPping tables with SERIALs

From
Jan Iven
Date:
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


Re: [HACKERS] DROPping tables with SERIALs

From
jwieck@debis.com (Jan Wieck)
Date:
>
> 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) #

Re: [HACKERS] DROPping tables with SERIALs

From
Vadim Mikheev
Date:
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


Re: [HACKERS] DROPping tables with SERIALs

From
Tom Lane
Date:
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


Re: [HACKERS] DROPping tables with SERIALs

From
Vadim Mikheev
Date:
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


Re: [HACKERS] DROPping tables with SERIALs

From
Tom Lane
Date:
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


Re: [HACKERS] DROPping tables with SERIALs

From
Vadim Mikheev
Date:
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


Re: [HACKERS] DROPping tables with SERIALs

From
Bruce Momjian
Date:
> 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
 


Re: [HACKERS] DROPping tables with SERIALs

From
Bruce Momjian
Date:
> 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