From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Sergio Pili <sergiop(at)sinectis(dot)com(dot)ar> |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: WAS: [Fwd: PostgreSQL new commands proposal] |
Date: | 2001-11-26 14:36:13 |
Message-ID: | 20011126060937.P10034-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Alessio Bragadini | 2001-11-26 14:36:38 | 7.2beta3 on Digital Alpha |
Previous Message | Teodor Sigaev | 2001-11-26 13:42:11 | Pls, apply patch fot contrib/tsearch |