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

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