The following bug has been logged online:
Bug reference: 5770
Logged by: Martin Edlman
Email address: edl...@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)=('t...@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','t...@mail.com')
-- output
-- NOTICE: insert into tmp.amavis_user(id, email, priority, policy_id)
values (15, t...@mail.com, 6, 1)
-- ERROR: insert or update on table "amavis_user" violates foreign key
constraint "amavis_user_email_fkey"
-- DETAIL: Key (email)=('t...@mail.com') is not present in table
"mail_account".
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
