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 dont 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 recursin 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.