The following bug has been logged on the website:

Bug reference: 6632
Logged by: Ignas Mikalajunas
Email address: ignas (AT) pow (DOT) lt
PostgreSQL version: 9.1.3
Operating system: Ubuntu 11.11
Description:

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 = 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 = 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.


--
Sent via pgsql-bugs mailing list (pgsql-bugs (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs