BUG #5770: Foreign key violation after insert

The following bug has been logged online:

Bug reference: 5770
Logged by: Martin Edlman
Email address: edlman(at)fortech(dot)cz
PostgreSQL version: 9.0.1
Operating system: Scientific Linux 5.5 (RHEL)
Description: Foreign key violation after insert
Details:

Hello,

I have two tables with RI/FK. There is a AFTER INSERT trigger on a master
table (mail_account) which inserts a record to a slave table (amavis_user).
But I get an error message
ERROR: insert or update on table "amavis_user" violates foreign key
constraint "amavis_user_email_fkey"
DETAIL: Key (email)=('test(at)mail(dot)com') is not present in table
"mail_account".

I encountered this problem during migration of the database from PgSQL 8.4
(where it works) to PgSQL 9.0.1.

I tried to set the FK constraint DEFERRABLE INITIALLY DEFERRED, I tried to
CREATE CONSTRAINT TRIGGER ... DEFERRABLE INITIALLY DEFERRED, I tried to SET
CONSTRAINTS tmp.amavis_user_email_fkey DEFERRED inside the trigger function
... all combinations - none of these helped.

Is it a bug or am I doing something wrong? It worked in 8.4 as I wrote.

The database migration is stuck on this. Please give me a hint or advice.

Regards, Martin E.

Here are the tables and trigger definitions:

-- trigger function
CREATE OR REPLACE FUNCTION tmp.mail_account_to_amavis_user() RETURNS trigger
AS
$BODY$
DECLARE
prio INTEGER;
BEGIN
IF NEW.username = 'alias' THEN
prio := 3;
ELSE
prio := 6;
END IF;

RAISE NOTICE 'insert into tmp.amavis_user(id, email, priority, policy_id)
values (%, %, %, 1)',
NEW.id, NEW.email, prio;

SET CONSTRAINTS tmp.amavis_user_email_fkey DEFERRED;

INSERT INTO tmp.amavis_user (id, email, priority, policy_id)
VALUES (NEW.id, quote_literal(NEW.email), prio, 1);

RETURN NEW;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION tmp.mail_account_to_amavis_user() OWNER TO import;

-- mail account table
CREATE TABLE tmp.mail_account
(
id serial NOT NULL,
username character varying(50) NOT NULL,
"password" character varying(50) NOT NULL,
email character varying(255),
uid integer DEFAULT 8,
gid integer DEFAULT 11,
home character varying(100),
CONSTRAINT mail_account_pkey PRIMARY KEY (id),
CONSTRAINT mail_account_email UNIQUE (email)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tmp.mail_account OWNER TO import;

-- trigger to insert a record to amavis_user
CREATE CONSTRAINT TRIGGER amavis_user
AFTER INSERT
ON tmp.mail_account
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE PROCEDURE tmp.mail_account_to_amavis_user();

-- table amavis user
CREATE TABLE tmp.amavis_user
(
id serial NOT NULL,
priority integer NOT NULL DEFAULT 7,
policy_id integer,
email character varying(255) NOT NULL,
CONSTRAINT amavis_user_pkey PRIMARY KEY (id),
CONSTRAINT amavis_user_email_fkey FOREIGN KEY (email)
REFERENCES tmp.mail_account (email) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
DEFERRABLE INITIALLY DEFERRED
)
WITH (
OIDS=FALSE
);

-- insert data to mail_account
insert into tmp.mail_account(username,password,email) values
('test','pwd','test(at)mail(dot)com')

-- output
-- NOTICE: insert into tmp.amavis_user(id, email, priority, policy_id)
values (15, test(at)mail(dot)com, 6, 1)
-- ERROR: insert or update on table "amavis_user" violates foreign key
constraint "amavis_user_email_fkey"
-- DETAIL: Key (email)=('test(at)mail(dot)com') is not present in table
"mail_account".