Hello pgsql-hackers,
Two tables t1 and t2.t2 has fk (no action) to t1.Two triggers on t1 before delete, delete all fk on t2, and on t2after
deleteupdate t1.
When we do delete on t1, we have situation when at t1 updates recordthat deleting. Of course logic is not correct,
but...PosgreSQLdelete records from t2, update record at t1 and leave it alive. Butwhy?
--
new_db=# SELECT * FROM t1;id | suma
----+------10 | 511 | 612 | 6
(3 rows)
new_db=# SELECT * FROM t2;id | fk_t1 | suma2
----+-------+-------12 | 10 | 613 | 10 | 6
(2 rows)
new_db=# DELETE FROM t1 WHERE id=10;
DELETE 0
:(
new_db=# SELECT * FROM t1;id | suma
----+------11 | 612 | 610 | -7^^^^^^^^^^^^^^^ why?
(3 rows)
new_db=# SELECT * FROM t2;id | fk_t1 | suma2
----+-------+-------
(0 rows)
new_db=# DELETE FROM t1 WHERE id=10;
DELETE 1
Test finished.
Script:
--
CREATE TABLE t1 ( id bigserial NOT NULL, suma bigint
);
CREATE TABLE t2 ( id bigserial NOT NULL, fk_t1 bigint, suma2 bigint
);
INSERT INTO t1 (id,suma) VALUES (10,5);
INSERT INTO t1 (id,suma) VALUES (11,6);
INSERT INTO t1 (id,suma) VALUES (12,6);
INSERT INTO t2 (id,fk_t1,suma2) VALUES (12,10,6);
INSERT INTO t2 (id,fk_t1,suma2) VALUES (13,10,6);
CREATE FUNCTION test2_t2() RETURNS "trigger" AS $$
begin /* Тело функции */ UPDATE t1 SET suma=suma-old.suma2 WHERE t1.id=old.fk_t1; return null;
end;
$$ LANGUAGE plpgsql;
CREATE FUNCTION test_t1() RETURNS "trigger" AS $$
begin /* Тело функции */ DELETE FROM t2 WHERE fk_t1=old.id; return old;
end;
$$ LANGUAGE plpgsql;
CREATE TRIGGER t1_tr BEFORE DELETE ON t1 FOR EACH ROW EXECUTE PROCEDURE test_t1();
CREATE TRIGGER t2_tr AFTER DELETE ON t2 FOR EACH ROW EXECUTE PROCEDURE test2_t2();
ALTER TABLE ONLY t1 ADD CONSTRAINT t1_pkey PRIMARY KEY (id);
ALTER TABLE ONLY t2 ADD CONSTRAINT t2_fk FOREIGN KEY (fk_t1) REFERENCES t1(id) ON UPDATE NO ACTION ON DELETE NO
ACTION;
--
--
С наилучшими пожеланиями, Репко Андрей Владимирович mailto:repko@sart.must-ipra.com
