Thread: Create index problem ( _bt_sort )

Create index problem ( _bt_sort )

From
Loïc TREGOUËT
Date:
    Hello,

When i try to create a index (btree on text field) after a restore i've
the
following error :
FATAL 1:  btree: failed to add item to the page in _bt_sort (2)

But , if i had some rows on this table , the index creation works fine !

The table contains 1 million rows.
I've tried a lot of time , on postgresql 7.0.2 and 6.5.3

    Any idea , thanks


raslog=# select count(username) from log2000 ;
 count
--------
 965047
(1 row)

raslog=# create index user2000 on log2000(username);
FATAL 1:  btree: failed to add item to the page in _bt_sort (2)
pqReadData() -- backend closed the channel unexpectedly.
        This probably means the backend terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
raslog=# insert into log2000 select * from log2001 ;
INSERT 0 111729
raslog=# create index user2000 on log2000(username);
CREATE
raslog=#

--
Loïc  TREGOUËT

Re: Create index problem ( _bt_sort )

From
Tom Lane
Date:
=?iso-8859-1?Q?Lo=EFc=20TREGOU=CBT?= <loic@cri74.org> writes:
> raslog=# create index user2000 on log2000(username);
> FATAL 1:  btree: failed to add item to the page in _bt_sort (2)

How long are the usernames?  IIRC, 7.0.* has some edge cases that might
fail when items approach the maximum allowed size of 1/3 page (~ 2700
bytes).

Would you be willing to try the example in 7.1beta4?

            regards, tom lane

Re: Create index problem ( _bt_sort )

From
Loïc TREGOUËT
Date:
Tom Lane wrote:
>
> =?iso-8859-1?Q?Lo=EFc=20TREGOU=CBT?= <loic@cri74.org> writes:
> > raslog=# create index user2000 on log2000(username);
> > FATAL 1:  btree: failed to add item to the page in _bt_sort (2)
>
> How long are the usernames?  IIRC, 7.0.* has some edge cases that might
> fail when items approach the maximum allowed size of 1/3 page (~ 2700
> bytes).
>
> Would you be willing to try the example in 7.1beta4?
>
>                         regards, tom lane

The maximum length , is about 40 character (but not 2700 bytes).
What do you think about this :
when i have add some rows , the index creation become possible . It's
strange , isn't it ? I don't remove any row .
I work on a debien and i like the system package so, for the 7.1beta4,
i will try if i'm forced .

    Thanks , soory for my english
--
Loïc  TREGOUËT

Re: Create index problem ( _bt_sort )

From
Tom Lane
Date:
=?iso-8859-1?Q?Lo=EFc=20TREGOU=CBT?= <loic@cri74.org> writes:
> Tom Lane wrote:
>> =?iso-8859-1?Q?Lo=EFc=20TREGOU=CBT?= <loic@cri74.org> writes:
> raslog=# create index user2000 on log2000(username);
> FATAL 1:  btree: failed to add item to the page in _bt_sort (2)
>>
>> How long are the usernames?  IIRC, 7.0.* has some edge cases that might
>> fail when items approach the maximum allowed size of 1/3 page (~ 2700
>> bytes).

> The maximum length , is about 40 character (but not 2700 bytes).

