Re: [BUGS] Bug #581: Sequence cannot be deleted

2002-02-11 21:17] Tom Lane said:
| pgsql-bugs(at)postgresql(dot)org writes:
| > A Sequence is created automatically with the SQL command:
| > CREATE TABLE fa_ccpsholderscpt(hsc_serial SERIAL NOT NULL ,chd_serial INTEGER NOT NULL ,hsc_respcode CHAR(2) NOT NULL ,scp_code CHAR(4) NOT NULL ,imp_flag SMALLINT)
|
| Okay, let's try it ...
|
| regression=# CREATE TABLE fa_ccpsholderscpt(hsc_serial SERIAL NOT NULL ,chd_serial INTEGER NOT NULL ,hsc_respcode CHAR(2) NOT NULL ,scp_code CHAR(4) NOT NULL ,imp_flag SMALLINT);
| NOTICE: CREATE TABLE will create implicit sequence 'fa_ccpsholderscp_hsc_serial_seq' for SERIAL column 'fa_ccpsholderscpt.hsc_serial'
| NOTICE: CREATE TABLE / UNIQUE will create implicit index 'fa_ccpsholderscp_hsc_serial_key' for table 'fa_ccpsholderscpt'
| CREATE
|
| > but when I try to delete it with the following command:
| > DROP SEQUENCE fa_ccpsholderscpt_hsc_serial_seq
| > I get this error:
| > sequence "fa_ccpsholderscpt_hsc_serial_se" does not exist
|
| Not surprising, because that's not what it's called. Check the NOTICE
| again.

If the user was not doing this via psql, he'd not ever see that
NOTICE. The naming of sequences has appeared in a number of
problem reports.

ISTM it would make sense to expose the sequence naming logic via
a builtin function, such as pg_serialseq(table,column)?

DROP SEQUENCE pg_serialseq(a_long_table_name,a_long_column_name);

This would be a fairly straightforward wrapper of
makeObjectName(relname,colname,"seq") and we could easily update it
if (when!) the SERIAL type is reworked to guarantee a way to get at
a SERIAL type's underlying sequence[1]

thanks.
brent

[1] At some point in time, I'd like to rework SERIAL such that the
actual sequence name is not used directly. I've been thinking
of making an optional parameter for the SERIAL type to allow
creation of SERIAL types that feed from an previously created
SERIAL sequence. I envision
CREATE TABLE a ( id SERIAL );
CREATE TABLE b ( id SERIAL(a.id) );
In short, I'd like to see nextval() and currval() not used for
dealing with columns declared as SERIAL, but this is a thought
for a later date...

--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman