Create index problem ( _bt_sort )

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

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

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

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

"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);
  	}

/*