From: | "Daniel Cristian Cruz" <danielcristian(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #6226: Broken foreign key stored on database (parent deleted with children still readable, BUG#6225 Update) |
Date: | 2011-09-26 18:29:33 |
Message-ID: | 201109261829.p8QITXaZ017828@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 6226
Logged by: Daniel Cristian Cruz
Email address: danielcristian(at)gmail(dot)com
PostgreSQL version: 9.1.1
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: Broken foreign key stored on database (parent deleted
with children still readable, BUG#6225 Update)
Details:
Hello,
Maybe my last message was very hard to read, English is not my natural
language and I'm with a strong headache (a real one, not about this
problem).
I've tested BUG #6225 in 9.1.1, and the problem persists. After deleting the
parent record, children record stills readable with a key that points to the
deleted record.
Script that shows the problem:
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, WHICH 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;
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2011-09-26 20:10:45 | Re: BUG #6226: Broken foreign key stored on database (parent deleted with children still readable, BUG#6225 Update) |
Previous Message | Robert Haas | 2011-09-26 17:14:40 | Re: comment fixes |