Re: Postgres consuming way too much memory???

Sorry about that, I was in a slight panic :)

I am using postgresql 8.1.4. I will install 8.1.3 and see if the same behavior exists.. we
may have started seeing this in 8.1.3, but I dont think before. I will check some stability
machines for similar bloating.

The query (calling a store proc) which is always running when the spiral begins is below. It simply performs
bulk linking of two objects. Depending on what the application is detecting, it could be called to insert
40 - 50k records, 500 at a time. When the box is healthy, this is a 200 - 500 ms op, but this starts to become
a 20000+ ms op. I guess this makes sense considering the paging.....

Jun 14 12:50:18 xxx postgres[5649]: [3-1] LOG: duration: 20117.984 ms statement: EXECUTE <unnamed> [PREPARE: select * from link_attacker_targets($1, $2, $3) as

CREATE OR REPLACE FUNCTION link_attacker_targets (p_attacker bigint, p_targets varchar, p_targets_size integer)
returns bigint[] as
$body$
DECLARE
v_targets bigint[];
v_target bigint;
v_returns bigint[];
v_returns_size integer := 0;
BEGIN
v_targets := convert_string2bigint_array (p_targets, p_targets_size);

FOR i IN 1..p_targets_size LOOP
v_target := v_targets[i];

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;
END LOOP;
RETURN v_returns;
END;
$body$
LANGUAGE plpgsql VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

On Wednesday 14 June 2006 17:03, you wrote:
> "jody brownell" <jody(dot)brownell(at)q1labs(dot)com> writes:
> > 27116 postgres 15 0 1515m 901m 91m S 0.0 22.9 18:33.96 postgres: qradar qradar ::ffff:x.x.x.x(51149) idle
>
> This looks like a memory leak, but you haven't provided enough info to
> let someone else reproduce it. Can you log what your application is
> doing and extract a test case? What PG version is this, anyway?
>
> regards, tom lane
>
>
>