Re: Foreign key not working in some cases when using triggers

> I have problem with foreign keys on postgresql-7.3.2-1PGDG.
>
> I have two tables, sorry for the lenght, but I want to give you complete
> information.
>
> Table qmail_account has FK on zone(name). On some occasions when I want
> to insert data to qmail_account I get the RI error that the value of
> qmail_account.domain was not found in zone.name. I'm sure it is there.
> Maybe there is some problem with index zone_name_key.
> Originaly I had this problem with zone.name and qmail_account.domain
> defined as varchar(100), so I changed it to char(100), but it didn't help.
> This problem occurs on some domains only - but on these domains it gives
> the error every time.
> I realized that when I insert the data with the "on-insert-working" domain
> and then update it to the "on-insert-not-working" domain, the update
> works.
> As I have a trigger on qmail_account which checks some values and sets
> them if they are empty. "home" is filled different ways for public and
> private domains, so I use select query to the "zone".
> When I specify "home" at insert, it works even with domains it failed with
> empty and then auto-filled "home".
>
> The trigger is run before insert/update on qmail_account. The trigger
> doesn't change the value of NEW.domain in any way.
>
> So I'd like to know if there is something I can do to make the insert
> working with the trigger.

If possible, can you give a complete example script that shows the
problem?