SERIAL silently trunctates sequence name to 31 characters

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Ward Vandewege
Your email address : ward(at)pong(dot)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 site: postgresql-7.2-1PGDG etc

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

I have a table defined as:

create table tbl_productpriceproperties (
pkey serial,

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,

productpriceid int8 not null default 0,
propertyid int8 not null default 0,
primary key (pkey)
);

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 sequence to 31 characters. From pg_dump:

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

CREATE SEQUENCE "tbl_productpriceproper_pkey_seq" start 1 increment 1 maxvalue 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:
----------------------------------------------------------------------

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=# create table tbl_productpriceproperties (
mydb(# pkey serial,
mydb(#
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,
mydb(# status boolean default '1' not null,
mydb(#
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_productpriceproperties_pkey' for table 'tbl_productpriceproperties'
CREATE

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

mydb=# 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.

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