Re: deferred check constraints

On Jul 16, 2007, at 3:29 PM, Perry Smith wrote:

>
> 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 postgres docs are great: http://www.postgresql.org/docs/8.2/
interactive/mvcc.html

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com