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@easesoftware.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@myemma.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