Re: constraint modification on todo list

Jeroen Ruigrok/asmodai wrote:
> -On [20030908 22:42], Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> >I assume MODIFY would allow you to alter the constraint without
> >re-checking all the rows, as would be required by DROP/ADD.  However, if
> >you are modifying the constraint, wouldn't we have to recheck all the
> >rows anyway.  Of course, one idea would be to allow MODIFY to make
> >changes that _don't_ require rechecking, but I have no idea what such
> >changes would be.
> 
> I might have misread/misremembered something:
> 
> Constraint State Modification
> 
> You can use the MODIFY CONSTRAINT clause of the ALTER TABLE statement to
> change the following constraint states:
> 
>     * DEFERRABLE or NOT DEFERRABLE
>     * INITIALLY DEFERRED or INITIALLY IMMEDIATE
>     * RELY or NORELY
>     * USING INDEX ...
>     * ENABLE or DISABLE
>     * VALIDATE or NOVALIDATE
>     * EXCEPTIONS INTO ...
> 
> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2103836
> 
> I wonder if it is possible to have ALTER TABLE ... MODIFY CONSTRAINT ...
> CHECK ...
> Because what I can imagine, and please correct me if I miss something in
> my thought pattern, you have a small gap between dropping a constraint
> and adding the new one allowing the possibility of missing checks.
> Or would this be solved by adding a second constraint under another
> constraint name with the new constraint values and then dropping the
> older one?  If so, then ALTER TABLE ... RENAME CONSTRAINT would come in
> handy.

Oh, you bring up two important issues --- one is is the gap in time
between the drop and the recreate.  This case can be done by
doing the query in a transaction --- the lock will exist until the
transaction completes, and in fact, you can roll it back in case you
don't like it.

The second case is changing not the constraint but its behavior, like
deferrability.  Right now I don't see any way to control that except
drop/recreate, and this is where MODIFY might make sense.

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