Re: WAS: [Fwd: PostgreSQL new commands proposal]

On Thu, 15 Nov 2001, Sergio Pili wrote:

> We are developing a project at the Universidad Nacional del Centro, in
> Argentina. Sergio Pili, who has communicated with you previously, is
> working with us. We are interested in the feature he is implementing:
> rule activation and deactivation.
>
> With respect to the safeness of this deactivation, we can say that:
>
> - It can be executed just only from the action of the rule.
> - The deactivated rule continues deactivated while the rewriting of the
> query which executed that deactivation is done. This means that the
> deactivation does not affect other queries. Moreover, the rule is
> automatically reactivated when the rewrite process is finished.
> - This feature avoids recursive activation.
>
> Example:
>
> CREATE TABLE A (aa int primary key, a int, b int);
> CREATE TABLE B (bb int primary key,a int, b int);
>
> CREATE RULE upd_b AS ON UPDATE TO B
> WHERE
>         NOT EXISTS (SELECT *
>         FROM A
>         WHERE  A.a = NEW.a
>                 AND A.b = NEW.b )
> DO INSTEAD
>         SELECT pg_abort_with_msg('No existen registros con a = '||
>         NEW.a || ' b = ' || NEW.b || ' en la tabla A');
>
> CREATE RULE upd_a AS ON UPDATE TO A
> DO
> UPDATE B SET a = NEW.a, b = NEW.b
> WHERE a = OLD.a
> AND b = OLD.b;

Since you asked for comments, I don't think this is
a terribly compelling example. It looks alot like a
multicolumn foreign key with on update cascade to
me except that it's defined against a non-unique
key (meaning the update rule may not do what you really
want if there are duplicate rows in a that are matched),
the error message is more specific, and it looks less
transaction safe than the current foreign key
implementation (imagine one transaction deleting
a row in A and another updating B to point to that
row).  Also, turning off the rule in this case is
wrong, since if something else (a before trigger
for example) modifies the row in A before it's inserted
I'm pretty sure you end up with a row in B that
doesn't match. I think there are probably useful
applications of turning off rule expansion, but
this isn't it.