Thread: ltree gist index errors and fill factor questions
Hello,
We have an ltree column (tree_path) that has a gist index (index_nodes_on_tree_path). This is in a 9.3.5 database. Recently errors started occurring in the postgres log on some updates to this table:
fixing incomplete split in index "index_nodes_on_tree_path", block 2358
STATEMENT: UPDATE "nodes" SET "parent_id" = $1, "contents_id" = $2, "contents_type" = $3, "trough_code" = $4, "live_flag" = $5, "ordering" =$6, "id" = $7, "tree_path" = $8 WHERE "nodes"."id" = 127695
ERROR: failed to add item to index page in "index_nodes_on_tree_path"
Reindexing index_nodes_on_tree_path fixes the problem temporarily, but it returns.
I'm wondering if this is a bug (googleing the incomplete split error returns results about a similar bug with b-tree indexes) or a problem with an inadequate fill factor setting. It doesnt look like there is a specified fill factor for this index and I'm not sure what the gist default is.
CREATE INDEX index_nodes_on_tree_path ON nodes USING gist (tree_path)
The table in question has about 94k rows, an example of the widest tree_path tuple is 69066.69090.69091.69094
Any advice is appreciated, happy new year!
Mike
Mike Broers <mbroers@gmail.com> writes:
> We have an ltree column (tree_path) that has a gist index
> (index_nodes_on_tree_path). This is in a 9.3.5 database. Recently errors
> started occurring in the postgres log on some updates to this table:
> fixing incomplete split in index "index_nodes_on_tree_path", block 2358
> STATEMENT: UPDATE "nodes" SET "parent_id" = $1, "contents_id" =
> $2, "contents_type" = $3, "trough_code" = $4, "live_flag" = $5, "ordering"
> =$6, "id" = $7, "tree_path" = $8 WHERE "nodes"."id" = 127695
> ERROR: failed to add item to index page in "index_nodes_on_tree_path"
> Reindexing index_nodes_on_tree_path fixes the problem temporarily, but it
> returns.
That sure sounds like a bug, but we're unlikely to be able to find it
without a lot more info. Is there any chance you could extract a
reproducible test case? Presumably this would require a starting data
set and a series of updates to apply to it to provoke the error. We'd
only need the data that goes into this index, not any of the rest of
the table.
regards, tom lane
Mike Broers <mbroers@gmail.com> writes:
> I will do my best to provide a reproducible test case. Is there any more
> information I can supply in the meantime that would help?
Well ... just to clarify, are you suffering any crashes in your database?
Because really the "fixing incomplete split" code shouldn't get entered
at all unless a previous index page split had failed partway through.
regards, tom lane