Re: BUG #6177: Size field type TEXT - Mailing list pgsql-bugs

From Bruce Momjian
Subject Re: BUG #6177: Size field type TEXT
Date
Msg-id 201108251720.p7PHKMC23157@momjian.us
Whole thread Raw
In response to Re: BUG #6177: Size field type TEXT  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Responses Re: BUG #6177: Size field type TEXT
List pgsql-bugs
Kevin Grittner wrote:
> "Claudio Oliveira" <claudiomsi@hotmail.com> wrote:
>
> > Use version 8.4 and have no issues with the field type TEXT.
> >
> > In version 9.1rc1 is limited to 4680 characters.
> >
> > Where do I change that size?
>
> test=# create table txt (val text);
> CREATE TABLE
> test=# insert into txt values (repeat('long string', 1000000));
> INSERT 0 1
> test=# select char_length(val) from txt;
>  char_length
> -------------
>     11000000
> (1 row)
>
> What makes you think it's limited to 4680 characters?

My guess is there is an index on the column:

    test=> create table txt (val text);
    CREATE TABLE
    test=> create index i_txt on txt(val);
    CREATE INDEX
    test=> insert into txt values (repeat('long string', 1000000));
    ERROR:  index row requires 125944 bytes, maximum size is 8191

You should probably not index long columns but rather index an md5 hash
of the value.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

pgsql-bugs by date:

Previous
From: Heikki Linnakangas
Date:
Subject: Re: BUG #6170: hot standby wedging on full-WAL disk
Next
From: Dave Page
Date:
Subject: Re: BUG #6169: a non fatal error occured during cluster.... problem with environment variables