WAS: [Fwd: PostgreSQL new commands proposal] - Mailing list pgsql-hackers

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
tablaA');
 

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