The following bug has been logged on the website:
Bug reference: 6632
Logged by: Ignas Mikalajunas
Email address: ignas@pow.lt
PostgreSQL version: 9.1.3
Operating system: Ubuntu 11.11
Description:=20=20=20=20=20=20=20=20
The snippet that reproduces the bug:
drop schema public cascade;
create schema public;
-- Setup
BEGIN;
CREATE TABLE apps (
id bigserial NOT NULL,
"type" varchar(32),
primary key (id)
);
CREATE TABLE content_items (
id bigserial NOT NULL,
"type" varchar(32),
app_id integer,
wall_post_id integer,
foreign key (app_id) references apps on delete cascade,
foreign key (wall_post_id) references content_items on delete set
null,
primary key (id)
);
CREATE OR REPLACE FUNCTION content_item_deleted_trigger() RETURNS trigger
AS $$
BEGIN
DELETE FROM
content_items
WHERE
content_items.id =3D OLD.wall_post_id;
RETURN OLD;
END
$$ LANGUAGE plpgsql;
-- the problem is in this trigger, if I make it an "AFTER" it works as it
should
CREATE TRIGGER content_item_deleted_trigger BEFORE DELETE ON
content_items
FOR EACH ROW
WHEN (OLD.wall_post_id is not null)
EXECUTE PROCEDURE content_item_deleted_trigger();
COMMIT;
-- End of schema setup
BEGIN;
INSERT INTO apps (type) VALUES ('basecamp');
INSERT INTO content_items (type, app_id, wall_post_id)
VALUES ('wall_post', NULL, NULL);
INSERT INTO content_items (type, app_id, wall_post_id)
VALUES ('basecamp_post', 1, 1);
COMMIT;
-- End of setup
BEGIN;
DELETE FROM apps WHERE apps.id =3D 1;
COMMIT;
-- This select still sees 1 item that refers to an app that does not exist
anymore
BEGIN;
SELECT count(*) from content_items;
SELECT count(*) from apps;
ROLLBACK;
I think being able to generate rows that do not pass constraints on a table
is a bug.
