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
