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

"Sorry, but no coments about this?

Tom?

regards,
Sergio."

Sergio Pili wrote:
> 
> Hi!!
> 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;
> 
> INSERT INTO A VALUES (1,1,2);
> INSERT INTO A VALUES (2,2,2);
> INSERT INTO A VALUES (3,1,2);
> 
> INSERT INTO B VALUES (100,1,2);
> INSERT INTO B VALUES (110,1,2);
> INSERT INTO B VALUES (120,2,2);
> INSERT INTO B VALUES (130,2,2);
> 
> UPDATE B SET a=4, b=4
> WHERE a=1 and b=2;
> #ERROR: “There are not records with a=4 b=4 in table A”
> 
> (OK!!)
> 
> UPDATE A SET a=4,b=4
> WHERE a=1 and b=2;
> #ERROR: “There are not records with a=4 b=4 in table A”
> 
> (we don’t want this ...)
> 
> Well, if we replace upd_a by
> 
> CREATE RULE upd_a AS ON UPDATE TO A
> DO
> (
> DEACTIVATE RULE upd_b;
> UPDATE B SET a = NEW.a, b = NEW.b
> WHERE a = OLD.a
> AND b = OLD.b;
> )
> 
> UPDATE A SET a=4,b=4
> WHERE a=1 and b=2;
> 
> #2 rows updated
> 
> SELECT * FROM A;
> 
> 1       4       4
> 2       2       2
> 3       4       4
> 
> SELECT * FROM B;
> 
> 100     4       4
> 110     4       4
> 120     2       2
> 130     2       2
> 
> (OK!)
> 
> regards,
> Jorge H. Doorn. Full professor
> Laura C. Rivero. Associate professor.
> 
> Tom Lane wrote:
> >
> > Sergio Pili <sergiop@sinectis.com.ar> writes:
> > >> A) It is related with situations where more than one rule is involved
> > >> and the seccond one requires completion of the first one. In our sort
> > >> of problems this happens frequently. This can be solved adding the
> > >> notion of "disablement" of the first rule within the re-writing of
> > >> the second rule when the first rule is not required since the
> > >> knowledge of the action of the second rule allows it. To do this, the
> > >> addition of two new commands is proposed: DEACTIVATE/ACTIVATE RULE.
> >
> > You haven't made a case at all for why this is a good idea, nor whether
> > the result couldn't be accomplished with some cleaner approach (no,
> > I don't think short-term disablement of a rule is a clean approach...)
> > Please give some examples that show why you think such a feature is
> > useful.
> >
> > >> B) The lack of a transaction abortion clause. (Chapter 17 Section 5
> > >> PostgreSQL 7.1 Programmer’s Guide)
> > >> The addition of the function
> > >> pg_abort_with_msg(text)
> > >> wich can be called from a SELECT is proposed.
> >
> > This seems straightforward enough, but again I'm bemused why you'd want
> > such a thing.  Rules are sufficiently nonprocedural that it's hard to
> > see the point of putting deliberate error traps into them --- it seems
> > too hard to control whether the error occurs or not.  I understand
> > reporting errors in procedural languages ... but all our procedural
> > languages already have error-raising mechanisms.  For example, you could
> > implement this function in plpgsql as
> >
> > regression=# create function pg_abort_with_msg(text) returns int as
> > regression-# 'begin
> > regression'#    raise exception ''%'', $1;
> > regression'#    return 0;
> > regression'# end;' language 'plpgsql';
> > CREATE
> > regression=# select pg_abort_with_msg('bogus');
> > ERROR:  bogus
> > regression=#
> >
> > Again, a convincing example of a situation where this is an appropriate
> > solution would go a long way towards making me see why the feature is
> > needed.
> >
> >                         regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly