Re: out of memory error

Hi,
you were right,
I do see those CREATE OR REPLACE FUNCTION a bit more than 1 per second
(approx. 12 times for 10 seconds)

2012-05-23 21:15:45 WET LOG: execute <unnamed>: CREATE OR
> REPLACE FUNCTION "rr_ptz_lock"() RETURNS TRIGGER AS $change_trigger$
> BEGIN
> PERFORM ACTIVE FROM
> "public".rr_running_flags;
> IF FOUND THEN
> RETURN NULL;
> END IF;
>
> IF (TG_OP = 'DELETE') THEN
> INSERT INTO "public".rr_pending_changes(change_table,
> change_key, change_type, change_time)
> SELECT 'ptz_lock', 'workstation_id|' ||
> OLD."workstation_id" || '|' || 'camera_id|' || OLD."camera_id", 'D', now();
> ELSIF (TG_OP = 'UPDATE') THEN
>
> INSERT INTO "public".rr_pending_changes(change_table,
> change_key, change_new_key, change_type, change_time)
> SELECT 'ptz_lock', 'workstation_id|' ||
> OLD."workstation_id" || '|' || 'camera_id|' || OLD."camera_id",
> 'workstation_id|' || NEW."workstation_id" || '|' || 'camera_id|' ||
> NEW."camera_id", 'U', now();
> ELSIF (TG_OP = 'INSERT') THEN
> INSERT INTO "public".rr_pending_changes(change_table,
> change_key, change_type, change_time)
> SELECT 'ptz_lock', 'workstation_id|' ||
> NEW."workstation_id" || '|' || 'camera_id|' || NEW."camera_id", 'I', now();
> END IF;
> RETURN NULL; -- result is ignored since this is an AFTER
> trigger
> END;
> $change_trigger$ LANGUAGE plpgsql
>

I don't know a lot about the internal of rubyrep, but do you think this is
not a normal behaviour from a postgresql server point of view ?

Vincent.

On Wed, May 23, 2012 at 11:26 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Vincent Dautremont <vincent(at)searidgetech(dot)com> writes:
> > 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.
>
> Hmm. I wondered whether rubyrep might be triggering this somehow;
> I don't know anything about that software. I went so far as to download
> rubyrep and look at the Postgres-specific source code yesterday. It
> doesn't look like it's doing anything strange, but I could easily have
> missed something.
>
> One thing you could try doing is to turn on query logging (set
> log_statement = all) and look to see if rubyrep, or some other part of
> your system, is indeed issuing repeated CREATE OR REPLACE FUNCTION
> commands.
>
> regards, tom lane
>