Re: BUG #6226: Broken foreign key stored on database (parent deleted with children still readable, BUG#6225 Update)

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
クルズ クリスチアン ダニエル