deferred check constraints
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.
I may figure out how to flip everything around so that the simple
reference constraint could be deferred. That is one option
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?
Thank you,
Perry
Perry Smith <pedz@easesoftware.com> writes:
Right now, it would be nice if I could get a check constraint to be
deferred.
Possibly you could use a deferred "constraint trigger" and do the check
inside that, but I think you will find there are all sorts of race
conditions in this design.
regards, tom lane
"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.
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
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 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
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
"Perry Smith" <pedz@easesoftware.com> writes:
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.
Not that I haven't done similar things in the past but you should think hard
about whether you can't normalize your data further to avoid this. It will
make querying your data later much easier. (Think of what your joins are going
to look like.)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com