Re: out of memory error

Thanks,
So I've been able to find what's causing my postgres process memory amount
to grow, but I don't know why it happens.

So, my software is updating 6 rows/second on my main database.
Rubyrep is running on my server with thebackup database doing a "replicate"
The huge TopMemoryContext problem and memory size of the postgres process
are happening on my backup database.

I've found out that when my software does these updates the memory of the
postgres process grows constantly at 24 MB/hour. when I stop my software to
update these rows, the memory of the process stops to grow.

also I've noticed that when I stop rubyrep, this postgres process disappear.

I'm running rubyrep 1.2.0 and here is my rubyrep config file :

RR::Initializer::run do |config|
> config.left = { # main database
> :adapter => 'xxx',
> :database => 'yyy',
> :username => 'zzz',
> :password => 'ttt',
> :host => '10.97.14.102'
> }
>
> config.right = { # backup database
> :adapter => 'xxx',
> :database => 'yyy',
> :username => 'zzz',
> :password => 'ttt',
> :host => '10.97.14.101'
> }
>
> config.options[:sync_conflict_handling] = :right_wins
> config.options[:replication_conflict_handling] = :right_wins
> config.include_tables 'archiver'
> config.include_tables 'camera'
> config.include_tables 'cda'
> config.include_tables 'component_restart'
> config.include_tables 'operation_mode'
> config.include_tables 'ptz_lock'
> config.include_tables 'standby_table'
> config.include_tables 'video_output'
>
> end
>

I don't know what other information I could add to that.

Vincent.

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

> Vincent Dautremont <vincent(at)searidgetech(dot)com> writes:
> >> 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
>
> Well, yeah, that is the common way to define a function. The question
> was about whether you are constantly re-executing this same SQL
> thousands of times over the course of a session. I think it would take
> actually using the function in between such redefinitions to provoke a
> plan leak, but if you were doing that it might explain the problem.
>
> regards, tom lane
>