Re: out of memory error

Thanks Tom,
when you say,

> An entirely blue-sky guess as
> to what your code might be doing to trigger such a problem is if you
> were constantly replacing the same function's definition via CREATE OR
> REPLACE FUNCTION.
>
Do you mean that what would happen is that when we call the plpgsql
function, it executes each time a create or replace, then execute the
function ?
because my functions are all written like that :

-- Function: spzoneinsert(integer, integer)
> CREATE OR REPLACE FUNCTION spzoneinsert(i_zoneid integer, i_output_port
> integer)
> RETURNS void AS
> $BODY$
>
> BEGIN
> Begin
> INSERT INTO zone
> (zone_Id,
> output_port)
> VALUES
> (i_zoneID,
> i_Output_Port);
> End;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
> ALTER FUNCTION spzoneinsert(integer, integer) OWNER TO db_casd_admin_role;
> GRANT EXECUTE ON FUNCTION spzoneinsert(integer, integer) TO
> db_casd_admin_role;
>

Which is suppose is the normal way to write a function. I assume only the
partr between the $BODY$ are executed.
then this is called via ADODB in our C++ softwares.

I will try to run a DB without its client softwares, just both DBs with
rubyrep and a BAT script doing updates using one of my functions call at a
quicker rate than 2Hz. and'll monitor the memory usage and PG logs

Vincent.

On Tue, May 22, 2012 at 2:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Vincent Dautremont <vincent(at)searidgetech(dot)com> writes:
> > I think that i'm using the database for pretty basic stuffs.
> > It's mostly used with stored procedures to update/ insert / select a row
> of
> > each table.
> > On 3 tables (less than 10 rows each), clients does updates/select at 2Hz
> to
> > have pseudo real-time data up to date.
> > I've got a total of 6 clients to the DB, they all access DB using stored
> > procedures
> > I would say that this is a light usage of the DB.
>
> > Then I have rubyrep 1.2.0 running to sync a backup of the DB.
> > it syncs 8 tables : 7 of them doesn't really change often and 1 is one of
> > the pseudo real-time data one.
>
> This is not much information. What I suspect is happening is that
> you're using plpgsql functions (or some other PL) in such a way that the
> system is leaking cached plans for the functions' queries; but there is
> far from enough evidence here to prove or disprove that, let alone debug
> the problem if that is a correct guess. An entirely blue-sky guess as
> to what your code might be doing to trigger such a problem is if you
> were constantly replacing the same function's definition via CREATE OR
> REPLACE FUNCTION. But that could be totally wrong, too.
>
> Can you put together a self-contained test case that triggers similar
> growth in the server process size?
>
> regards, tom lane
>