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

RESOLVED FIXED in Bugzilla 2.20

Status

()

Bugzilla
Creating/Changing Bugs
P1
major
RESOLVED FIXED
13 years ago
13 years ago

People

(Reporter: Max Kanat-Alexander, Assigned: Max Kanat-Alexander)

Tracking

2.19.2
Bugzilla 2.20
Dependency tree / graph
Bug Flags:
approval +

Details

(URL)

Attachments

(1 attachment)

(Assignee)

Description

13 years ago
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.
(Assignee)

Updated

13 years ago
Target Milestone: --- → Bugzilla 2.20
(Assignee)

Updated

13 years ago
Blocks: 291776
Summary: PostgreSQL can sometimes throw an "index item" error → PostgreSQL can sometimes throw an "index item size" error
(Assignee)

Comment 1

13 years ago
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.
(Assignee)

Updated

13 years ago
Summary: PostgreSQL can sometimes throw an "index item size" error → PostgreSQL dies when you insert a string longer than 2700 characters into an indexed field
(Assignee)

Comment 2

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

Updated

13 years ago
Status: NEW → ASSIGNED
(Assignee)

Updated

13 years ago
Assignee: create-and-change → mkanat
Status: ASSIGNED → NEW
(Assignee)

Updated

13 years ago
Status: NEW → ASSIGNED
Priority: -- → P1
(Assignee)

Comment 3

13 years ago
Created attachment 183041 [details] [diff] [review]
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.
Attachment #183041 - Flags: review?(bugzilla)
(Assignee)

Updated

13 years ago
Attachment #183041 - Flags: review?(bugzilla) → review?(bugreport)
(Assignee)

Comment 4

13 years ago
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.
Attachment #183041 - Flags: review?(bugreport) → review?(jouni)

Comment 5

13 years ago
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.
Attachment #183041 - Flags: review?(jouni) → review+
(Assignee)

Comment 6

13 years ago
OK, And I assert that I've tested this code on PostgreSQL, and it works.
Flags: approval?
Flags: approval? → approval+
(Assignee)

Comment 7

13 years ago
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
Status: ASSIGNED → RESOLVED
Last Resolved: 13 years ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.