Re: Trigger to update records out of memory

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'?

--
Robert