Thread: Foreign key not working in some cases when using triggers
-----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-----
> 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?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> If possible, can you give a complete example script that shows the
> problem?
Here it is.
CREATE OR REPLACE FUNCTION qmail_account_check() RETURNS TRIGGER AS '
-- do some check before insert/update on qmail_account
DECLARE
rec1 RECORD;
rec2 RECORD;
rec3 RECORD;
recx RECORD;
BEGIN
-- allow the script only before operation on qmail_account
IF NOT (TG_WHEN = ''BEFORE'' AND TG_RELNAME = ''qmail_account'') THEN
RAISE NOTICE ''qmail_account_check RETURNS NULL 1 for % @ %'',
NEW.username,NEW.domain;
RETURN NULL;
END IF;
-- INSERT, UPDATE
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
IF NEW.home ISNULL OR NEW.home = '''' THEN
-- get qmail base dir from table constants
NEW.home := get_constant(''qmailhome'');
IF NEW.home ISNULL THEN
RAISE EXCEPTION ''QMAIL home dir lookup failed on table constants'';
END IF;
SELECT INTO rec1 public_mail FROM zone WHERE name = NEW.domain AND
public_mail;
IF FOUND THEN -- public domain, create structured home dir
-- NEW.home/NEW.domain/u/us/username
RAISE NOTICE ''Zone % is public'', NEW.domain;
SELECT INTO rec2 NEW.home || ''/'' ||
btrim(NEW.domain,'' '')::varchar || ''/'' ||
substring(NEW.username from 1 for 1) ||
''/'' ||
substring(NEW.username from 1 for 2) ||
''/'' || NEW.username AS home;
NEW.home := rec2.home;
ELSE
-- private zone, set homedir to NEW.home/NEW.domain/username
RAISE NOTICE ''Zone % is private'', NEW.domain;
SELECT INTO rec3 NEW.home || ''/'' ||
btrim(NEW.domain,'' '')::varchar || ''/'' ||
NEW.username AS home;
NEW.home := rec3.home;
END IF;
END IF;
IF NEW.password ISNULL OR NEW.password = '''' THEN
NEW.password := generate_password();
END IF;
RETURN NEW;
END IF;
RAISE NOTICE ''qmail_account_check RETURNS NULL 2 for % @ %'',
NEW.username,NEW.domain;
RETURN NULL;
END
' LANGUAGE 'plpgsql';
- --
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/WCDmoFlEKJy9loQRAhzLAJ0X5y+bbhG3MVih0acZvzuqGJ9KzgCfftZ5
DsPMUXkkwX1heP7VdN3aTro=
=YIt6
-----END PGP SIGNATURE-----
On Fri, 5 Sep 2003, Martin Edlman wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > If possible, can you give a complete example script that shows the > > problem? > > Here it is. > Unfortunately the trigger depends on some other functions that I dind't have so I replaced those with string constants. I couldn't get it to fail with a little bit of playing around on my 7.3.4 box, however. If you set up a new database with the tables and triggers involved, can you get it to fail with some test data, and if so, can you send a dump of the new database along with the command you ran to get it to fail? If you don't get it to fail, it might be worth trying to reindex the index on zone(name).
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Unfortunately the trigger depends on some other functions that I dind't > have so I replaced those with string constants. I couldn't get it to fail > with a little bit of playing around on my 7.3.4 box, however. The functions replacement should be ok. > If you set up a new database with the tables and triggers involved, can > you get it to fail with some test data, and if so, can you send a dump of > the new database along with the command you ran to get it to fail? When I have some spare time I'll try to do it, but now I'm very busy. The whole database is much bigger with more complex RI/FK, so it would be interresting if the same problematic data I have in the tables zone and qmail_account will work on the stripped database. > If you don't get it to fail, it might be worth trying to reindex the index > on zone(name). I reindexed it several times. I didn't help. Regards, Martin - -- 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/WDSUoFlEKJy9loQRAsl/AJ4gYQtQrNNMG8PxjKJAr0DYHUZbHwCdGgks ljxSijx6g2NOeA90N3MN/m8= =CBtj -----END PGP SIGNATURE-----