Search Postgresql Archives

Referential integrity vulnerability in 8.3.3

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux