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

Fri, 27 Nov 1998 16:20:56 +0100
Jan Iven <j.iven@rz.uni-sb.de>
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
>
>

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



Fri, 27 Nov 1998 17:22:52 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] DROPping tables with SERIALs
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

Sat, 28 Nov 1998 01:39:09 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] DROPping tables with SERIALs
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

Sat, 28 Nov 1998 15:30:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] DROPping tables with SERIALs 
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

Sun, 29 Nov 1998 15:24:37 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] DROPping tables with SERIALs
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

Sun, 29 Nov 1998 12:07:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] DROPping tables with SERIALs 
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

Mon, 30 Nov 1998 10:40:43 +0700
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] DROPping tables with SERIALs
> 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, Pennsylvania 19026

Tue, 1 Dec 1998 20:21:35 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] 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.

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, Pennsylvania 19026

Sat, 12 Dec 1998 22:49:23 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] DROPping tables with SERIALs