X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 881AA53975 for ; Tue, 10 May 2005 01:32:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61618-07 for ; Tue, 10 May 2005 04:32:29 +0000 (GMT) Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by svr1.postgresql.org (Postfix) with ESMTP id C0ACF53968 for ; Tue, 10 May 2005 01:32:27 -0300 (ADT) Received: from ausbayes1.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Mon, 09 May 2005 23:32:26 -0500 Received: from BLRMAIL1.aus.pervasive.com ([10.3.16.60]) by ausbayes1.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Mon, 9 May 2005 23:32:22 -0500 content-class: urn:content-classes:message Subject: Re: CASCADE and TRIGGER - Some weird problem Date: Tue, 10 May 2005 10:02:16 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-15" Content-Transfer-Encoding: quoted-printable Message-ID: <02767D4600E59A4487233B23AEF5C5992A4087@blrmail1.aus.pervasive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: X-MIMEOLE: Produced By Microsoft Exchange V6.0.6603.0 Thread-Topic: [SQL] CASCADE and TRIGGER - Some weird problem Thread-Index: AcVSvxArL/X4w6zEQdqJC15+erl0swCWfbHQ From: "Ramakrishnan Muralidharan" To: "Sonic" , X-OriginalArrivalTime: 10 May 2005 04:32:24.0908 (UTC) FILETIME=[43BF48C0:01C55519] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.208 tagged_above=0 required=5 tests=AWL, EXCUSE_3, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200505/122 X-Sequence-Number: 21435 Hi, The issue is due to records in Account_message is still exists for the = records which are going to be deleted from the Message table. Please = check the sequence of deleting the records. When I tried to delete a record using your example, the following = exception is raised. ERROR: update or delete on "message" violates foreign key constraint = "account_message__msg_fkey" on "account_message" DETAIL: Key (_message_id)=3D(2) is still referenced from table = "account_message". Regards, R.Muralidharan -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Sonic Sent: Wednesday, May 04, 2005 1:01 PM To: pgsql-sql@postgresql.org Subject: [SQL] CASCADE and TRIGGER - Some weird problem -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 hi, have a little problem with a trigger and the ON DELETE CASCADE statement. i'm working on a db that represents Users and Messages. A message can be owned by more than a user. if i delete a user all his related objects are deleted too (ON DELETE CASCADE), but if a message that this user owns is also owned by another user, it has not to be deleted. i just put ON DELETE CASCADE statement on foreign keys and thos seem to work. then i wrote a trigger to check if each message the user owns is owned by someone else. if it's not delete it! - ----------------------------------------------------------- - -----this is the code: CREATE TABLE OWNER( _LOGIN TEXT, PRIMARY KEY(_LOGIN) ); CREATE TABLE MESSAGE( _MESSAGE_ID TEXT, PRIMARY KEY(_MESSAGE_ID) ); CREATE TABLE ACCOUNT( _INDIRIZZO TEXT UNIQUE, _LOGIN TEXT, PRIMARY KEY(_INDIRIZZO,_LOGIN), FOREIGN KEY(_LOGIN)REFERENCES OWNER(_LOGIN) ON DELETE CASCADE); CREATE TABLE ACCOUNT_MESSAGE( _MSG TEXT, _INDIRIZZO TEXT, PRIMARY KEY(_MSG,_INDIRIZZO), FOREIGN KEY(_MSG)REFERENCES MESSAGE(_MESSAGE_ID), FOREIGN KEY(_INDIRIZZO)REFERENCES ACCOUNT(_INDIRIZZO) ON DELETE = CASCADE); CREATE TABLE FOLDER( _PATH TEXT, _OWNER TEXT, PRIMARY KEY(_PATH), FOREIGN KEY(_OWNER)REFERENCES OWNER(_LOGIN) ON DELETE CASCADE); CREATE TABLE MSG_IN_FOLDER( _MSG TEXT, _FOLDER TEXT, PRIMARY KEY(_MSG,_FOLDER), FOREIGN KEY(_MSG)REFERENCES MESSAGE(_MESSAGE_ID), FOREIGN KEY(_FOLDER)REFERENCES FOLDER(_PATH) ON DELETE CASCADE); CREATE FUNCTION check_message() RETURNS trigger AS $check_MESSAGE$ BEGIN DELETE FROM MESSAGE WHERE _message_id IN ( SELECT _MSG = =09 FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER WHERE _MSG =3D OLD._MSG GROUP BY _MSG HAVING count(*)=3D1 ); RAISE NOTICE 'Value of OLD._MSG %', OLD._MSG; RETURN NULL; END; $check_MESSAGE$ LANGUAGE plpgsql; CREATE TRIGGER check_message AFTER DELETE ON MSG_IN_FOLDER FOR EACH ROW EXECUTE PROCEDURE check_message(); - ------------------------------------------------- - --and these are some values: delete from OWNER; delete from ACCOUNT; delete from MESSAGE; delete from ACCOUNT_MESSAGE; delete from FOLDER; delete from MSG_IN_FOLDER; insert into OWNER (_login) values ('anna'); insert into OWNER (_login) values ('paolo'); insert into ACCOUNT values ('anna@gmail.com', 'anna'); insert into ACCOUNT values ('paolo@gmail.com', 'paolo'); insert into MESSAGE (_message_id) values ('1'); insert into MESSAGE (_message_id) values ('2'); insert into ACCOUNT_MESSAGE values ('1', 'anna@gmail.com'); insert into ACCOUNT_MESSAGE values ('1', 'paolo@gmail.com'); insert into ACCOUNT_MESSAGE values ('2', 'anna@gmail.com'); insert into FOLDER (_path, _OWNER) values ('c', 'anna'); insert into MSG_IN_FOLDER values ('1', 'c'); insert into MSG_IN_FOLDER values ('2', 'c'); select * from MESSAGE; - ---------- as you see there are 2 messages. message 1 is owned both by 'anna' and 'paolo'. message 2 is owned just by 'anna'. now what i want is that if i delete user 'anna' just message 2 is = deleted. i guess i've done that with my trigger: ... CREATE FUNCTION check_message() RETURNS trigger AS $check_MESSAGE$ BEGIN DELETE FROM MESSAGE WHERE _message_id IN ( SELECT _MSG = =09 FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER WHERE _MSG =3D OLD._MSG GROUP BY _MSG HAVING count(*)=3D1 ); RAISE NOTICE 'Value of OLD._MSG %', OLD._MSG; RETURN NULL; END; $check_MESSAGE$ LANGUAGE plpgsql; CREATE TRIGGER check_message AFTER DELETE ON MSG_IN_FOLDER FOR EACH ROW EXECUTE PROCEDURE check_message(); ... i'm also sure that values it process are right beacuse of that RAISE NOTICE 'Value of OLD._MSG %', OLD._MSG; but something seems to go wrong. all 'anna' stuff is deleted but not her message 2 ! DELETE FROM owner WHERE _login =3D 'anna'; SELECT * FROM message; can someone help me to understand why this is not done? i don't receive any error message from postgres... thanks paolo from italy sonicaaaa@gmail.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (MingW32) iD8DBQFCeHpD8gTT7JZTWqIRAvc6AJ9yqXq4EOP+JZ4NJ+pekiwuqko0XACeMz/8 DBZdQevWM1emBodYH5QP0G4=3D =3DV6VW -----END PGP SIGNATURE----- ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if = your joining column's datatypes do not match