X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 29DB99FA5BA for ; Thu, 15 Jun 2006 12:44:20 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 11388-09 for ; Thu, 15 Jun 2006 12:44:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 88FAF9F9CAA for ; Thu, 15 Jun 2006 12:44:09 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k5FFi8Bo028420; Thu, 15 Jun 2006 11:44:08 -0400 (EDT) To: "jody brownell" cc: pgsql-performance@postgresql.org Subject: Re: Postgres consuming way too much memory??? In-reply-to: <200606150901.19201.jody.brownell@q1labs.com> References: <200606141618.37849.jody.brownell@q1labs.com> <3106.1150315380@sss.pgh.pa.us> <200606150901.19201.jody.brownell@q1labs.com> Comments: In-reply-to "jody brownell" message dated "Thu, 15 Jun 2006 09:01:18 -0300" Date: Thu, 15 Jun 2006 11:44:08 -0400 Message-ID: <28419.1150386248@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/306 X-Sequence-Number: 19663 "jody brownell" writes: > BEGIN > INSERT into attacker_target_link (attacker_id, target_id) values (p_attacker, v_target); > v_returns_size := v_returns_size + 1; > v_returns[v_returns_size] := v_target; > EXCEPTION WHEN unique_violation THEN > -- do nothing... app cache may be out of date. > END; Hmm. There is a known problem that plpgsql leaks some memory when catching an exception: http://archives.postgresql.org/pgsql-hackers/2006-02/msg00885.php So if your problem case involves a whole lot of duplicates then that could explain the initial bloat. However, AFAIK that leakage is in a transaction-local memory context, so the space ought to be freed at transaction end. And Linux's malloc does know about giving space back to the kernel (unlike some platforms). So I'm not sure why you're seeing persistent bloat. Can you rewrite the function to not use an EXCEPTION block (perhaps a separate SELECT probe for each row --- note this won't be reliable if there are concurrent processes making insertions)? If so, does that fix the problem? regards, tom lane