#1416 Wiki: postgres7 error [index row size exceeds btree maximum]

v1.8
open
nobody
7
2004-06-24
2004-04-02
Patrice Levesque
No

Using postgresql 7.4.1, editing a Wiki page, an error
occurs when lots of text is inserted:

-------------------------------------
Warning: postgres7 error: ERROR: index row size 4200
exceeds btree maximum, 2713 in query:
update "tiki_pages" set "description"=?, "data"=?,
"comment"=?, "lastModif"=?, "version"=?, "user"=?,
"ip"=?, "page_size"=? where "pageName"=?
in /var/www/html/tiki-1.8/lib/tikidblib.php on line 125
--------------------------------------
(followed by the page contents).

The error is not triggered by anything special on the
page (I can split page in two wiki pages and everything
is ok).

Also, if I test using a simple sentence repeated over
and over, the error does not occur.

I attached a file that shows that behavior; to
reproduce the bug, paste the text into a Wiki edit-box
and save.

There might be a workaround for this (disabling indexes
on the table?); will that break anything? Or is
something else possible?

Thanks for your time,

Discussion

  • CAPSLOCK2000
    CAPSLOCK2000
    2004-04-17

    Logged In: YES
    user_id=855062

    I just encountered this bug, it's because in postgresql the Index
    is over all of the data column (exceeding the maximum of 2713
    bytes). In mysql it's limited to the first 255 bytes. I'm still
    looking for a way to make postgresql do the same.

     
  • Logged In: NO

    there is no sens to use indexes on 'text' type fields in
    postgresql, operators 'like', 'ilike', ~ and similar not use
    andy indexes.
    So this problem solution is remove all 'text' indexes.

     
  • teedog
    teedog
    2004-06-24

    • priority: 5 --> 7
     
  • teedog
    teedog
    2004-06-24

    Logged In: YES
    user_id=807810

    Has anything been done about this?

     
  • Logged In: YES
    user_id=277127

    I encountered this error when a user was trying to add a
    larger article, and found that dropping the two indexes
    "tiki_articles_body" and "tiki_articles_ft" resolved the issue.

    Now it might be that in some other database there would be
    some purpose to these indexes, but in PostgreSQL they will
    achieve nothing useful at all. They are not full-text
    indexes. If you wanted an index that returned you the row
    with an exactly matching text, an index on md5(body) would
    achieve that without exceeding the index row-length limit.

    The SQL to drop these was simply:
    drop index tiki_articles_body;
    drop index tiki_articles_ft;

    The index creation on tiki_articles in tiki-1.9-pgsql.sql
    seems fairly bogus in general:

    This looks fine, and useful:
    CREATE INDEX "tiki_articles_reads" ON "tiki_articles"("reads");

    These will give alphabetical sorting on title and heading.
    Is this useful? if these are there in some misguided
    attempt to speed full-text search on these fields, then they
    should be removed:
    CREATE INDEX "tiki_articles_title" ON "tiki_articles"("title");
    CREATE INDEX "tiki_articles_heading" ON
    "tiki_articles"("heading");

    These two achieve nothing, and should be removed until a
    workable alternative can be created:
    CREATE INDEX "tiki_articles_body" ON "tiki_articles"("body");
    CREATE INDEX "tiki_articles_ft" ON
    "tiki_articles"("title","heading","body");

     
  • Logged In: YES
    user_id=277127

    So far I have removed the following pointless indexes from
    my PostgreSQL based installation:

    tiki_articles_ft
    tiki_blog_posts_ft
    tiki_blogs_ft
    tiki_comments_ft
    tiki_directory_sites_ft
    tiki_faq_questions_ft
    tiki_faqs_ft
    tiki_files_ft
    tiki_galleries_ft
    tiki_images_ft
    tiki_pages_ft
    tiki_blog_posts_data
    tiki_comments_data
    tiki_pages_data
    tiki_articles_body

    The comment on 2004-06-10 00:16 is not strictly true -
    indexes on text fields will be used for comparison, where
    the comparison can be anchored to the start of the column,
    so "field LIKE 'abc%'" will use an index on "field", and
    similarly "lower(substring(field,0,10)) LIKE 'abc%'" will
    use an index on "lower(substring(field,0,10))" if it exists.

    The sense of the comment is correct, though: unless you are
    going to have a lot of records in a table, and you are going
    to use an anchorable part of a column to do a lookup, then
    the index is pointless.

    If these indexes are intended to be used for sorting
    purposes, then sorting on (say) the first 10 characters
    should be sufficient, so an index on
    lower(substring(field,0,10)) might achieve something.
    Unfortunately for this to be useful that same usage would
    need to be coded into the SQL statement doing the lookup,
    and I'm pretty sure various of the other TikiWiki databases
    aren't capable of indexes on functions.

    Meanwhile, the best solution for Sirius will be to remove
    these indexes completely from the pgsql.sql definitions,
    since they _will_ cause actual failures of the application
    all over the map.

    Can someone with access please take responsibility for
    changing this in the pgsql.sql before RC3 goes out!

    Thanks.