Hmm, then it's not what I thought.  Would you be willing to send me
a pg_dump of that table?  (Don't send it to the whole list!)

            regards, tom lane

Re: Create index problem ( _bt_sort )

From
"Yasuo Ohgaki"
Date:
> > raslog=# create index user2000 on log2000(username);
> > FATAL 1:  btree: failed to add item to the page in _bt_sort (2)
> >>
> >> How long are the usernames?  IIRC, 7.0.* has some edge cases that might
> >> fail when items approach the maximum allowed size of 1/3 page (~ 2700
> >> bytes).
>
> > The maximum length , is about 40 character (but not 2700 bytes).
>
> Hmm, then it's not what I thought.  Would you be willing to send me
> a pg_dump of that table?  (Don't send it to the whole list!)

I've got similar error.
=====
CREATE  INDEX "abcid_idx" on "abc" using btree ( "abcid" );
FATAL 1:  btree: failed to add item to the page in _bt_sort (2)
pqReadData() -- backend closed the channel unexpectedly.
 This probably means the backend terminated abnormally
 before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
=====
I couldn't reproduece this error with the same procedure.

(First of all, I did this for testing performance, so the indexed col has
the same text data and 500,000 rows. After Drop/Create table the same
table with the same data, PostgreSQL stops complainning.
My PostgreSQL has 32KB page size, and enabled multi-byte char code support.)

My locale is
======
LANG=ja_JP.eucJP
LC_CTYPE="ja_JP.eucJP"
LC_NUMERIC="ja_JP.eucJP"
LC_TIME="ja_JP.eucJP"
LC_COLLATE="ja_JP.eucJP"
LC_MONETARY="ja_JP.eucJP"
LC_MESSAGES="ja_JP.eucJP"
LC_PAPER="ja_JP.eucJP"
LC_NAME="ja_JP.eucJP"
LC_ADDRESS="ja_JP.eucJP"
LC_TELEPHONE="ja_JP.eucJP"
LC_MEASUREMENT="ja_JP.eucJP"
LC_IDENTIFICATION="ja_JP.eucJP"
LC_ALL=
=====

I guess there are something wrong in _bt_sort(2) or locale?

--
Yasuo Ohgaki


Re: Create index problem ( _bt_sort )

From
Tom Lane
Date:
"Yasuo Ohgaki" <yasuo_ohgaki@hotmail.com> writes:
> raslog=# create index user2000 on log2000(username);
> FATAL 1:  btree: failed to add item to the page in _bt_sort (2)

Stephen van Egmnond was kind enough to submit a self-contained example,
from which it turns out that this bug is one that was found and fixed
about a month ago (sheesh, my memory is going).  Attached is a patch
that fixes it in 7.0.*.

            regards, tom lane


*** src/backend/access/nbtree/nbtsort.c.orig    Wed Apr 12 13:14:49 2000
--- src/backend/access/nbtree/nbtsort.c    Thu Jan  4 16:51:49 2001
***************
*** 28,34 ****
   * Portions Copyright (c) 1994, Regents of the University of California
   *
   * IDENTIFICATION
!  *      $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/access/nbtree/nbtsort.c,v 1.52 2000/04/12 17:14:49
momjianExp $ 
   *
   *-------------------------------------------------------------------------
   */
--- 28,34 ----
   * Portions Copyright (c) 1994, Regents of the University of California
   *
   * IDENTIFICATION
!  *      $Header: /home/projects/pgsql/cvsroot/pgsql/src/backend/access/nbtree/nbtsort.c,v 1.52.2.1 2001/01/04
21:51:49tgl Exp $ 
   *
   *-------------------------------------------------------------------------
   */
***************
*** 321,327 ****
               btisz,
               (PageGetPageSize(npage) - sizeof(PageHeaderData) - MAXALIGN(sizeof(BTPageOpaqueData))) /3 -
sizeof(ItemIdData));

!     if (pgspc < btisz)
      {
          Buffer        obuf = nbuf;
          Page        opage = npage;
--- 321,327 ----
               btisz,
               (PageGetPageSize(npage) - sizeof(PageHeaderData) - MAXALIGN(sizeof(BTPageOpaqueData))) /3 -
sizeof(ItemIdData));

!     while (pgspc < btisz)
      {
          Buffer        obuf = nbuf;
          Page        opage = npage;
***************
*** 436,441 ****
--- 436,448 ----
           * we aren't locking).
           */
          _bt_wrtbuf(index, obuf);
+
+         /*
+          * Recompute pgspc and loop back to check free space again.  If
+          * we were forced to split at a bad split point, we might need
+          * to split again.
+          */
+         pgspc = PageGetFreeSpace(npage);
      }

      /*