Re: deferred check constraints

"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.

> 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.

But many people do put queries in check constraints with the caveats
understood -- it's still useful if the referred-to data is basically static.
So perhaps it would be useful.

There is something in the standard called Assertions which I think are
supposed to address this issue. But they're "hard" and I don't know if any
database supports them. I wonder if we did whether anyone would find them
useful.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com