From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Denne <Stephen.Denne@datamail.co.nz>
Cc: pgsql-performance@postgresql.org
Subject: Re: 8.3rc1 Out of memory when performing update
Date: Thu, 24 Jan 2008 23:50:25 -0500
Message-ID: <22975.1201236625@sss.pgh.pa.us> (raw)
In-Reply-To: <F0238EBA67824444BC1CB4700960CB480482B5B7@dmpeints002.isotach.com>
References: <F0238EBA67824444BC1CB4700960CB480482B525@dmpeints002.isotach.com>
<21675.1201228274@sss.pgh.pa.us>
<F0238EBA67824444BC1CB4700960CB480482B5B7@dmpeints002.isotach.com>
"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
inline patch 1: ri_triggers.c (+10/-0 lines) download inline-1.patchIndex: 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);
}
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, Stephen.Denne@datamail.co.nz
Subject: Re: 8.3rc1 Out of memory when performing update
In-Reply-To: <22975.1201236625@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox