"Stephen Denne" <Stephen.Denne@xxxxxxxxxxxxxx> 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
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);
}
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
