Deactivate Rules

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.