Referential integrity vulnerability in 8.3.3

Hello community

There is an oddity (or a bug) in situation with returning null before
delete trigger and referential integrity in PG 8.3.3. I tryed to find
a solution in Google and PG documentation and have noticed nothing
useful.

Let's start from tables creation.

CREATE TABLE table1
(
id serial NOT NULL,
field1 text,
CONSTRAINT table1_pk PRIMARY KEY (id)
);

CREATE TABLE table2
(
id serial NOT NULL,
table1_id integer,
CONSTRAINT table2_pk PRIMARY KEY (id),
CONSTRAINT table2_fk1 FOREIGN KEY (table1_id)
REFERENCES table1 (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);

Well.. Second one refrences first one and has to be updated and
deleted cascaded. Next create before delete trigger on tabe2 allways
returning null.

CREATE OR REPLACE FUNCTION tr_stop()
RETURNS trigger AS
$BODY$begin
return null;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER tr_stop
BEFORE DELETE
ON table2
FOR EACH ROW
EXECUTE PROCEDURE tr_stop();

Inserting three rows into table1

insert into table1 (id, field1) values (1, 'qqq');
insert into table1 (id, field1) values (2, 'www');
insert into table1 (id, field1) values (3, 'eee');

and refer to them from table2.

insert into table2 (id, table1_id) values (1, 1);
insert into table2 (id, table1_id) values (2, 2);
insert into table2 (id, table1_id) values (3, 3);

Now comming to a head. As I supposed earlier, deletion from table1 has
to be prevented by referential integrity when the trigger prevents
deletion of refered row from table2. But it doesn't.

delete from table1;

It deletes all rows from table1 and doesn't touch rows from table2.

select * from table1
id | field1
----+--------
(0 rows)

select * from table2 where not exists(select 1 from table1 where id =
table2.table1_id)
id | table1_id
----+-----------
1 | 1
2 | 2
3 | 3
(3 rows)

Will you explain me please why PG behave so cos IMHO it's a bit
illogical. Thanx.

p.s. Some info from pg_trigger below

select c.relname, t.* from pg_class c left join pg_trigger t on
t.tgrelid = c.oid
where relname in ('table1', 'table2') order by relname

relname | tgrelid | tgname | tgfoid |
tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid |
tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr |
tgargs
---------+----------+-------------------------------+----------+--------+-----------+----------------+--------------+---------------+--------------+--------------+----------------+---------+--------+--------
table1 | 55880268 | RI_ConstraintTrigger_55880305 | 1646 |
9 | O | t | table2_fk1 | 55880296 |
55880302 | f | f | 0 | |
table1 | 55880268 | RI_ConstraintTrigger_55880306 | 1647 |
17 | O | t | table2_fk1 | 55880296 |
55880302 | f | f | 0 | |
table2 | 55880296 | tr_stop |
55881180 | 11 | O | f | |
0 | 0 | f | f | 0 |
|
table2 | 55880296 | RI_ConstraintTrigger_55880303 | 1644 |
5 | O | t | table2_fk1 | 55880268 |
55880302 | f | f | 0 | |
table2 | 55880296 | RI_ConstraintTrigger_55880304 | 1645 |
17 | O | t | table2_fk1 | 55880268 |
55880302 | f | f | 0 | |
(5 rows)

--
Regards,
Sergey Konoplev