BUG #5770: Foreign key violation after insert

The following bug has been logged online:

Bug reference:      5770
Logged by:          Martin Edlman
Email address:      edlman@fortech.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@mail.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@mail.com')

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