Re: deferred check constraints

On Jul 16, 2007, at 3:08 PM, Gregory Stark wrote:

> "Perry Smith" <pedz(at)easesoftware(dot)com> 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