Subject: [GENERAL] Pb importing data in v. 7.0.3 From: "Herve Piedvache" To: pgsql-general@postgresql.org Date: Wed, 3 Jan 2001 16:14:46 +0100 Organization: ELMA Status: Hi, I've got a problem when doing an import of data after a backup. bkp cmd: pg_dump mydatabase > db Then I dropped the base and recreate one. When doing a "psql -e mydatabase < db", an error occured when recreating indexes. The error is : ------------------------- 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. connection to server was lost ------------------------- It seems to be because the lower index is > than the upper ... The source code for this error is in :the file "src/backend/access/nbtree/nbsort.c I understood that this problem was existing in earlier version of Postgres, like 6.5.* Is there any solution to make my import of data working properly. Thanks a lot for any help, and sorry for my poor english -- Lifo. Subject: Re: [GENERAL] Pb importing data in v. 7.0.3 From: Tom Lane To: "Herve Piedvache" , "Jean-Christophe Boggio" , "Sebastien COUREAU" cc: pgsql-general@postgresql.org Date: Wed, 03 Jan 2001 23:25:26 -0500 Comments: In-reply-to Tom Lane message dated "Wed, 03 Jan 2001 11:52:13 -0500" Status: > "Herve Piedvache" writes: >> When doing a "psql -e mydatabase < db", an error occured when recreating >> indexes. >> The error is : >> ------------------------- >> FATAL 1: btree: failed to add item to the page in _bt_sort (2) After digging around in the failed process, I now understand what's happening. This error occurs when index building needs to insert a new index item on a disk page, and finds that there isn't enough space to insert the item, so it splits the page ... but then there *still* isn't enough space on the right-hand split page for the new index item. This can happen in pathological cases, because the choice of split point is constrained by 7.0's handling of chains of equal keys; it doesn't want to split in the middle of a chain. In the example at hand, it seems that the pre-split page contained a single key of a lower value (call it 'A') and all the remaining keys were of the same higher value (call it 'AAAA'). We split the page in such a way that the left sub-page has the 'A' and the right page has all the 'AAAA's ... which leaves us enough room on the right page to insert an 'A', but unfortunately what we want to insert is 'AAAA' and it doesn't fit. The equal-key logic has been entirely rewritten for 7.1, and I believe that it's not subject to this bug any longer. I'm very hesitant to try to jury-rig a solution for 7.0, though, because the old equal-key handling is so complex and fragile that some other, more common case might break. How do you feel about running 7.1 beta? regards, tom lane Subject: Re: [GENERAL] Pb importing data in v. 7.0.3 From: Tom Lane To: "Herve Piedvache" , "Jean-Christophe Boggio" , "Sebastien COUREAU" cc: pgsql-general@postgresql.org, pgsql-patches@postgresql.org Date: Wed, 03 Jan 2001 23:43:57 -0500 Comments: In-reply-to Tom Lane message dated "Wed, 03 Jan 2001 23:25:26 -0500" Status: I wrote: > The equal-key logic has been entirely rewritten for 7.1, and I believe > that it's not subject to this bug any longer. I'm very hesitant to try > to jury-rig a solution for 7.0, though Actually, after thinking a little more, I think that the attached patch might cure the problem in 7.0.3. Give it a try, if you like. regards, tom lane *** src/backend/access/nbtree/nbtsort.c.orig Wed Apr 12 13:14:49 2000 --- src/backend/access/nbtree/nbtsort.c Wed Jan 3 23:37:46 2001 *************** *** 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); } /*