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

Re: Using ENUM with huge NAMEDATALEN

From: "David Andersen" <david(at)andersen(dot)gs>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Using ENUM with huge NAMEDATALEN
Date: 2008-07-27 00:13:47
Message-ID: c6bf5b380807261713m2c413388y22814f22a8f6fd6a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi again,

I am attempting to alter pg_enum.enumlabel to Text, but I seem to run into a
strange permission problem with regards to system tables. I am not allowed
to modify them even if I am a superuser. Some output to make sure I do not
make any novice mistakes:

C:\Program Files\PostgreSQL\8.3\bin>psql -d postgres -U postgres
Welcome to psql 8.3.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

Warning: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.

postgres=# GRANT ALL privileges ON TABLE pg_enum TO postgres;
GRANT
postgres=# alter table pg_enum ALTER COLUMN enumlabel TYPE text;
ERROR:  permission denied: "pg_enum" is a system catalog
STATEMENT:  alter table pg_enum ALTER COLUMN enumlabel TYPE text;
ERROR:  permission denied: "pg_enum" is a system catalog
postgres=# \du
                               List of roles
 Role name | Superuser | Create role | Create DB | Connections | Member of
-----------+-----------+-------------+-----------+-------------+-----------
 postgres  | yes       | yes         | yes       | no limit    | {}
 tull      | yes       | yes         | yes       | no limit    | {}
(2 rows)

postgres=#



Thanks in advance for your help.

Regards,

David


On Sat, Jul 26, 2008 at 11:48 PM, David Andersen <david(at)andersen(dot)gs> wrote:

> Hi Tom,
>
> Thanks a lot for the tip! I will try this. You probably saved be a few days
> of work!
>
> Regards,
>
> David
>
>
> On Sat, Jul 26, 2008 at 11:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "David Andersen" <david(at)andersen(dot)gs> writes:
>> > One of the side tables is text with length of up to 900 characters. My
>> > question is whether I could build PostgreSQL with NAMEDATALEN  (which
>> > controls the max size of Enums) equal to 900?
>>
>> I wouldn't recommend it.  Consider changing pg_enum.enumlabel to type
>> TEXT instead.
>>
>>                        regards, tom lane
>>
>>
>>
>

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-07-27 00:36:00
Subject: Re: Using ENUM with huge NAMEDATALEN
Previous:From: David AndersenDate: 2008-07-26 21:48:50
Subject: Re: Using ENUM with huge NAMEDATALEN

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