Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E64982E309D for ; Fri, 25 Jan 2008 00:50:34 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 37478-03 for ; Fri, 25 Jan 2008 00:50:31 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0F1A72E1C3D for ; Fri, 25 Jan 2008 00:50:29 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m0P4oQnu022976; Thu, 24 Jan 2008 23:50:26 -0500 (EST) To: "Stephen Denne" cc: pgsql-performance@postgresql.org Subject: Re: 8.3rc1 Out of memory when performing update In-reply-to: References: <21675.1201228274@sss.pgh.pa.us> Comments: In-reply-to "Stephen Denne" message dated "Fri, 25 Jan 2008 16:20:49 +1300" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <22963.1201236548.0@sss.pgh.pa.us> Date: Thu, 24 Jan 2008 23:50:25 -0500 Message-ID: <22975.1201236625@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/229 X-Sequence-Number: 28468 ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <22963.1201236548.1@sss.pgh.pa.us> "Stephen Denne" 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 ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <22963.1201236548.2@sss.pgh.pa.us> Content-Description: trigger-leak.patch Index: ri_triggers.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v retrieving revision 1.101 diff -c -r1.101 ri_triggers.c *** ri_triggers.c 3 Jan 2008 21:23:15 -0000 1.101 --- ri_triggers.c 25 Jan 2008 04:45:56 -0000 *************** *** 3099,3104 **** --- 3099,3106 ---- elog(ERROR, "conkey is not a 1-D smallint array"); riinfo->nkeys = numkeys; memcpy(riinfo->fk_attnums, ARR_DATA_PTR(arr), numkeys * sizeof(int16)); + if ((Pointer) arr != DatumGetPointer(adatum)) + pfree(arr); /* free de-toasted copy, if any */ adatum = SysCacheGetAttr(CONSTROID, tup, Anum_pg_constraint_confkey, &isNull); *************** *** 3113,3118 **** --- 3115,3122 ---- ARR_ELEMTYPE(arr) != INT2OID) elog(ERROR, "confkey is not a 1-D smallint array"); memcpy(riinfo->pk_attnums, ARR_DATA_PTR(arr), numkeys * sizeof(int16)); + if ((Pointer) arr != DatumGetPointer(adatum)) + pfree(arr); /* free de-toasted copy, if any */ adatum = SysCacheGetAttr(CONSTROID, tup, Anum_pg_constraint_conpfeqop, &isNull); *************** *** 3127,3132 **** --- 3131,3138 ---- ARR_ELEMTYPE(arr) != OIDOID) elog(ERROR, "conpfeqop is not a 1-D Oid array"); memcpy(riinfo->pf_eq_oprs, ARR_DATA_PTR(arr), numkeys * sizeof(Oid)); + if ((Pointer) arr != DatumGetPointer(adatum)) + pfree(arr); /* free de-toasted copy, if any */ adatum = SysCacheGetAttr(CONSTROID, tup, Anum_pg_constraint_conppeqop, &isNull); *************** *** 3141,3146 **** --- 3147,3154 ---- ARR_ELEMTYPE(arr) != OIDOID) elog(ERROR, "conppeqop is not a 1-D Oid array"); memcpy(riinfo->pp_eq_oprs, ARR_DATA_PTR(arr), numkeys * sizeof(Oid)); + if ((Pointer) arr != DatumGetPointer(adatum)) + pfree(arr); /* free de-toasted copy, if any */ adatum = SysCacheGetAttr(CONSTROID, tup, Anum_pg_constraint_conffeqop, &isNull); *************** *** 3155,3160 **** --- 3163,3170 ---- ARR_ELEMTYPE(arr) != OIDOID) elog(ERROR, "conffeqop is not a 1-D Oid array"); memcpy(riinfo->ff_eq_oprs, ARR_DATA_PTR(arr), numkeys * sizeof(Oid)); + if ((Pointer) arr != DatumGetPointer(adatum)) + pfree(arr); /* free de-toasted copy, if any */ ReleaseSysCache(tup); } ------- =_aaaaaaaaaa0--