Memory problems executing SQL statement

Hello,

Details first! (appologies about the length of this email)

                           version
-------------------------------------------------------------
 PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96

max_connections = 128
shared_buffers = 50000
wal_buffers = 16
sort_mem = 512
wal_files = 8
checkpoint_segments = 3
checkpoint_timeout = 300

shmmax=536870912
shmall=1073741824

The machine is a dual processor Athlon w/ 4 GB of RAM running Linux 7.3
(RPMs kernel-smp-2.4.18-5, postgresql-7.2.1-2PGDG)


  I'm trying to migrate from 7.1.3 to 7.2 and have hit a problem:
whenever I execute the following complex (ok, possibly badly written ;^)
statement I get a severe error.

select tblIRFPAI_Ident.IRFPAIUniqueID,
       tblIRFPAI_MedInfo.ImpGroupAdmit,
       tblRIC.Code as RIC_Code,
       tblRIC.ShortDesc as RIC_ShortDesc,
       tblRIC.Description as RIC_FullDesc,
       tblIRFPAI_CoMorbidities.ICD as Comorbidity
from tblIRFPAI_Ident
     join tblIRFPAI_Main on
     (tblIRFPAI_Main.UniqueID = tblIRFPAI_Ident.IRFPAIUniqueID)
     join tblIRFPAI_MedInfo on
     (tblIRFPAI_MedInfo.IRFPAIUniqueID = tblIRFPAI_Ident.IRFPAIUniqueID)
     left outer join tblImpGroupRICxref on
     (tblImpGroupRICxref.ImpGroupCode = tblIRFPAI_MedInfo.ImpGroupAdmit)
     left outer join tblRIC on
     (tblRIC.Code = tblImpGroupRICxref.RICCode)
     left outer join tblIRFPAI_CoMorbidities on
     (tblIRFPAI_CoMorbidities.IRFPAIUniqueID =
tblIRFPAI_Ident.IRFPAIUniqueID)
where tblIRFPAI_Ident.FacilityUniqueID <> 111 and
      tblIRFPAI_main.AssessType = 'A' and
      tblIRFPAI_main.Deleted <> true and
      tblIRFPAI_MedInfo.ImpGroupAdmit is not NULL and
      tblRIC.Code = 1
order by tblIRFPAI_Ident.IRFPAIUniqueID;


Sometimes I get:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

with the (partial) log showing:

DEBUG:  server process (pid 10673) was terminated by signal 11
DEBUG:  terminating any other active server processes
DEBUG:  all server processes terminated; reinitializing shared memory
and semaphores
FATAL 1:  The database system is starting up
DEBUG:  database system was interrupted at 2002-08-15 17:30:45 EDT


and sometimes:

ERROR:  Read from hashjoin temp file failed

with similar messages in pgsql.log

and sometimes:

MemoryContextAlloc: invalid request size 3137347616 (number changes from
time to time)

This error is both being returned as an exception and appearing in
pgsql.log


I see no errors in /var/log/messages


I'm at a loss of where to look / what to do now. This SQL statement
worked under 7.1.2 with nary a complaint, but I want to move to the
"latest and greatest" version.

Any ideas what could be causing this? My guess is that I'm running out
of resources, but I seem to get different messages at different times so
I can't tell what's being overtaxed.

-Steve

oh, btw
the front end is a Tomcat application (tomcat 3.3 running on a different
machine) using the latest pgsql2.jar
the problem occurs whether it is tomcat issuing the SQL or running it by
hand via psql