BUG #6225: Child table with a missing parent key which no exists in a complex trigger/cascade schema - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      6225
Logged by:          Daniel Cristian Cruz
Email address:      danielcristian@gmail.com
PostgreSQL version: 9.1.0
Operating system:   "PostgreSQL 9.1.0 on x86_64-unknown-linux-gnu, compiled
by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit"
Description:        Child table with a missing parent key which no exists in
a complex trigger/cascade schema
Details:

Hi masters,

I will update my server to 9.1.1, but saw right now it was updated, and I
know that this problem occurs in 9.0.4 because my team is using 9.0.

I had this schema, when, after deleting a main record, it cascades to
childrens.

The last line shows a repetition that has a repetition_conf that is no
longer in database...

CREATE TABLE event (
 id_event serial primary key
);

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
);

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 REPETITION CONFIG IS DELETED, REPETITION IS DELETED TOO
 CONSTRAINT event_fk FOREIGN KEY (id_event) REFERENCES event (id_event)
  ON UPDATE NO ACTION ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
   -- IF EVENT IS DELETED, SET NULL TO REFERENCE (BUSINESS LOGIC)
);

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();
   -- IF REPETITION IS DELETED, EVENT IS DELETED TOO

-- ACTUAL INSERT STEPS
BEGIN;
INSERT INTO event (id_event) VALUES (DEFAULT); -- CREATE MAIN EVENT
INSERT INTO repetition_conf (id_event) VALUES
(CURRVAL('event_id_event_seq')); -- CREATE REPETITION CONFIG
-- THESE TWO NEXT STEPS ARE A LOOP IN A TRIGGER ON repetition_conf
INSERT INTO event (id_event) VALUES (DEFAULT);
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;

-- IN ANOTHER SESSION, DELETE THE MAIN EVENT, WICH WILL DELETE CONFIG AND
REPETITIONS
BEGIN;
DELETE FROM event WHERE id_event = 1;
COMMIT;

-- NOW I HAD A FOREIGN KEY WITH NO PARENT...
SELECT *
FROM repetition
LEFT JOIN repetition_conf
 ON repetition.id_repetition_conf = repetition_conf.id_repetition_conf
WHERE repetition_conf.id_repetition_conf IS NULL;