BUG #4828: Fault a foreign key

The following bug has been logged online:

Bug reference: 4828
Logged by: alex
Email address: alexafanasjev(at)yandex(dot)ru
PostgreSQL version: PostgreSQL8.3.7
Operating system: Windows XP 2002 SP2
Description: Fault a foreign key
Details:

Run in psql:

--DROP DATABASE test_fk;
CREATE DATABASE test_fk WITH TEMPLATE = template0;

ALTER DATABASE test_fk OWNER TO postgres;
\connect test_fk

CREATE PROCEDURAL LANGUAGE plpgsql;

ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

CREATE TABLE tbl11 (
id_tbl1 serial NOT NULL,
cl1 integer NOT NULL,
naim text
);

CREATE TABLE tbl12 (
id_tbl2 serial NOT NULL,
cl1 integer NOT NULL,
kod character(1)
);

ALTER TABLE public.tbl12 OWNER TO postgres;

CREATE FUNCTION tr_f1() RETURNS trigger
AS $$
BEGIN
IF NEW.kod = 'a' THEN
NEW.cl1 = OLD.cl1;
END IF;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE FUNCTION tr_f2() RETURNS trigger
AS $$
BEGIN
IF OLD.kod = 'c' THEN
RETURN NULL;
END IF;
RETURN OLD;
END;
$$
LANGUAGE plpgsql;

--

INSERT INTO tbl11(cl1, naim) VALUES ( 1, '1/1');
INSERT INTO tbl11(cl1, naim) VALUES ( 2, '1/2');

INSERT INTO tbl12( cl1, kod) VALUES ( 1, 'a');
INSERT INTO tbl12( cl1, kod) VALUES ( 1, 'b');
INSERT INTO tbl12( cl1, kod) VALUES ( 2, 'c');
INSERT INTO tbl12( cl1, kod) VALUES ( 2, 'd');

ALTER TABLE ONLY tbl11 ADD CONSTRAINT sui UNIQUE (cl1);

CREATE TRIGGER tr1
BEFORE UPDATE ON tbl12
FOR EACH ROW
EXECUTE PROCEDURE tr_f1();

CREATE TRIGGER tr2
BEFORE DELETE ON tbl12
FOR EACH ROW
EXECUTE PROCEDURE tr_f2();

ALTER TABLE ONLY tbl12 ADD CONSTRAINT fk FOREIGN KEY (cl1) REFERENCES
tbl11(cl1) ON UPDATE CASCADE ON DELETE CASCADE;

Run in psql:
UPDATE tbl11 SET cl1=5 WHERE cl1=1;
DELETE FROM tbl11 WHERE cl1=2;
SELECT * FROM tbl12 WHERE not exists(select 1 from tbl11 WHERE tbl11.cl1 =
tbl12.cl1);