SERIAL silently trunctates sequence name to 31 characters

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
                        POSTGRESQL BUG REPORT TEMPLATE
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D


Your name        :    Ward Vandewege
Your email address    : ward@pong.be


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)     : Linux 2.4.17 ELF

  PostgreSQL version (example: PostgreSQL-7.1.1):   PostgreSQL-7.2

  Compiler used (example:  gcc 2.95.2)        : ? RPM packages from PosgresSQL si=
te: postgresql-7.2-1PGDG etc


Please enter a FULL description of your problem:
------------------------------------------------

I have a table defined as:=20

create table tbl_productpriceproperties (
  pkey serial,=20

  id int8 not null,
  uid int8 default 0 not null,
  suid int8 default 0 not null,
  epoch int8 default 0 not null,
  status boolean default '1' not null,
=20=20=20=20
  productpriceid int8 not null default 0,
  propertyid int8 not null default 0,
  primary key (pkey)
);=20

select nextval('tbl_productpriceproperties_pkey_seq') gives the error:

ERROR: tbl_productpriceproperties_pkey is an index relation

The problem is that the sequence creation code truncates the name of the se=
quence to 31 characters. From pg_dump:

--
-- TOC Entry ID 50 (OID 34241)
--
-- Name: tbl_productpriceproper_pkey_seq Type: SEQUENCE Owner: postgres
--=20=20=20=20=20=20

CREATE SEQUENCE "tbl_productpriceproper_pkey_seq" start 1 increment 1 maxva=
lue 9223372036854775807 minvalue 1 cache 1;

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:=20
----------------------------------------------------------------------

Create a table with serial column such that length(tablename_colname_seq) >=
 31. Try to do a select nextval('tablename_colname_seq'). Weep.

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

1. Mention this restriction in the documentation.

2. Fix the output of the create table command:

mydb=3D# create table tbl_productpriceproperties (
mydb(#   pkey serial,
mydb(#=20=20=20
mydb(#   id int8 not null,
mydb(#   uid int8 default 0 not null,
mydb(#   suid int8 default 0 not null,
mydb(#   epoch int8 default 0 not null,=20
mydb(#   status boolean default '1' not null,
mydb(#=20=20=20
mydb(#   productpriceid int8 not null default 0,
mydb(#   propertyid int8 not null default 0,
mydb(#   primary key (pkey)
mydb(# );
NOTICE:  CREATE TABLE will create implicit sequence 'tbl_productpriceproper=
_pkey_seq' for SERIAL column 'tbl_productpriceproperties.pkey'
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'tbl_product=
priceproperties_pkey' for table 'tbl_productpriceproperties'
CREATE

The NOTICE is correct but misleading. Introduce the warning created when tr=
ying to create the sequence manually:

mydb=3D# create SEQUENCE tbl_productpriceproperties_pkey_seq;
NOTICE:  identifier "tbl_productpriceproperties_pkey_seq" will be truncated=
 to "tbl_productpriceproperties_pkey"

Even better, make it an error.

3. Remove the restricition?

Thanks people,
Ward Vandewege.

--=20
Pong.be         -(           "HTML needs a rant tag" - Alan Cox            =
)-
Virtual hosting -(                                                         =
)-
http://pong.be  -(                                                         =
)-