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

Thu, 15 Nov 2001 16:35:02 -0300
Sergio Pili <sergiop@sinectis.com.ar>
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

Mon, 19 Nov 2001 14:38:26 -0300
Sergio Pili <sergiop@sinectis.com.ar>
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.



Mon, 26 Nov 2001 06:36:13 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: WAS: [Fwd: PostgreSQL new commands proposal]
> 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),

Good, that is exactly what is. It is a case of inclusion dependence. The
inclusion dependences can be based on key (foreign key) or not based on
key.
The implementation of the cases of inclusion dependences not based on
key (as well as other types of dependences) not still been standardized
and they are study matter in the academic atmospheres. If you are
interested, I can mention bibliography and references on these topics.
The specification of this type of dependences is not supported by any
DBMS.

> 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 donï¿½t know if I have understood well but these rules single was an
example in which was useful and necessary the deactivation of a rule.
For the complete control of the inclusion dependence it is necessary
also to create rules that control the deletes on A and the inserts on B.
If this explanation doesn't satisfy you, please explain to me with an
example the problem that you are mentioning.

> I think there are probably useful
> applications of turning off rule expansion, but
> this isn't it.

Another application of the deactivation would be the possibility to
avoid the recursion, for example for the same case of the inclusion
dependence, it would be possible to make:

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 (DEACTIVATE RULE upd_b;
UPDATE B SET a = NULL, b = NULL
WHERE bb = OLD.bb;)

Rule that it would implement a possible "SET NULL" for an update on B.
I suppose that avoiding the recursiï¿½n could still have a much wider use.

Many Thanks for the coments!

best regards,

Sergio.

Mon, 26 Nov 2001 19:35:21 -0300
Sergio Pili <sergiop@sinectis.com.ar>
Re: WAS: [Fwd: PostgreSQL new commands proposal]
On Mon, 26 Nov 2001, Sergio Pili wrote:

> > 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),
>
> Good, that is exactly what is. It is a case of inclusion dependence. The
> inclusion dependences can be based on key (foreign key) or not based on
> key.
>
> The implementation of the cases of inclusion dependences not based on
> key (as well as other types of dependences) not still been standardized
> and they are study matter in the academic atmospheres. If you are
> interested, I can mention bibliography and references on these topics.
> The specification of this type of dependences is not supported by any
> DBMS.

I'd always be interested in interesting documents. :)

> > 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 don�t know if I have understood well but these rules single was an
> example in which was useful and necessary the deactivation of a rule.
> For the complete control of the inclusion dependence it is necessary
> also to create rules that control the deletes on A and the inserts on B.
> If this explanation doesn't satisfy you, please explain to me with an
> example the problem that you are mentioning.

The delete/update things is:
transaction 1 starts
transaction 2 starts
transaction 1 deletes a row from A
 -- There are no rows in B that can be seen by
 -- this transaction so you don't get any deletes.
transaction 2 updates a row in B
 -- The row in A can still be seen since it
 -- hasn't expired for transaction 2
transaction 1 commits
transaction 2 commits

The trigger thing is (I'm not 100% sure, but pretty sure this
is what'll happen - given that a test rule with a
function that prints a debugging statement gave me the
originally specified value not the final value)
transaction 1 starts
 you say update A key to 2,2
 - does cascade update of B as rule expansion to 2,2
 - before trigger on A sets NEW.key to 3,3
 - the row in A actually becomes 3,3
You'd no longer be checking the validity of the value
of B and so you'd have a broken constraint.


> > I think there are probably useful
> > applications of turning off rule expansion, but
> > this isn't it.
>
> Another application of the deactivation would be the possibility to
> avoid the recursion, for example for the same case of the inclusion
> dependence, it would be possible to make:
>
> 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 (DEACTIVATE RULE upd_b;
> UPDATE B SET a = NULL, b = NULL
> WHERE bb = OLD.bb;)
>
> Rule that it would implement a possible "SET NULL" for an update on B.
> I suppose that avoiding the recursi�n could still have a much wider use.

All in all I think you'd be better off with triggers than rules, but I
understand what you're trying to accomplish.


Mon, 26 Nov 2001 14:50:21 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: WAS: [Fwd: PostgreSQL new commands proposal]
Stephan Szabo wrote:
> 
> On Mon, 26 Nov 2001, Sergio Pili wrote:
> 
> > The implementation of the cases of inclusion dependences not based on
> > key (as well as other types of dependences) not still been standardized
> > and they are study matter in the academic atmospheres. If you are
> > interested, I can mention bibliography and references on these topics.
> > The specification of this type of dependences is not supported by any
> > DBMS.
> 
> I'd always be interested in interesting documents. :)

Codd, E.: "The Relational Model for Database Management". Version 2.
Addison Wesley Publishing Co. 1990
Abiteboul, S.; Hull, R.; Vianu, V.: "Foundations on Databases". Addison
Wesley Publ. Co. 1995
Date, C: "Relational Databases, Selected Writings 1985-1989". Addison
Wesley. Reprinted with corrections 1989.
Casanova, M et al.: "Optimization of relational schemes containing
inclusion dependencies". Proceedings of 15 VLDB Conference. Amsterdam,
1989 pp.315-325.

