Deactivate Rules - Mailing list pgsql-hackers
From | Sergio Pili |
---|---|
Subject | Deactivate Rules |
Date | |
Msg-id | 3AA7699E.16F22F3@sinectis.com.ar Whole thread Raw |
List | pgsql-hackers |
Hi! I am student of system engineering and I carried out a work with PostgreSQL in which I should implement inclusion dependencies using rules. During the development of this work, I met with a problem that I solved adding the Postgres the possibility to disable (deactivate) a rule temporarily. In what follows, I detail the problem and define the deactivation. If you are interested in this concept to incorporate it in the official version, I can send you a diff. Moreover, I suppose this concept may be useful in other contexts since STARBUST, for instance, has a similar sentence to activate/deactivate the rules. With the following tables: test=# create table table_a(a_cod int primary key,a_desc char); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'table_a_pkey' for table 'table_a' CREATE test=# create table table_b(b_cod int primary key,b_desc char,a_cod int); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'table_b_pkey' for table 'table_b' CREATE test=# insert into table_a values (1,'O'); INSERT 138026 1 test=# insert into table_a values (2,'T'); INSERT 138027 1 test=# insert into table_b values (100,'O',1); INSERT 138028 1 And the inclusion dependency defined as follows: table_b(a_cod) => table_a(a_cod) The UPDATE with "restricted" option can be implemented with the rule: CREATE RULE u_table_b_res AS ON UPDATE TO table_b WHERE (OLD.a_cod <> NEW.a_cod OR OLD.a_cod is NULL ) AND NEW.a_cod is not NULL AND NOT EXISTS (SELECT table_a.a_cod FROM table_a WHERE table_a.a_cod = new.a_cod ) DO INSTEAD select pg_abort_with_msg(new.a_cod||' NOT EXIST IN TABLE table_a'); -- pg_abort_with_msg(msg) is a function, that call elog(ERROR,msg) This rule works as expected but if I define a "cascade" action for table_b when table_a is updated: CREATE RULE u_table_a_cas AS ON UPDATE TO table_a DO update table_b set a_cod=New.a_cod where table_b.a_cod=OLD.a_cod; And I execute: test=# update table_a set a_cod=100 where a_cod=1; ERROR: 100 NOT EXIST IN TABLE table_a This result is no the expected one. This happens because a rewriting system characteristic: the queryTree of the rule u_table_b_res is executed in the first place and therefore the execution miscarries. To solve this problem I added to the grammar the sentences DEACTIVATE RULE rulename and REACTIVATE RULE rulename. The sentence DEACTIVATE RULE allows me to disable the rule u_table_b_res and then to avoid the interference. The sentence REACTIVATE RULE turns the rule in active state again. These new sentences don't reach the executor. DEACTIVATE only avoids the triggering of this rule during the rewriting process (i.e. the action is not included in the queryTree to be executed) and it only affects the current session. The rule remains disabled only during the rewriting phase of the original sentence (the UPDATE to table_a in this example) since the DEACTIVATE is detected (in fireRules, of rewriteHandler.c), until finding a REACTIVATE or until the end of the rule. With the new sentence the rule would be: CREATE RULE u_table_a_cas AS ON UPDATE TO table_a DO ( deactivate rule u_table_b_res; update table_b set a_cod=New.a_cod where table_b.a_cod=OLD.a_cod; ) It is necessary to keep in mind that the rule should only be disabled when its action is not longer necessary, like it is this case. A rule cannot be disabled indiscriminately, thus it is only possible to disable a rule if the user that creates the rule (in whose action the DEACTIVATE is executed) has "permission RULE" on the table owning the rule to be disabled. For the previous case, if 'userA' is the user that creates the rule 'u_table_a_cas', ' userA' should have "permission RULE" on ' table_a' and also on ' table_b' (that is the owner of the rule ' u_table_b_res') That is all. Thanks Sergio.
pgsql-hackers by date: