Re: 8.3rc1 Out of memory when performing update - Mailing list pgsql-performance

"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
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);
  }