Re: Trigger to update records out of memory

I think the IMMUTABLE might be your issue.



Robert Fitzpatrick wrote:

>On Fri, 2004-06-18 at 13:17, Robert Fitzpatrick wrote:
>
>
>>ohc=# CREATE OR REPLACE FUNCTION "public"."clear_common_groups" ()
>>RETURNS trigger AS'
>>ohc'# BEGIN
>>ohc'#   IF NEW.common_area = ''t'' THEN
>>ohc'#     UPDATE tblhudunits SET common_area = ''f'' WHERE
>>hud_building_id = NEW.hud_building_id;
>>ohc'#   END IF;
>>ohc'#   IF NEW.exterior_area = ''t'' THEN
>>ohc'#     UPDATE tblhudunits SET exterior_area = ''f'' WHERE
>>hud_building_id = NEW.hud_building_id;
>>ohc'#   END IF;
>>ohc'#   RETURN NULL;
>>ohc'# END;
>>ohc'# 'LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY
>>INVOKER;
>>CREATE FUNCTION
>>ohc=# CREATE TRIGGER "new_common_area" BEFORE INSERT OR UPDATE
>>ohc-# ON "public"."tblhudunits" FOR EACH ROW
>>ohc-# EXECUTE PROCEDURE "public"."clear_common_groups"();
>>CREATE TRIGGER
>>ohc=# update tblhudunits set common_area = 't' where sort_order = 2;
>>ERROR:  out of memory
>>DETAIL:  Failed on request of size 1048576.
>>
>>
>>
>
>After getting doing some NOTICEs, I find it I'm looping my update
>funtion. But I change the TRIGGER to 'BEFORE INSERT' instead and now I
>have a peculiar problem. The first time I ran the UPDATE query, I
>receive an good response, ever since I receive 'INSERT 0 0'. But neither
>time did the record get inserted.
>
>ohc=# insert into tblhudunits (hud_building_id, sort_order, common_area)
>values (21, 10, 't');
>NOTICE:  21
>INSERT 1304826 1
>ohc=# insert into tblhudunits (hud_building_id, sort_order, common_area)
>values (21, 10, 't');
>NOTICE:  21
>INSERT 0 0
>
>Same 'INSERT 0 0' no matter what valid values I use. Can someone tell me
>what it means to receive 'INSERT 0 0'?
>
>
>