Re: constraint modification on todo list

Hi people,

can someone add:

Add an ALTER TABLE MODIFY CONSTRAINT

item to the todo list? I am even willing to pick this one up in a
while, after I finish some other outstanding tasks.

--
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
Pull me down again and guide me into pain...

On Mon, 2003-09-08 at 14:32, Jeroen Ruigrok/asmodai wrote:
> Hi people,
>
> can someone add:
>
> Add an ALTER TABLE MODIFY CONSTRAINT
>
> item to the todo list? I am even willing to pick this one up in a
> while, after I finish some other outstanding tasks.

This could be rather time consuming to actually write but having the
ability to change foreign key on update / on delete modes without
rechecking all of the data would be very useful.

I think this is a more consistent syntax:
ALTER TABLE .. ADD OR REPLACE <table constraint>

-On [20030908 20:52], Rod Taylor (rbt(at)rbt(dot)ca) wrote:
>This could be rather time consuming to actually write but having the
>ability to change foreign key on update / on delete modes without
>rechecking all of the data would be very useful.

I was more interested in this feature for CHECK constraints. :)

>I think this is a more consistent syntax:
>ALTER TABLE .. ADD OR REPLACE <table constraint>

I was following Oracle's example here for sake of some consistency
between some RDBMSes.

--
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
Gravitation can not be held responsible for people falling in love...

Jeroen Ruigrok/asmodai <asmodai(at)wxs(dot)nl> writes:
> can someone add:
> Add an ALTER TABLE MODIFY CONSTRAINT
> item to the todo list?

Why? For a constraint, it's not obvious what this would do for you that
dropping and re-adding the constraint wouldn't do. In the places where
we support CREATE OR REPLACE, it's because it's important to maintain
continuity of the object's identity, but I don't see any need for that
with respect to check constraints.

BTW, getting something put on the todo list doesn't mean anyone's going
to step up to the plate and do it ...

regards, tom lane

Tom Lane wrote:
> Jeroen Ruigrok/asmodai <asmodai(at)wxs(dot)nl> writes:
> > can someone add:
> > Add an ALTER TABLE MODIFY CONSTRAINT
> > item to the todo list?
>
> Why? For a constraint, it's not obvious what this would do for you that
> dropping and re-adding the constraint wouldn't do. In the places where
> we support CREATE OR REPLACE, it's because it's important to maintain
> continuity of the object's identity, but I don't see any need for that
> with respect to check constraints.

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.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

-On [20030908 22:42], Bruce Momjian (pgman(at)candle(dot)pha(dot)pa(dot)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.

--
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
Things should be made as simple as possible, but not any simpler...

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> 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.

Yeah. Rod's point about altering foreign key action settings is a good
one, but other than that I don't see a whole lot of scope for shortcuts.

regards, tom lane

Jeroen Ruigrok/asmodai <asmodai(at)wxs(dot)nl> writes:
> 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.

If you're concerned about concurrent transactions, you should do the
change like this:

begin;
alter table drop constraint ...;
alter table add constraint ...;
commit;

which leaves no window for missed checks. (The first ALTER will take
out an exclusive lock on the table, which will be held till end of
transaction.)

regards, tom lane

"Jeroen Ruigrok/asmodai" <asmodai(at)wxs(dot)nl> wrote:
> 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.

I think, someone correct me if I'm wrong, you can do it inside a
transaction,
so no time window without constraint.

Regards
Gaetano Mendola

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > 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.
>
> Yeah. Rod's point about altering foreign key action settings is a good
> one, but other than that I don't see a whole lot of scope for shortcuts.

The only shortcuts that come to my mind is when you enlarge ( or relax )
a constraint, and I think that is not so easy detect it.

Regards
Gaetano Mendola

Jeroen Ruigrok/asmodai wrote:
> -On [20030908 22:42], Bruce Momjian (pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> 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.

IIRC, Oracle does not have rollback-able DDL. That might imply that the
reason they have MODIFY CONSTRAINT is that in Oracle you can't use the
above way to eliminate the window. Can you put ALTERs inside
transactions at all in Oracle?

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > 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.
>
> Yeah. Rod's point about altering foreign key action settings is a good
> one, but other than that I don't see a whole lot of scope for shortcuts.

Agreed. Added to TODO:

o Allow ALTER TABLE to change constraint deferrability and actions

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

-On [20030909 00:42], Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>IIRC, Oracle does not have rollback-able DDL. That might imply that the
>reason they have MODIFY CONSTRAINT is that in Oracle you can't use the
>above way to eliminate the window. Can you put ALTERs inside
>transactions at all in Oracle?

As one of the Oracle gurus at work told me:

DDL does an implicit commit, so no rollback possible.
It also shouldn't be necessary, because you cannot change a table which
is in use.
It attempts to do a table lock and it fails.

--
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
We should take care not to make the intellect our god; it has, of
course, powerful muscles, but no personality...

Jeroen Ruigrok/asmodai <asmodai(at)wxs(dot)nl> writes:
> -On [20030909 00:42], Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> IIRC, Oracle does not have rollback-able DDL. That might imply that the
>> reason they have MODIFY CONSTRAINT is that in Oracle you can't use the
>> above way to eliminate the window. Can you put ALTERs inside
>> transactions at all in Oracle?

> As one of the Oracle gurus at work told me:

> DDL does an implicit commit, so no rollback possible.
> It also shouldn't be necessary, because you cannot change a table which
> is in use.
> It attempts to do a table lock and it fails.

Is that their excuse?

We can't ALTER a table that's already in use when the first ALTER
starts, either --- its attempt to exclusive-lock the table will fail.
But once you get the exclusive lock, you can (in Postgres) perform
a series of operations without fear that subsequently-started
transactions will be able to see the incompletely changed state of the
table. Evidently Oracle can't handle that. That's why they need to
invent combination operations like MODIFY CONSTRAINT.

regards, tom lane

-On [20030911 15:43], Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>We can't ALTER a table that's already in use when the first ALTER
>starts, either --- its attempt to exclusive-lock the table will fail.
>But once you get the exclusive lock, you can (in Postgres) perform
>a series of operations without fear that subsequently-started
>transactions will be able to see the incompletely changed state of the
>table. Evidently Oracle can't handle that. That's why they need to
>invent combination operations like MODIFY CONSTRAINT.

As my colleague says:

it is indeed a lazy choice, but super safe and that's the goal.

--
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai / kita no mono
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
Man inagines that it is death he fears; but what he fears is the unforeseen,
the explosion. What man fears is himself...

"Jeroen Ruigrok/asmodai" <asmodai(at)wxs(dot)nl> wrote:
> -On [20030911 15:43], Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> >We can't ALTER a table that's already in use when the first ALTER
> >starts, either --- its attempt to exclusive-lock the table will fail.
> >But once you get the exclusive lock, you can (in Postgres) perform
> >a series of operations without fear that subsequently-started
> >transactions will be able to see the incompletely changed state of the
> >table. Evidently Oracle can't handle that. That's why they need to
> >invent combination operations like MODIFY CONSTRAINT.
>
> As my colleague says:
>
> it is indeed a lazy choice, but super safe and that's the goal.

Does your colleague know the Aesops's Fables: "The fox and the Grapes" ?

Regards
Gaetano Mendola