pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule problem
4+ messages / 4 participants
[nested] [flat]
* Rule problem
@ 1999-02-10 21:34 pierre@desertmoon.com
0 siblings, 0 replies; 4+ messages in thread
From: pierre@desertmoon.com @ 1999-02-10 21:34 UTC (permalink / raw)
To: pgsql-sql
All,
given this rule:
create rule add_upref_os as on insert to upref_os do
update gen_stats set total = total + 1 where os_id=current.os_id;
and this insert....
insert into upref_os values(1234,2);
gives me this error continuously until I cancel the query:
NOTICE: Non-functional update, only first update is performed
NOTICE: Non-functional update, only first update is performed
NOTICE: Non-functional update, only first update is performed
I'm running 6.4. Any ideas? I've got other rules that seem to function without
issues...
-=pierre
^ permalink raw reply [nested|flat] 4+ messages in thread
* RULE problem
@ 1999-03-02 22:06 Pascal Mueller <mueller@pmsun20.dial.eunet.ch>
0 siblings, 0 replies; 4+ messages in thread
From: mueller@pmsun20.dial.eunet.ch @ 1999-03-02 22:06 UTC (permalink / raw)
To: pgsql-sql
Hi
I have a problem using rules and do not know if I am wrong or if there
is a bug somewhere?
I have two tables A and B. I want to restrict deleting an instance of table
A if there is a instance in B referencing A.
I tried to set up a rule for this. Here's an example:
-- START EXAMPLE
-- **********************************************************************
-- * rule_test.sql *
-- * A test case for rule usage *
-- * *
-- * Last modification: 03/02/1999 pm, SWS *
-- **********************************************************************
-- Create table
CREATE TABLE a ( a INT PRIMARY KEY );
CREATE TABLE b ( b INT PRIMARY KEY, ref INT );
-- RULEs ----------------------------------------------------------------
-- Rules for database integrity.
-- Restrict REMOVE on A if there is still an entry in B referencing A
CREATE RULE demo AS ON DELETE TO a
WHERE EXISTS (
SELECT * FROM b
WHERE b.ref = CURRENT.a
)
DO INSTEAD NOTHING
;
-- FILL IN DATA ---------------------------------------------------------
INSERT INTO a VALUES ( '1' );
INSERT INTO a VALUES ( '2' );
INSERT INTO a VALUES ( '3' );
INSERT INTO b VALUES ( '1', '1' );
INSERT INTO b VALUES ( '2', '3' );
INSERT INTO b VALUES ( '3', '3' );
-- TRY TO REMOVE --------------------------------------------------------
DELETE FROM a WHERE a = '1'; -- Should not work because of b.1
DELETE FROM a WHERE a = '2'; -- Should work
DELETE FROM a WHERE a = '3'; -- Should not work because of b.2 and b.3
-- END EXAMPLE
The DELETE commands does not work and ends up in an ERROR in all 3 cases:
DELETE FROM a WHERE a = '1'; -- Should not work because of b.1
ERROR: ExecEvalExpr: unknown expression type 108
DELETE FROM a WHERE a = '2'; -- Should work
ERROR: ExecEvalExpr: unknown expression type 108
DELETE FROM a WHERE a = '3'; -- Should not work because of b.2 and b.3
ERROR: ExecEvalExpr: unknown expression type 108
And trying to understand what's going on I issued en EXPLAIN command,
crashing the backend:
pascal=> explain verbose delete from a;
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while
processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
What I'm doing wrong here? Any hint? Thanks for any help.
Pascal Mueller
^ permalink raw reply [nested|flat] 4+ messages in thread
* Rule problem
@ 2002-06-13 10:11 Svenn Helge Grindhaug <svenn@ii.uib.no>
2002-06-13 14:36 ` Re: Rule problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Svenn Helge Grindhaug @ 2002-06-13 10:11 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have defined the following 3 tables (a,b,c) and 2 rules (a_delete,
c_delete) in postgres 7.2:
Table "a"
Column | Type | Modifiers
--------+---------+-----------
id1 | integer | not null
Primary key: a_pkey
Rules: a_delete
Table "b"
Column | Type | Modifiers
--------+---------+-----------
id1 | integer |
id2 | integer |
Table "c"
Column | Type | Modifiers
--------+---------+-----------
id2 | integer | not null
Primary key: c_pkey
Rules: c_delete
create rule a_delete as
on delete to a do
delete from b where id1 = old.id1;
create rule c_delete as
on delete to c do
delete from a where id1 = (select id1 from b where id2 = old.id2);
The tables contains the following values:
select * from c;
id2
-----
104
105
106
select * from a;
id1
-----
1
2
3
select * from b;
id1 | id2
-----+-----
1 | 105
The problem is that when I try to delete an entry i table c which have a
reference in table b to table a, the rule deletes the entry in table c and
table b but NOT in table a. (see output below)
Can someone please tell me why this happens and how to solve it
delete from c where id2 = 105;
DELETE 1
select * from a;
id1
-----
1 <- This should also be deleted by the c_delete rule!!!
2
3
select * from b;
id1 | id2
-----+-----
(0 rows)
select * from c;
id2
-----
104
106
Thanks
Svenn Grindhaug.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rule problem
2002-06-13 10:11 Rule problem Svenn Helge Grindhaug <svenn@ii.uib.no>
@ 2002-06-13 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-06-13 14:36 UTC (permalink / raw)
To: Svenn Helge Grindhaug <svenn@ii.uib.no>; +Cc: pgsql-sql
Svenn Helge Grindhaug <svenn@ii.uib.no> writes:
> create rule a_delete as
> on delete to a do
> delete from b where id1 = old.id1;
> create rule c_delete as
> on delete to c do
> delete from a where id1 = (select id1 from b where id2 = old.id2);
The a_delete rule is run before the actual "DELETE FROM a" command,
so by the time the latter runs, the b row is gone, and no match exists.
I'd be inclined to think about doing this with foreign keys (set up
for ON DELETE CASCADE) instead of handmade rules. If you really want
to do it by hand, triggers will probably work better than rules.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-06-13 14:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-10 21:34 Rule problem pierre@desertmoon.com
1999-03-02 22:06 RULE problem Pascal Mueller <mueller@pmsun20.dial.eunet.ch>
2002-06-13 10:11 Rule problem Svenn Helge Grindhaug <svenn@ii.uib.no>
2002-06-13 14:36 ` Tom Lane <tgl@sss.pgh.pa.us>
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