291803 - PostgreSQL dies when you insert a string longer than 2700 characters into an indexed field

Status: RESOLVED FIXED

(Bugzilla :: Creating/Changing Bugs, defect, P1)

Description

[Max Kanat-Alexander]

I was working on my MySQL-to-PostgreSQL script, and when it tried to copy
comments from the longdescs table in MySQL to the longdescs table in PostgreSQL,
I got:

DBD::Pg::st execute failed: ERROR:  btree: index item size 2732 exceeds maximum 2713

The URL says that this is a problem with indexing, but I haven't looked into it
too deeply...

It's possible that we need to somehow limit our indexes on the thetext field.

Comment 1

[Max Kanat-Alexander]

Removing the index from the "thetext" field fixes this problem.

I doubt that our really complex "LIKE" statement in Simple Search will use the
index, anyway, so we can probably just eliminate it until we have fulltext indexing.

Comment 2

[Max Kanat-Alexander]

The solution that Tomas and I have decided on is to remove any index marked as
"FULLTEXT" and just not create it in Pg.

Comment 3

[Max Kanat-Alexander]

Attachment: Remove the "thetext" index and only create it on MySQL

OK, I think this is the simplest and cleanest solution for 2.20. It's a pretty
small patch, this way.

Comment 4

[Max Kanat-Alexander]

Comment on attachment 183041 [details] [diff] [review]
Remove the "thetext" index and only create it on MySQL

Hey Jouni, could you review this? I haven't heard from Joel in a while.

Comment 5

[Jouni Heikniemi]

Comment on attachment 183041 [details] [diff] [review]
Remove the "thetext" index and only create it on MySQL

Works on MySQL, cannot test with Postgres. r=jouni, partially by inspection.

Comment 6

[Max Kanat-Alexander]

OK, And I assert that I've tested this code on PostgreSQL, and it works.

Comment 7

[Max Kanat-Alexander]

Checking in checksetup.pl;
/cvsroot/mozilla/webtools/bugzilla/checksetup.pl,v  <--  checksetup.pl
new revision: 1.403; previous revision: 1.402
done
Checking in Bugzilla/DB/Pg.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Pg.pm,v  <--  Pg.pm
new revision: 1.10; previous revision: 1.9
done