Thread: Pb importing data in v. 7.0.3
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.
"Herve Piedvache" <herve@elma.fr> 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)
Wow. I thought we'd fixed that. Is your db dump file small enough that
you could send it to me (at least the table that causes this error)?
regards, tom lane
> "Herve Piedvache" <herve@elma.fr> 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
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);
}
/*