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(at)sinectis(dot)com(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly