[BUGS] Backend crash while indexing large strings

[pgsql-bugs]

Stephen van Egmnond ([EMAIL PROTECTED]) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Backend crash while indexing large strings

Long Description
The attached sql crashed Postgres 7.0.2 on a debian linux system (intel), using the 
packages 7.0.2-6.

The crash exhibits a wierd dynamic. Any of the following will avert a crash:

* remove the foo_key primary key and associated inserts
* remove the insert just before the index
* insert fewer large strings, e.g. 10 instead of 100. On my system, the magic number 
is 16.

Sample Code
drop table foo;
create table foo (
foo_key integer,
random  varchar(1000)
);
create sequence foo_sequence start 200;

-- create a pl/sql procedure
drop function thrash_database(integer);
create function  thrash_database(integer) RETURNS integer AS '
declare
i integer;
v_number_of_rows alias for $1;
begin
  FOR i IN 1..v_number_of_rows LOOP
insert into foo (foo_key, random) values (nextval(''foo_sequence''), 
''abcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghijabcdefghij'');
  END LOOP;
return 1;
END;
' language 'plpgsql';

-- change 100 to 10 to avert a crash.
select thrash_database(100);

-- comment out the following line to avert a crash
insert into foo values (1, '1');

create index foo_on_random on foo ( random );

No file was uploaded with this report

Re: [BUGS] Backend crash while indexing large strings

[Tom Lane]

[EMAIL PROTECTED] writes:
> The attached sql crashed Postgres 7.0.2 on a debian linux system
> (intel), using the packages 7.0.2-6.

I get

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.

Is that the same thing you see?

The script does not seem to trigger the failure in current sources,
so I suspect the equal-keys changes I made awhile back fixed it.
But I will dig into the crash and make sure.  Many thanks for providing
a self-contained test case!

regards, tom lane

Re: [ADMIN] Create index problem ( _bt_sort )

[Tom Lane]

"Yasuo Ohgaki" <[EMAIL PROTECTED]> 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.origWed 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;
Pageopage = npage;
--- 321,327 
 btisz,
 (PageGetPageSize(npage) - sizeof(PageHeaderData) - 
MAXALIGN(sizeof(BTPageOpaqueData))) /3 - sizeof(ItemIdData));
  
!   while (pgspc < btisz)
{
Buffer  obuf = nbuf;
Pageopage = 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);
}
  
/*