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

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

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?

In Informix you need to create the unique index explicitly. I like this
because it keeps
things flexible. The unique constraint could be on a compount key.

Andreas

Zeugswetter Andreas IZ5 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?

In Informix you need to create the unique index explicitly. I like this
because it keeps
things flexible. The unique constraint could be on a compount key.

Agreed.

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

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.

Only an explicitly created unique index will enforce behavior this in
Informix.
(I tested this just to be sure)

Andreas

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.

I hope that this wasn't to say that the SERIAL should substitute for 0
as well as NULL. That would be quite annoying. I say stick to NULL.
If all the other DB's in the world want to shoot themselves in the foot
I don't think we should follow their example.

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.

I hope that this wasn't to say that the SERIAL should substitute

for 0

as well as NULL. That would be quite annoying. I say stick to

NULL.

Yes, 0 is an allowed value for a serial, that should not be
substituted.
I like the behavior that a NULL is substituted.
Informix is a little dull here. It forces a not null constraint, and
will therefore
raise an error if a null is inserted. I don't see any advantage in
this behavior.
(You are forced to skip the field in the insert statement to get a
generated serial)

Andreas

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