Re: Trigger to update records out of memory

DeJuan Jackson <djackson(at)speedfc(dot)com> writes:
> I think the IMMUTABLE might be your issue.

Nah, the problem is that the trigger is recursive.
The UPDATEs it performs internally trigger the trigger again,
resulting in another UPDATE, resulting in another trigger call...
eventually you run out of memory.

AFAICT the UPDATEs are the hardest possible way to do things
anyhow. You're in a BEFORE trigger, you can just alter the NEW
record to alter what will be stored.

Finally, RETURN NULL is not what you want in a BEFORE trigger;
that disables actually doing anything. (In this case it fails
before you ever get that far :-()

In short the function ought to look more like

CREATE OR REPLACE FUNCTION clear_common_groups () RETURNS trigger AS'
BEGIN
IF NEW.common_area = ''t'' THEN
NEW.common_area = ''f'';
END IF;
IF NEW.exterior_area = ''t'' THEN
NEW.exterior_area = ''f'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

I agree that the IMMUTABLE and other decorations are useless though...

regards, tom lane