Skip site navigation (1) Skip section navigation (2)

SERIAL silently trunctates sequence name to 31 characters

From: Ward Vandewege <ward(at)pong(dot)be>
To: pgsql-bugs(at)postgresql(dot)org
Subject: SERIAL silently trunctates sequence name to 31 characters
Date: 2002-03-13 16:47:22
Message-ID: 20020313114722.B21783@countzero.vandewege.net (view raw or flat)
Thread:
Lists: pgsql-bugs
============================================================================
                        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  -(                                                         )-

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-03-13 19:39:37
Subject: Re: Bug #613: Sequence values fall back to previously checkpointed
Previous:From: Ben GrimmDate: 2002-03-13 16:28:11
Subject: Re: Bug #613: Sequence values fall back to previously checkpointed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group