Trigger causes the server to crash with SEGV - Mailing list pgsql-bugs

Vlad Seryakov (vlad@crystalballinc.com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Trigger causes the server to crash with SEGV

Long Description
PostgreSQL version : PostgreSQL 7.2devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
create table D_Inv_Location_Types (
   Inv_Loc_Type_ID integer not null,
   Inv_Loc_Type_Name varchar not null,
   Inv_Loc_Type_Description varchar not null,
   Inv_Loc_Type_Disp_ID_Name varchar null,
   CONSTRAINT DILT_pk PRIMARY KEY(Inv_Loc_Type_ID),
   CONSTRAINT DILT_uk UNIQUE(Inv_Loc_Type_Name)
);
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_desc
ription,inv_loc_type_disp_id_name) values(96,'State','These are what make up the Unit
ed States of America','State');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_desc
ription,inv_loc_type_disp_id_name) values(3,'City','This is a big town','City');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_desc
ription,inv_loc_type_disp_id_name) values(15,'County','This is a large piece of land
that holds numerous settlements including cities and towns.','County ID');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_desc
ription,inv_loc_type_disp_id_name) values(174,'Country','This is an area of geographi
cal location that governs itself from a central location. i.e. Like that country Scot
land in the Continent of Europe','Country ID');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_desc
ription,inv_loc_type_disp_id_name) values(166,'Central Office','CO is the facility wh
ere all Telco equipment serving a particular area is located. The CO may host voice a
nd/or data and/or video equipment.There may also be network management servers there.
','CO ID');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_desc
ription,inv_loc_type_disp_id_name) values(4,'Street','This is basically a road','ACIS
A');
insert into d_inv_location_types (inv_loc_type_id,inv_loc_type_name,inv_loc_type_desc
ription,inv_loc_type_disp_id_name) values(337,'Intersection','This is a location that
 basically joins two points','Intersect ID');
create table D_Inv_Loc_Type_Parent_Allowed (
   Inv_Loc_Type_ID integer not null,
   Inv_Loc_Parent_ID integer not null,
   CONSTRAINT D_Inv_LPTA_pk PRIMARY KEY(Inv_Loc_Type_ID,Inv_Loc_Parent_ID),
   CONSTRAINT D_Inv_LPTA_fk FOREIGN KEY(Inv_Loc_Type_ID) references D_Inv_Location_Ty
pes(Inv_Loc_Type_ID),
   CONSTRAINT D_Inv_LPTA2_fk FOREIGN KEY(Inv_Loc_Parent_ID) references D_Inv_Location
_Types(Inv_Loc_Type_ID)
);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values
(4,15);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values
(4,3);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values
(3,15);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values
(15,96);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values
(166,3);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values
(96,174);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values
(337,3);
insert into d_inv_loc_type_parent_allowed (inv_loc_type_id,inv_loc_parent_id) values
(337,4);
DROP FUNCTION sp_loc_parent_check(INTEGER);
CREATE FUNCTION sp_loc_parent_check(INTEGER) RETURNS BOOLEAN AS '
DECLARE
   ID ALIAS FOR $1;
   rows RECORD;
BEGIN
  FOR rows IN SELECT * FROM d_inv_loc_type_parent_allowed
              WHERE inv_loc_parent_id=ID LOOP
    IF rows.inv_loc_type_id = ID THEN
       RAISE EXCEPTION ''OSS: You can not make a child of the location type its paren
t'';
    END IF;
    PERFORM sp_loc_parent_check(rows.inv_loc_type_id);
  END LOOP;
  RETURN TRUE;
END;' LANGUAGE 'plpgsql';

DROP FUNCTION loc_types_parent_trigger_func();
CREATE FUNCTION loc_types_parent_trigger_func() RETURNS OPAQUE AS '
DECLARE
    rows RECORD;
BEGIN
    PERFORM sp_loc_parent_check(NEW.inv_loc_type_id);
    RETURN NEW;
END;' LANGUAGE 'plpgsql';

DROP TRIGGER loc_types_parent_trigger ON d_inv_loc_type_parent_allowed;
CREATE TRIGGER loc_types_parent_trigger AFTER INSERT ON d_inv_loc_type_parent_allowed
FOR EACH ROW EXECUTE PROCEDURE loc_types_parent_trigger_func();

/* The statement that is causing the error with the system*/
insert into d_inv_loc_type_parent_allowed values(96,3);




Sample Code


No file was uploaded with this report