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

2002-02-11 21:17] Tom Lane said:
| pgsql-bugs@postgresql.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
beenthinking   of making an optional parameter for the SERIAL type to allow   creation of SERIAL types that feed from
anpreviously 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