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 :
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.
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@xxxxxxxxxxxxx> wrote:
Vincent Dautremont <vincent@xxxxxxxxxxxxxxxx> writes:This is not much information. What I suspect is happening is that
> 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.
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
