pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHello & create rule question
2+ messages / 2 participants
[nested] [flat]
* Hello & create rule question
@ 1999-03-05 16:31 Emils Klotins <emils@mail.usis.bkc.lv>
1999-03-05 16:47 ` Re: [SQL] Hello & create rule question Jan Wieck <jwieck@debis.com>
0 siblings, 1 reply; 2+ messages in thread
From: Emils Klotins @ 1999-03-05 16:31 UTC (permalink / raw)
To: pgsql-sql
Hello!
I have just joined this mailing list, so please do not become frustrated, if I happen to ask
something that has already been answered recently.
There is a thing, which didn't work as I expected, so maybe you could tell me, what have I done
wrong?
There are 2 tables:
museums (museum_id int2, description text, address_id int2)
addresses (address_id int2, address text);
(they are actually a bit different, the museums.museum_id & address.address_id being unique
indices with default values from sequences etc.)
I want to delete museum's address from addresses when I delete something from museums.
So I thought to do it with a rule:
CREATE RULE museumdel ON DELETE TO museums DO DELETE FROM addresses WHERE
addresses.address_id=current.address_id;
Now, when I do DELETE FROM museums, the psql monitor displays DELETE 0 and when I
check the tables, the proper entry from museums IS deleted, but the associated addresses row
is not.
What am I doing wrong?
Thanks in advance for any ideas & tips!
Emils Klotins e-mail: emils@mail.usis.bkc.lv
Systems Manager URL: http://www.usis.bkc.lv/
USIS Riga 7 Smilsu Str., Riga LV1050, LATVIA
......................................................
RSA PGP key published on: http://pgpkeys.mit.edu:11371
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Hello & create rule question
1999-03-05 16:31 Hello & create rule question Emils Klotins <emils@mail.usis.bkc.lv>
@ 1999-03-05 16:47 ` Jan Wieck <jwieck@debis.com>
0 siblings, 0 replies; 2+ messages in thread
From: jwieck@debis.com @ 1999-03-05 16:47 UTC (permalink / raw)
To: emils@mail.usis.bkc.lv; +Cc: pgsql-sql
Emils Klotins wrote:
> [...]
>
> museums (museum_id int2, description text, address_id int2)
> addresses (address_id int2, address text);
>
> CREATE RULE museumdel ON DELETE TO museums DO DELETE FROM addresses WHERE
> addresses.address_id=current.address_id;
>
> Now, when I do DELETE FROM museums, the psql monitor displays DELETE 0 and when I
> check the tables, the proper entry from museums IS deleted, but the associated addresses row
> is not.
>
> What am I doing wrong?
The fault is that you're using v6.3.2. The above cannot work
before v6.4.
Details on that:
Let's say you're doing
DELETE FROM museums WHERE museum_id < 100;
The additional query generated by the rule system to delete
the addresses would look like
DELETE FROM addresses
WHERE museums.museum_id < 100
AND addresses.address_id = museums.museum_id;
The bug in v6.3.2 is, that this additional query is executed
AFTER the delete from museum and between these two queries a
command counter increment is done by the traffic cop. Thus,
at the time the addresses should get deleted, there are no
museums with id < 100 left and the above DELETE will not find
anything to delete. That's why you're seeing the
DELETE 0
response, because the response sent to the client is allways
the one from the last executed query. If you try (on your old
installation) to use the following (also not working) rule
CREATE RULE museumdel ON DELETE TO museums
DO UPDATE addresses SET address = 'deleted'
WHERE address_id = old.address_id;
you should see that the response to a delete from museums
becomes
UPDATE 0
You must upgrade to a past v6.4 version to use rules ON
UPDATE/DELETE. The last bugfix release on the v6.4 tree,
v6.4.3 will be out in a few days.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-03-05 16:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-05 16:31 Hello & create rule question Emils Klotins <emils@mail.usis.bkc.lv>
1999-03-05 16:47 ` Jan Wieck <jwieck@debis.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