Foreign key not working in some cases when using triggers - Mailing list pgsql-bugs

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

    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.


cust=# \d zone
                                     Table "public.zone"
   Column    |          Type          |           Modifiers
- -------------+------------------------+------------------------------------
 id          | integer                | not null default ...
             |                        | ... nextval('zone_id_seq'::text)
 contractid  | integer                | not null
 name        | character(100)         | not null
 public_mail | boolean                | not null default '0'
 public_web  | boolean                | not null default '0'
 ns1id       | integer                | not null default '0'
 ns2id       | integer                | default '0'
 ns3id       | integer                | default '0'
 mx0id       | integer                | not null default '0'
 mx10id      | integer                | default '0'
 mx100id     | integer                | default '0'
 expire      | date                   |
 extra       | text                   | default ''
 comment     | character varying(200) | default ''

Indexes: zone_pkey primary key btree (id),
         zone_name_key unique btree (name),
         zone_contractid_idx btree (contractid)

cust=# \d qmail_account
                            Table "public.qmail_account"
   Column      |             Type        |     Modifiers
- ---------------+-------------------------+----------------------------------
 id            | integer                 | not null default ...
               |                         | ... nextval('qmail_account_id_seq'::text)
 contractid    | integer                 | not null
 username      | character varying(50)   | not null
 password      | character varying(50)   |
 passwordenc   | boolean                 | default 'n'
 uid           | integer                 | default '8'
 gid           | integer                 | default '11'
 home          | character varying(100)  |
 hardquota     | integer                 | default '50'
 valid_from    | timestamp w/o time zone | default 'now()'
 valid_to      | timestamp w/o time zone |
 enabled       | boolean                 | default '1'
 use_dot_qmail | boolean                 | default '0'
 lastlogin     | timestamp w/o time zone |
 lastdelivery  | timestamp w/o time zone |
 comment       | character varying(200)  | default ''
 home_old      | character varying(100)  | default ''
 domain        | character(100)          | not null

Indexes: qmail_account_pkey primary key btree (id),
         qmail_account_email_idx unique btree (username, "domain"),
         qmail_account_contractid_idx btree (contractid),
         qmail_account_username_idx btree (username)

Foreign Key constraints: $2 FOREIGN KEY ("domain") REFERENCES "zone"(name)
                            ON UPDATE CASCADE ON DELETE NO ACTION,
                         $1 FOREIGN KEY (contractid) REFERENCES
                            contract(id) ON UPDATE NO ACTION ON DELETE NO
                            ACTION
Triggers: log,
          qmail_account_check


- --

Martin Edlman
Fortech s.r.o, Litomysl
Public PGP key: http://edas.visaci.cz/#keys

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/Vu/FoFlEKJy9loQRAvxiAJ9An+DuvgFh/jyXgCDQrHoDS/s9YQCeL1uF
t/2b7ZRSN+xK6tWSFZPe16o=
=anYf
-----END PGP SIGNATURE-----