Обсуждение: Create index problem ( _bt_sort )
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
=?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
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);
}
/*