From: | Daniel Cristian Cruz <danielcristian(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Pg Bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #6226: Broken foreign key stored on database (parent deleted with children still readable, BUG#6225 Update) |
Date: | 2011-10-05 17:45:23 |
Message-ID: | CACffM9GqNJVfhU=Tsq9=_CcLNeCoBzyW-2drB-nk-c8oq9G1Pg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
2011/10/5 Alvaro Herrera <alvherre(at)commandprompt(dot)com>
> Well, some people say it's a bug, others say it's not; and even if it
> is, changing it means backwards incompatible behavior, so *if* it is
> patched, it will only change the behavior on a future release, not
> whatever you're using.
Well, I'll try to explain better, because it could be a bug, since after
deleting and cascading, a deleted row is still visible and has a foreign key
constraint violated with no error message.
Even if some people relies on this to build something, it is something not
desirable, because I expect a foreign key constraint to be always true.
The sample error case is:
-- My event table
CREATE TABLE event (
id_event serial primary key
);
-- My event repetition configuration
CREATE TABLE repetition_conf (
id_repetition_conf serial primary key,
id_event integer,
CONSTRAINT event_fk FOREIGN KEY (id_event) REFERENCES event (id_event)
ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
-- IF MAIN EVENT IS DELETED, REPETITION CONFIG IS DELETED TOO
);
-- Event repetition events
CREATE TABLE repetition (
id_repetition serial primary key,
id_repetition_conf integer,
id_event integer,
CONSTRAINT repetition_conf_fk FOREIGN KEY (id_repetition_conf) REFERENCES
repetition_conf (id_repetition_conf)
ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-- if I delete config, I delete all repetitions
CONSTRAINT event_fk FOREIGN KEY (id_event) REFERENCES event (id_event)
ON UPDATE NO ACTION ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
-- if repeated event is deleted, just set null to the reference, I need
to know there was a repetition
);
-- a trigger to remove an repetition event, when repetition is deleted
CREATE OR REPLACE FUNCTION remove_event()
RETURNS trigger AS
$BODY$
BEGIN
DELETE FROM event
WHERE id_event = OLD.id_event;
RETURN OLD;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER remove_repetition_event
BEFORE DELETE
ON repetition
FOR EACH ROW
WHEN ((OLD.id_event IS NOT NULL))
EXECUTE PROCEDURE remove_event();
BEGIN;
-- create the main event
INSERT INTO event (id_event) VALUES (DEFAULT);
-- create the repetition config
INSERT INTO repetition_conf (id_event)
VALUES(CURRVAL('event_id_event_seq'));
-- create the repetition event
INSERT INTO event (id_event) VALUES (DEFAULT);
-- create the repetition link
INSERT INTO repetition (id_repetition, id_repetition_conf, id_event)
VALUES (DEFAULT, CURRVAL('repetition_conf_id_repetition_conf_seq'),
CURRVAL('event_id_event_seq'));
COMMIT;
-- delete an event with repetition in a transaction
-- *event *cascades to *repetition_conf*, then cascades to* repetition*,
then trigger cascades to* event* which set nulls back to *repetition*
BEGIN;
DELETE FROM event WHERE id_event = 1;
COMMIT;
-- now, there is a repetition pointing to a config, that is not visible
anymore
SELECT
repetition.id_repetition_conf AS referenced_id,
repetition_conf.id_repetition_conf AS reference
FROM repetition
LEFT JOIN repetition_conf
ON repetition.id_repetition_conf = repetition_conf.id_repetition_conf
WHERE repetition_conf.id_repetition_conf IS NULL;
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2011-10-05 21:12:30 | Re: BUG #6238: ECPG converts "long long" to long on Windows |
Previous Message | Tom Lane | 2011-10-05 15:23:15 | Re: BUG #6240: About - postgreswdinit.sql |