Thread: ltree gist index errors and fill factor questions

ltree gist index errors and fill factor questions

From
Mike Broers
Date:

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



Re: ltree gist index errors and fill factor questions

From
Tom Lane
Date:
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


Re: ltree gist index errors and fill factor questions

From
Mike Broers
Date:
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?

Re: ltree gist index errors and fill factor questions

From
Tom Lane
Date:
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


Re: ltree gist index errors and fill factor questions

From
Mike Broers
Date:
The database is not crashing thankfully.  We are waiting for the errors to come back to turn up logging in the hopes of creating the reproducible set.