Thread: BUG #6177: Size field type TEXT

The following bug has been logged online:

Bug reference:      6177
Logged by:          Claudio Oliveira
Email address:      claudiomsi@hotmail.com
PostgreSQL version: 9.1rc1
Operating system:   Windows 7
Description:        Size field type TEXT
Details:

Hello,

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?

Thank you.

"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?

-Kevin

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. +

DQpIZWxsbywNCg0KSSdtIGRvaW5nIHRoZSB0ZXN0IGluIFBHQWRtaW4uDQoN
Ck11c3QgYmUgYSBidWcgaW4gUEdBZG1pbS4NCg0KSSdtIHNvcnJ5IEkgaGF2
ZSBub3QgdGVzdGVkIGluIHBzcWwNCg0KY3JlYXRlIHRhYmxlIHR4dCAodmFs
IHRleHQpOw0KdHh0IGluc2VydCBpbnRvIHZhbHVlcyDigIvigIsocmVwZWF0
ICgneCcsIDQ1MDApKTsNCmNoYXJfbGVuZ3RoIHNlbGVjdCAodmFsKSBmcm9t
IHR4dDsNCnR4dCBpbnNlcnQgaW50byB2YWx1ZXMg4oCL4oCLKHJlcGVhdCAo
J3gnLCA0Njg1KSk7DQpjaGFyX2xlbmd0aCBzZWxlY3QgKHZhbCkgZnJvbSB0
eHQ7DQoNCnNlbGVjdCAqLCBsZW5ndGggKHZhbCkgdmFsIGlzIG51bGwsICh2
YWwgfiAneCcpIGZyb20gdHh0Ow0KDQpUaGFuayB5b3UuDQpDbGF1ZGlvIE9s
aXZlaXJhIA0KaHR0cDovL3d3dy5tc2lzb2x1Y29lcy5jb20uYnINCg0KPiBG
cm9tOiBicnVjZUBtb21qaWFuLnVzDQo+IFN1YmplY3Q6IFJlOiBbQlVHU10g
QlVHICM2MTc3OiBTaXplIGZpZWxkIHR5cGUgVEVYVA0KPiBUbzogS2V2aW4u
R3JpdHRuZXJAd2ljb3VydHMuZ292DQo+IERhdGU6IFRodSwgMjUgQXVnIDIw
MTEgMTM6MjA6MjIgLTA0MDANCj4gQ0M6IGNsYXVkaW9tc2lAaG90bWFpbC5j
b207IHBnc3FsLWJ1Z3NAcG9zdGdyZXNxbC5vcmcNCj4gDQo+IEtldmluIEdy
aXR0bmVyIHdyb3RlOg0KPiA+ICJDbGF1ZGlvIE9saXZlaXJhIiA8Y2xhdWRp
b21zaUBob3RtYWlsLmNvbT4gd3JvdGU6DQo+ID4gIA0KPiA+ID4gVXNlIHZl
cnNpb24gOC40IGFuZCBoYXZlIG5vIGlzc3VlcyB3aXRoIHRoZSBmaWVsZCB0
eXBlIFRFWFQuDQo+ID4gPiANCj4gPiA+IEluIHZlcnNpb24gOS4xcmMxIGlz
IGxpbWl0ZWQgdG8gNDY4MCBjaGFyYWN0ZXJzLg0KPiA+ID4gDQo+ID4gPiBX
aGVyZSBkbyBJIGNoYW5nZSB0aGF0IHNpemU/DQo+ID4gIA0KPiA+IHRlc3Q9
IyBjcmVhdGUgdGFibGUgdHh0ICh2YWwgdGV4dCk7DQo+ID4gQ1JFQVRFIFRB
QkxFDQo+ID4gdGVzdD0jIGluc2VydCBpbnRvIHR4dCB2YWx1ZXMgKHJlcGVh
dCgnbG9uZyBzdHJpbmcnLCAxMDAwMDAwKSk7DQo+ID4gSU5TRVJUIDAgMQ0K
PiA+IHRlc3Q9IyBzZWxlY3QgY2hhcl9sZW5ndGgodmFsKSBmcm9tIHR4dDsN
Cj4gPiAgY2hhcl9sZW5ndGgNCj4gPiAtLS0tLS0tLS0tLS0tDQo+ID4gICAg
IDExMDAwMDAwDQo+ID4gKDEgcm93KQ0KPiA+ICANCj4gPiBXaGF0IG1ha2Vz
IHlvdSB0aGluayBpdCdzIGxpbWl0ZWQgdG8gNDY4MCBjaGFyYWN0ZXJzPw0K
PiANCj4gTXkgZ3Vlc3MgaXMgdGhlcmUgaXMgYW4gaW5kZXggb24gdGhlIGNv
bHVtbjoNCj4gDQo+IAl0ZXN0PT4gY3JlYXRlIHRhYmxlIHR4dCAodmFsIHRl
eHQpOw0KPiAJQ1JFQVRFIFRBQkxFDQo+IAl0ZXN0PT4gY3JlYXRlIGluZGV4
IGlfdHh0IG9uIHR4dCh2YWwpOw0KPiAJQ1JFQVRFIElOREVYDQo+IAl0ZXN0
PT4gaW5zZXJ0IGludG8gdHh0IHZhbHVlcyAocmVwZWF0KCdsb25nIHN0cmlu
ZycsIDEwMDAwMDApKTsNCj4gCUVSUk9SOiAgaW5kZXggcm93IHJlcXVpcmVz
IDEyNTk0NCBieXRlcywgbWF4aW11bSBzaXplIGlzIDgxOTENCj4gDQo+IFlv
dSBzaG91bGQgcHJvYmFibHkgbm90IGluZGV4IGxvbmcgY29sdW1ucyBidXQg
cmF0aGVyIGluZGV4IGFuIG1kNSBoYXNoDQo+IG9mIHRoZSB2YWx1ZS4NCj4g
DQo+IC0tIA0KPiAgIEJydWNlIE1vbWppYW4gIDxicnVjZUBtb21qaWFuLnVz
PiAgICAgICAgaHR0cDovL21vbWppYW4udXMNCj4gICBFbnRlcnByaXNlREIg
ICAgICAgICAgICAgICAgICAgICAgICAgICAgIGh0dHA6Ly9lbnRlcnByaXNl
ZGIuY29tDQo+IA0KPiAgICsgSXQncyBpbXBvc3NpYmxlIGZvciBldmVyeXRo
aW5nIHRvIGJlIHRydWUuICsNCiAJCSAJICAgCQkgIA==

Claudio Oliveira <claudiomsi@hotmail.com> wrote:

> I'm doing the test in PGAdmin.
>
> Must be a bug in PGAdmim.
>
> I'm sorry I have not tested in psql
>
> create table txt (val text);
> txt insert into values ​​(repeat ('x', 4500));
> char_length select (val) from txt;
> txt insert into values ​​(repeat ('x', 4685));
> char_length select (val) from txt;
>
> select *, length (val) val is null, (val ~ 'x') from txt;

Hmm.  Maybe you should try taking this to the pgadmin-support list.

Your script came out sort of mangled in email, and apparently has
funny characters in it because I couldn't copy/paste and modify -- I
had to retype.  But this runs fine in psql for me:  (Printing the
hundreds of x's omitted from the post, but that looks OK to me,
too.)

test=# create table txt (val text);
CREATE TABLE
test=# insert into txt values (repeat('x', 4500));
INSERT 0 1
test=# insert into txt values (repeat('x', 4685));
INSERT 0 1
test=# select char_length(val), val is null, (val ~ 'x') from txt;
 char_length | ?column? | ?column?
-------------+----------+----------
        4500 | f        | t
        4685 | f        | t
(2 rows)

-Kevin