Thread: SET CONSTRAINTS and like named constraints
It looks like that right now if you have multiple constraints with the same name on different tables and some are deferrable and some are not, SET CONSTRAINTS "name" DEFERRED will fail when it reaches the not deferrable constraint. Is this the behavior we want, or do we want it to defer the deferrable ones with that name and possibly warn that some were not deferrable?
Stephan Szabo wrote: > > It looks like that right now if you have multiple constraints > with the same name on different tables and some are deferrable > and some are not, SET CONSTRAINTS "name" DEFERRED will fail when > it reaches the not deferrable constraint. Is this the behavior > we want, or do we want it to defer the deferrable ones with that > name and possibly warn that some were not deferrable? We have this TODO: * Allow SET CONSTRAINTS to be qualified by schema/table -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thu, 4 Sep 2003, Bruce Momjian wrote: > Stephan Szabo wrote: > > > > It looks like that right now if you have multiple constraints > > with the same name on different tables and some are deferrable > > and some are not, SET CONSTRAINTS "name" DEFERRED will fail when > > it reaches the not deferrable constraint. Is this the behavior > > we want, or do we want it to defer the deferrable ones with that > > name and possibly warn that some were not deferrable? > > We have this TODO: > > * Allow SET CONSTRAINTS to be qualified by schema/table I'd think the above is in addition to the TODO item. We should define the behavior for unqualified constraint names. I can see a few possibilities for the behavior of SET CONSTRAINTS DEFERREDa) The current behavior. If an unqualified constraintname matches multiple constraints and any of those constraints are non-deferrable it is an error, otherwise all matching constraints are deferred.b) The above behavior. If an unqualified constraint name matches multiple constraints and all of those constraints are non-deferrable it is an error, otherwise all matching deferrable constraints are deferred (possibly with a warning if any are non-deferrable).c) If an unqualified constraint name matches multiple constraints itis an error (presumably for set ... immediate as well). a is the easiest to do probably since it's what's there right now. It's also compatible with how we do things now. b is closer to what I think people might expect it to do and allows more than what we do now (so presumably the only people that would be bitten by it on upgrade are people that are getting errors right now). c is the "safest" option for preventing someone from doing something they don't want, but is the least compatible with what we have now.
Stephan Szabo wrote: > On Thu, 4 Sep 2003, Bruce Momjian wrote: > > > Stephan Szabo wrote: > > > > > > It looks like that right now if you have multiple constraints > > > with the same name on different tables and some are deferrable > > > and some are not, SET CONSTRAINTS "name" DEFERRED will fail when > > > it reaches the not deferrable constraint. Is this the behavior > > > we want, or do we want it to defer the deferrable ones with that > > > name and possibly warn that some were not deferrable? > > > > We have this TODO: > > > > * Allow SET CONSTRAINTS to be qualified by schema/table > > I'd think the above is in addition to the TODO item. We should define the > behavior for unqualified constraint names. > > I can see a few possibilities for the behavior of SET CONSTRAINTS DEFERRED > a) The current behavior. If an unqualified constraint name matches > multiple constraints and any of those constraints are non-deferrable it is > an error, otherwise all matching constraints are deferred. > b) The above behavior. If an unqualified constraint name matches > multiple constraints and all of those constraints are non-deferrable it is > an error, otherwise all matching deferrable constraints are deferred > (possibly with a warning if any are non-deferrable). > c) If an unqualified constraint name matches multiple constraints it is > an error (presumably for set ... immediate as well). > > > a is the easiest to do probably since it's what's there right now. It's > also compatible with how we do things now. b is closer to what I think > people might expect it to do and allows more than what we do now (so > presumably the only people that would be bitten by it on upgrade are > people that are getting errors right now). c is the "safest" option for > preventing someone from doing something they don't want, but is the least > compatible with what we have now. Right, what we do when we reference an unqualified constraint name is to apply the command to all of them and abort if any of them fail. If you can get agreement to change that, I will add it to the TODO. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073