Re: Crash in gist insertion on pathological box data

On Thu, Mar 26, 2009 at 5:39 PM, Andrew Gierth
<andrew(at)tao11(dot)riddles(dot)org(dot)uk> wrote:
> A user on IRC reported a crash (backend segfault) in GiST insertion
> (in 8.3.5 but I can reproduce this in today's HEAD) that turns out
> to be due to misbehaviour of gist_box_picksplit.
>
> The nature of the problem is this: if gist_box_picksplit doesn't find
> a good disposition on the first try, then it tries to split the data
> again based on the positions of the box centers. But there's a problem
> here with floating-point rounding; it's possible for the average of N
> floating-point values to be strictly greater (or less) than all of the
> values individually, and the function then returns with, for example,
> all the entries assigned to the left node, and nothing in the right
> node. This causes gistSplit to try and split the left node again, with
> predictable results.
>

I probably have a workaround. As I understand the problem it touches
gist indexes with one box type field only. After googling picksplit
and reading some info I supposed that If another (distinctive) field
would be appended to the index (after the box field) then another
(old) picksplit functionality would be started instead of new (buggy)
one. Andrew approved my assumption on IRC. So I found all the indexes
(gist) with one box field and recreated them with extra column (bigint
PK field). Well on this moment our DB has been working for a 22 hour
without crashes and errors.

Of course not being pg-hacker I can't guaranty that my assumption is
absolutely correct and I welcome your criticism.

--
Regards,
Sergey Konoplev
--
PostgreSQL articles in english & russian
http://gray-hemp.blogspot.com/search/label/postgresql/