pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCASCADE and TRIGGER - Some weird problem
2+ messages / 2 participants
[nested] [flat]
* CASCADE and TRIGGER - Some weird problem
@ 2005-05-04 07:31 Sonic <sonicaaaa@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Sonic @ 2005-05-04 07:31 UTC (permalink / raw)
To: pgsql-sql
-----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
FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER
WHERE _MSG = OLD._MSG
GROUP BY _MSG
HAVING count(*)=1
);
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
FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER
WHERE _MSG = OLD._MSG
GROUP BY _MSG
HAVING count(*)=1
);
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 = '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=
=V6VW
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: CASCADE and TRIGGER - Some weird problem
@ 2005-05-10 04:32 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 0 replies; 2+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-05-10 04:32 UTC (permalink / raw)
To: Sonic <sonicaaaa@gmail.com>; pgsql-sql
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)=(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
FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER
WHERE _MSG = OLD._MSG
GROUP BY _MSG
HAVING count(*)=1
);
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
FROM ACCOUNT_MESSAGE NATURAL JOIN msg_in_FOLDER
WHERE _MSG = OLD._MSG
GROUP BY _MSG
HAVING count(*)=1
);
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 = '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=
=V6VW
-----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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-05-10 04:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-04 07:31 CASCADE and TRIGGER - Some weird problem Sonic <sonicaaaa@gmail.com>
2005-05-10 04:32 Re: CASCADE and TRIGGER - Some weird problem Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox