I don't have a PostgreSQL build environment.
It is now Friday night for me. I left the alternate query running, and will find out on Monday what happened.
If I drop the fk constraint, and/or its index, would I still be affected by the leak you found?
Regards,
Stephen Denne.
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Fri 25/01/2008 5:50 p.m.
To: Stephen Denne
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 8.3rc1 Out of memory when performing update
"Stephen Denne" <Stephen.Denne@datamail.co.nz> writes:
> I altered the update statement slightly, and reran the query.
> The altered query has been running over 3 hours now,
> without using lots of memory (38M private bytes).
> 2046 temp files were created (2.54GB worth),
> which have recently changed from slowly growing in size
> to very very slowly reducing in number.
Hmm. I think what that really means is you haven't got to the part of
the query where the leak is :-(. In my attempt to reproduce this
I found that 8.3 has introduced a memory leak into the RI trigger
support, such that even if an UPDATE doesn't change the FK columns
it's still likely to leak a few dozen bytes per updated row.
Please see if the attached patch makes it better for you.
regards, tom lane
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way.
__________________________________________________________________ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite.
Please see http://www.dmzglobal.com/services/bqem.htm for details.
__________________________________________________________________
