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 <t...@sss.pgh.pa.us> wrote: > Vincent Dautremont <vinc...@searidgetech.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 >
