Horton
Menu

Thread

  1. Create index problem ( _bt_sort )

    Loïc TREGOUËT <loic@cri74.org> — 2001-01-29T16:20:50Z

    	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

  2. Re: Create index problem ( _bt_sort )

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-29T16:47:58Z

    =?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

  3. Re: Create index problem ( _bt_sort )

    Loïc TREGOUËT <loic@cri74.org> — 2001-01-29T16:59:04Z

    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

  4. Re: Create index problem ( _bt_sort )

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-01-29T17:20:13Z

    =?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

  5. Re: Create index problem ( _bt_sort )

    Yasuo Ohgaki <yasuo_ohgaki@hotmail.com> — 2001-02-08T00:25:22Z

    > > 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

  6. Re: Create index problem ( _bt_sort )

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-08T23:17:50Z

    "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 momjian Exp $
   *
   *-------------------------------------------------------------------------
   */
--- 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:49 tgl 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);
  	}
  
  	/*