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@xxxxxxxxxxxxx> wrote:
Vincent Dautremont <vincent@xxxxxxxxxxxxxxxx> writes:Hmm. I wondered whether rubyrep might be triggering this somehow;
> 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 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
