Thread: Backend crash while indexing large strings

Stephen van Egmnond (svanegmond@home.com) 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

pgsql-bugs@postgresql.org 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

Tom Lane (tgl@sss.pgh.pa.us) wrote:
> 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?

Yes, sorry for omitting the crash.

> 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!

Great!  Excellent.  Looking forward to 7.1!

Having just spent last night (midnight to 4 am) installing Oracle, let
me say that I'm going to make my positive best efforts to work with
pgsql.

I was considering using the ACS (http://www.arsdigita.com/ ),
which requries Oracle, but after last night's nightmare, I'm hereby
pledging to work with OpenACS (http://www.openacs.org/ ) which is
a port to Postgres.

/Steve