On Jul 16, 2007, at 3:08 PM, Gregory Stark wrote:
"Perry Smith" <pedz@xxxxxxxxxxxxxxxx> writes:
Right now, it would be nice if I could get a check constraint to
be deferred.
Its a long story. I want a circular constraint. The way things
are set up
right now, it would be easy if I could defer my check
constraint. I'm doing a
polymorphic relation. One direction is a simple reference a
fixed table. The
other direction is a reference to table that changes based upon
the type of
the item. I can do this check in a function which implies it is
a check
constraint.
The main problem with this is that check constraints which refer to
other
tables don't really work. Not to the degree of rigour that referential
integrity checks maintain.
Consider what happens if someone updates the record you're
targeting but
hasn't committed yet. Your check constraint will see the old
version and pass
even though it really shouldn't. It'll even pass if the update has
committed
but your query started before it did so.
This brings up a point that I have wondered about. I think I need a
nice clear concise explanation of how the magic of a relational
database transactions are done.
I'll go see if I can find one. If anyone has a pointer to one, that
will help me the most right now.
The other option is to add deferred check constraints to
PostgreSQL. I've
never looked at the PostgreSQL code but I like parsers, etc. How
hard would
it be to add this to PostgreSQL and is it something of general
interest or am
I somewhat lost in the woods?
I suspect the reason they don't exist is precisely as above that
they don't
really make a lot of sense. If your check constraint can't usefully
include
queries on other tables then there's no reason to defer it. Your
record isn't
going to become acceptable later if it isn't now.
The constraint will be valid before the transaction completes (is
what I am thinking).
I need to add an element to table A and an element to table B that
reference each other. The "polymorphic" gunk comes up because table
B is not the same table each time. I just want something that will
fire after the inserts but before the transaction ends that will make
sure that A->B and B->A.
Thank you for your help,
Perry
