Re: Postgres consuming way too much memory???

["jody brownell" <jody(dot)brownell(at)q1labs(dot)com>]

The last version of postgres we had in production was 8.1.1 actually, not 8.1.3. 

So far, on my stability box and older production stability boxes I dont see the same behavior. 

I will install 8.1.1 on these boxes and see what I see.

On Thursday 15 June 2006 09:01, jody brownell wrote:
> 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
> > 
> > 
> > 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>