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

2011/10/5 Alvaro Herrera <alvherre@commandprompt.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 =3D 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 =3D 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 =3D repetition_conf.id_repetition_conf
WHERE repetition_conf.id_repetition_conf IS NULL;


--=20
Daniel Cristian Cruz
=E3=82=AF=E3=83=AB=E3=82=BA =E3=82=AF=E3=83=AA=E3=82=B9=E3=83=81=E3=82=A2=
=E3=83=B3 =E3=83=80=E3=83=8B=E3=82=A8=E3=83=AB