Backend dies creating plpgsql procedures (with reproducible example!)

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Wayne Piekarski / Matt Altus
Your email address : wayne(at)senet(dot)com(dot)au

Category : runtime: back-end
Severity : critical

Summary: Backend dies creating plpgsql procedures (with reproducible example!)

System Configuration
--------------------
Operating System : FreeBSD 2.2.7 A.OUT

PostgreSQL version : 6.5 and 6.4.2

Compiler used : gcc 2.7.2.1

Hardware:
---------
Pentium-II 450, 384 mb RAM, Big disk

Versions of other tools:
------------------------
All GNU tools

--------------------------------------------------------------------------

Problem Description:
--------------------

In a previous email I replied to someone else where they were saying that
the backend would die inserting functions or something along those lines.
(I can't really remember exactly what it was). I've seen this a few times
but was never able to create a test case which would reproduce this. Well,
the other day I did a pg_dump of our 6.4.2 database and tried to load it
back into 6.5 - it failed with the error message:

FATAL 1: btree: failed to add item to the page

Also, I've seen other errors such as "all my bits have fallen off the earth" or
something along those lines.

This is pretty freaky stuff as you'd imagine. I tried to load the same dump file
into 6.4.2 and we got the same problem as well! So this is bad news as we need
to play with the dump file to get it to reload properly. This means that
our dump files from pg_dump are not really useful without some hacking.

So, we did some testing and managed to produce a simple test case
(although it is a bit long) that causes it to happen all the time, so it
is possible to attach gdb or something to the postgres process to find out
what is happening.

The test script is included below:

Program runs with -o -F switch for no fsync

If you need anything such as a core dump (I don't have a pgsql6.5 compiled
with -g, but i can do this if required) then I will be happy to supply it.

--------------------------------------------------------------------------

Test Case:
----------
I had to paste this into Netscape for the form here, so hopefully it made
it into the email correctly:

Do a:

destroydb tester
createdb tester

and then send this into psql tester

[see the attached file, the mailer daemon wouldn't let me submit this email
unless I compressed it and sent it as an attachment]

--------------------------------------------------------------------------

Solution:
---------
Sorry, no patch for this. I couldn't understand the code on initial inspection.

--------------------------------------------------------------------------

thanks,
Wayne

------------------------------------------------------------------------------
Wayne Piekarski Tel: (08) 8221 5221
Research & Development Manager Fax: (08) 8221 5220
SE Network Access Pty Ltd Mob: 0407 395 889
222 Grote Street Email: wayne(at)senet(dot)com(dot)au
Adelaide SA 5000 WWW: http://www.senet.com.au