BAZIN Nicolas (nbazin@ingenico.com.au) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Sequence cannot be deleted

Long Description
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)

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

I work with version 7.1.3 on Openserver 5.0.5, gcc 2.95.3 and send the SQL commands through the JDBC driver.


Sample Code


No file was uploaded with this report


Mon, 11 Feb 2002 20:26:37 -0500 (EST)
pgsql-bugs@postgresql.org
Bug #581:  Sequence cannot be deleted

On Mon, 11 Feb 2002 pgsql-bugs@postgresql.org wrote:

> BAZIN Nicolas (nbazin@ingenico.com.au) reports a bug with a severity of 1
> The lower the number the more severe it is.
>
> Short Description
> Sequence cannot be deleted
>
> Long Description 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)
>
> but when I try to delete it with the following command:
> DROP SEQUENCE fa_ccpsholderscpt_hsc_serial_seq

That's not the name of the sequence in question unless you've upped
the number of characters in an identifier.  The sequence appears to
on my machine be named "fa_ccpsholderscp_hsc_serial_seq" because the
name would have ended up being too long.


Mon, 11 Feb 2002 18:14:31 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Bug #581:  Sequence cannot be deleted
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.

			regards, tom lane

Mon, 11 Feb 2002 21:17:19 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 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 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

Tue, 12 Feb 2002 15:09:28 -0500
Brent Verner <brent@rcfile.org>
Re: [BUGS] Bug #581: Sequence cannot be deleted
Brent Verner <brent@rcfile.org> writes:
> ISTM it would make sense to expose the sequence naming logic via
> a builtin function, such as pg_serialseq(table,column)?

That might seem cleaner, but I think there's a hidden gotcha: it nails
down a presumption that the sequence name is a function of the table
name, column name, and nothing else.  So I think it'd actually make it
harder rather than easier for us to make the sorts of changes we might
want to make in future.  (F'r instance, we might add an OID into the
name to prevent collisions.)

I believe that the surprising-name problem will largely go away anyway
as soon as we get around to increasing the default NAMEDATALEN.  With
a decent name length no one would ever see truncation in practice.

Also, of course, what we really want is for SERIAL sequences to get
dropped by themselves when the parent table is dropped, and then users
don't need to know what the generated sequence name is ...

			regards, tom lane

Tue, 12 Feb 2002 15:48:09 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [BUGS] Bug #581: Sequence cannot be deleted 
On Tuesday 12 February 2002 21:48, Tom Lane wrote:
> I believe that the surprising-name problem will largely go away anyway
> as soon as we get around to increasing the default NAMEDATALEN.  With
> a decent name length no one would ever see truncation in practice.

Sorry to butt in here, but I would second this suggestion. One of my databases
has rather long-winded table and field names (mostly in German, which doesn't
help much ;-). There aren't any which exceed 31 characters on their own, but
sequences can get scarily long, so I always build with NAMEDATALEN set to 128,
just to be on the safe side.

Is there any reason for the default value (31 characters?), or are there
any performance issues associated with longer values?

> Also, of course, what we really want is for SERIAL sequences to get
> dropped by themselves when the parent table is dropped, and then users
> don't need to know what the generated sequence name is ...

This would be nice.

Yours

Ian Barwick

Tue, 12 Feb 2002 22:58:18 +0100
Ian Barwick <barwick@gmx.net>
Re: [BUGS] Bug #581: Sequence cannot be deleted
Ian Barwick <barwick@gmx.net> writes:
> Is there any reason for the default value (31 characters?),

It's historical AFAIK.

> or are there
> any performance issues associated with longer values?

Larger values would definitely waste space in the system tables (since
type name is fixed-width).  Bigger system tables = more I/O = some
amount of slowdown.  I have not heard that anyone has tried to measure
the cost.  It might be negligible; we just don't know.

I believe we'd be happy to change the number as soon as someone does the
legwork to quantify what it's going to cost.

			regards, tom lane

Tue, 12 Feb 2002 17:17:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [BUGS] Bug #581: Sequence cannot be deleted 
I have a similar problem.  Where I have both long table names and long
column names.  Has the increase of the NAMEDATALEN been targeted for a
release?  From my perspective,  I would prefer if the algorithm to determine
the sequence name was more of a function on the table name rather than a
combination of the table name and column name, as I have never created a
table that has more than one sequence in it.

I would also welcome the ability to have the drop table command drop the
sequence as well.

Tom

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Ian Barwick
Sent: February 12, 2002 4:58 PM
To: Tom Lane; pgsql-hackers@postgresql.org
Cc: nbazin@ingenico.com.au; Brent Verner
Subject: Re: [HACKERS] [BUGS] Bug #581: Sequence cannot be deleted

On Tuesday 12 February 2002 21:48, Tom Lane wrote:
> I believe that the surprising-name problem will largely go away anyway
> as soon as we get around to increasing the default NAMEDATALEN.  With
> a decent name length no one would ever see truncation in practice.

Sorry to butt in here, but I would second this suggestion. One of my
databases
has rather long-winded table and field names (mostly in German, which
doesn't
help much ;-). There aren't any which exceed 31 characters on their own, but
sequences can get scarily long, so I always build with NAMEDATALEN set to
128,
just to be on the safe side.

Is there any reason for the default value (31 characters?), or are there
any performance issues associated with longer values?

> Also, of course, what we really want is for SERIAL sequences to get
> dropped by themselves when the parent table is dropped, and then users
> don't need to know what the generated sequence name is ...

This would be nice.

Yours

Ian Barwick

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Tue, 12 Feb 2002 17:25:33 -0500
"Tom Innes" <tinnes@inforamp.net>
Re: [BUGS] Bug #581: Sequence cannot be deleted