> The delete/update things is:
> transaction 1 starts
> transaction 2 starts
> transaction 1 deletes a row from A
>  -- There are no rows in B that can be seen by
>  -- this transaction so you don't get any deletes.
> transaction 2 updates a row in B
>  -- The row in A can still be seen since it
>  -- hasn't expired for transaction 2
> transaction 1 commits
> transaction 2 commits


I understand. This happens because with the MVCC, the writings don't
lock the readings...
I don't like a lot this but the MVCC works this way.


> 
> The trigger thing is (I'm not 100% sure, but pretty sure this
> is what'll happen - given that a test rule with a
> function that prints a debugging statement gave me the
> originally specified value not the final value)
> transaction 1 starts
>  you say update A key to 2,2
>  - does cascade update of B as rule expansion to 2,2
>  - before trigger on A sets NEW.key to 3,3
>  - the row in A actually becomes 3,3
> You'd no longer be checking the validity of the value
> of B and so you'd have a broken constraint.
> 

If this is true, does mean that the rules can be avoided
using before triggers?
Are not the commands executed in the triggers passed through the
re-writing system?


> All in all I think you'd be better off with triggers than rules, but I
> understand what you're trying to accomplish.

We fully agree with you in the sense that our examples and inclusion
dependencies may be totally handled using triggers. In fact, we have
done this many times in several cases. The question here is not, for
example, ï¿½how to preserve an inclusion dependencyï¿½ but ï¿½which is the
better way to preserve inclusion dependenciesï¿½.
We are so insistent on this matter because the level of abstraction (and
generality) of rules is higher than the triggers and thus it becomes
easier to express a real world problem in a rule than in a trigger.
PostgreSQL rules can "almost"  be used for this sort of problems (we do
not bother you with the whole set of features that this approach will
allow).
In this way, for just a minimum price,  we may buy a new wide set of
capabilities. We ensure you that this is a very good deal. If you want
to discuss which are those new capabilities, we can send you a large
more explicative document on the subject.

Regards,

Sergio Pili

Sat, 01 Dec 2001 20:21:00 -0300
Sergio Pili <sergiop@sinectis.com.ar>
Re: WAS: [Fwd: PostgreSQL new commands proposal]
On Sat, 1 Dec 2001, Sergio Pili wrote:

> [documents snipped]

Thanks.

> > The delete/update things is:
> > transaction 1 starts
> > transaction 2 starts
> > transaction 1 deletes a row from A
> >  -- There are no rows in B that can be seen by
> >  -- this transaction so you don't get any deletes.
> > transaction 2 updates a row in B
> >  -- The row in A can still be seen since it
> >  -- hasn't expired for transaction 2
> > transaction 1 commits
> > transaction 2 commits
>
> I understand. This happens because with the MVCC, the writings don't
> lock the readings...
> I don't like a lot this but the MVCC works this way.

You can get this by doing row level locks with for update or table
locks, but you have to be careful to make sure to do it and AFAIK
for update doesn't work in subselects and table locks are much
much too strong (for update is too strong as well, but it's less
too strong - see arguments about the fk locking ;) )

> > The trigger thing is (I'm not 100% sure, but pretty sure this
> > is what'll happen - given that a test rule with a
> > function that prints a debugging statement gave me the
> > originally specified value not the final value)
> > transaction 1 starts
> >  you say update A key to 2,2
> >  - does cascade update of B as rule expansion to 2,2
> >  - before trigger on A sets NEW.key to 3,3
> >  - the row in A actually becomes 3,3
> > You'd no longer be checking the validity of the value
> > of B and so you'd have a broken constraint.
> >
>
> If this is true, does mean that the rules can be avoided
> using before triggers?
> Are not the commands executed in the triggers passed through the
> re-writing system?

Before triggers have the option of actually changing the *actual*
tuple to insert/update as I understand it.  It's not that the
before trigger runs an update (which wouldn't work because the
row isn't there) but that the before trigger can change the row
being inserted (for example to add a timestamp) or negate
the insert/deletion/update entirely (returning NULL) which would mean
that you'd have rule things going off when the original operation
was canceled by trigger I believe.

> > All in all I think you'd be better off with triggers than rules, but I
> > understand what you're trying to accomplish.
>
> We fully agree with you in the sense that our examples and inclusion
> dependencies may be totally handled using triggers. In fact, we have
> done this many times in several cases. The question here is not, for
> example, �how to preserve an inclusion dependency� but �which is the
> better way to preserve inclusion dependencies�.
> We are so insistent on this matter because the level of abstraction (and
> generality) of rules is higher than the triggers and thus it becomes
> easier to express a real world problem in a rule than in a trigger.
> PostgreSQL rules can "almost"  be used for this sort of problems (we do
> not bother you with the whole set of features that this approach will
> allow).
> In this way, for just a minimum price,  we may buy a new wide set of
> capabilities. We ensure you that this is a very good deal. If you want
> to discuss which are those new capabilities, we can send you a large
> more explicative document on the subject.

Well, I'm not particularly the person you need to convince, since I don't
have a strong view on the functionality/patch in question :), I was just
pointing out that the example given wasn't likely to convince someone.


Sat, 1 Dec 2001 15:39:54 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: WAS: [Fwd: PostgreSQL new commands proposal]