[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [HACKERS] [BUGS] Bug #581: Sequence cannot be deleted
From:       Brent Verner <brent () rcfile ! org>
Date:       2002-02-12 20:09:28
[Download RAW message or body]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly


[prev in list] [next in list] [prev in thread] [next in thread]