Обсуждение: 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