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