-----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 ...
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 ...
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-----


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

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+bbhG3MVih0acZvzuqGJ9Kzg CfftZ5
DsPMUXkkwX1heP7VdN3aTro=
=YIt6
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Fri, 5 Sep 2003, Martin Edlman wrote:

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).



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

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

The functions replacement should be ok.

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.

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-----


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html