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.