Thread: Dropping column silently kills multi-coumn index (was [ODBC] Error when accessing tables with deleted columns)

The other discussion made me try this out:

> So the new dll did take out the delete column.  I
> assume that if you do a drop column that it removes
> the associated index.

(Obviously 7.3.* is involved here...)

I did the following:

oms=# create table __temp1(field1 varchar(10), field2 varchar(10));
CREATE TABLE
oms=# create index __idx_temp1 on __temp1 (field1, field2);
CREATE INDEX
oms=# alter table __temp1 drop column field2;
ALTER TABLE

Note that the ALTER TABLE query succeeded *quietly* and did in fact drop
the index.

Is this intended behavior?  Rather seems like it should refuse to drop
an indexed column, or at least refuse to drop a culumn that's a member
of a multi-culumm index and give notice when an index is dropped.  I
think I'd opt for the never-drop-an-indexed-column approach.

Just an observation :-)

Glen Parker
glenebob@nwlink.com

"Glen Parker" <glenebob@nwlink.com> writes:
> Note that the ALTER TABLE query succeeded *quietly* and did in fact drop
> the index.

If indexes require a CASCADE to be dropped by DROP COLUMN, then DROP
TABLE on an indexed table would also require CASCADE.  Does that seem
like a good idea?

            regards, tom lane

> > Note that the ALTER TABLE query succeeded *quietly* and did in fact
> > drop the index.
>
> If indexes require a CASCADE to be dropped by DROP COLUMN,
> then DROP TABLE on an indexed table would also require
> CASCADE.  Does that seem like a good idea?

I see the connection you're trying to make there, but I don't think it
quite follows.  When you drop a table, all its indexes logically become
orphaned and so can be quietly dropped; who would expect the indexes to
stay?  When you drop a column that belongs to a multi-column index on
the other hand, the index does not become logically orphaned.  It
becomes... Something else...  I think it could be an intuative
expectation that the server should re-structure the index minus the
dropped field.  In other words, the index *can* exist without the
dropped field, just not in its current form.  Because of that
uncertainty, it makes sense to me to refuse to drop the column.  The
reason I suggested the same behavior for *single* column indexes is
purely for constistancy.

The post that got me looking into this showed that exact uncertainty;
there was a question whether the index was dropped or not.

And no, requiring CASCADE on table drops to get rid of indexes makes
exactly zero sence to me :-)

Glen

The issue here is whether dropping a column should automatically drop a
multi-column index of which that column is a member.

The example shown below is particularly good because the dropped field
is second in the index, meaning that the index is useful for lookups on
field1 alone, so dropping field2 removes a useful index on field1.  I
don't think it is defensible to allow DROP COLUMN to remove the index.
Instead, I think we have to refuse the DROP COLUMN and require the user
to drop the index and recreate it just on field1 if desired. I don't
think CASCASE enters into this because of the effect on field1.

Comments?

---------------------------------------------------------------------------

Example case was:

> oms=# create table __temp1(field1 varchar(10), field2 varchar(10));
> CREATE TABLE
> oms=# create index __idx_temp1 on __temp1 (field1, field2);
> CREATE INDEX
> oms=# alter table __temp1 drop column field2;
> ALTER TABLE

> > > Note that the ALTER TABLE query succeeded *quietly* and did in fact
> > > drop the index.
> >
> > If indexes require a CASCADE to be dropped by DROP COLUMN,
> > then DROP TABLE on an indexed table would also require
> > CASCADE.  Does that seem like a good idea?
>
> I see the connection you're trying to make there, but I don't think it
> quite follows.  When you drop a table, all its indexes logically become
> orphaned and so can be quietly dropped; who would expect the indexes to
> stay?  When you drop a column that belongs to a multi-column index on
> the other hand, the index does not become logically orphaned.  It
> becomes... Something else...  I think it could be an intuative
> expectation that the server should re-structure the index minus the
> dropped field.  In other words, the index *can* exist without the
> dropped field, just not in its current form.  Because of that
> uncertainty, it makes sense to me to refuse to drop the column.  The
> reason I suggested the same behavior for *single* column indexes is
> purely for constistancy.
>
> The post that got me looking into this showed that exact uncertainty;
> there was a question whether the index was dropped or not.
>
> And no, requiring CASCADE on table drops to get rid of indexes makes
> exactly zero sence to me :-)
>
> Glen
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

--
  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, Pennsylvania 19073

Bruce Momjian wrote:
> The issue here is whether dropping a column should automatically drop a
> multi-column index of which that column is a member.
>
> The example shown below is particularly good because the dropped field
> is second in the index, meaning that the index is useful for lookups on
> field1 alone, so dropping field2 removes a useful index on field1.  I
> don't think it is defensible to allow DROP COLUMN to remove the index.
> Instead, I think we have to refuse the DROP COLUMN and require the user
> to drop the index and recreate it just on field1 if desired. I don't
> think CASCASE enters into this because of the effect on field1.
>
> Comments?

Would it be possible/practical to have PostgreSQL recreate the
multi-column index, but without the dropped column?

Regards and best wishes,

Justin Clift

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
    - Indira Gandhi

At 11:36 AM 2/15/03 +1100, Justin Clift wrote:

>Bruce Momjian wrote:
>>think CASCASE enters into this because of the effect on field1.
>>Comments?
>
>Would it be possible/practical to have PostgreSQL recreate the
>multi-column index, but without the dropped column?

Wouldn't that take a long time in some cases?

I think it's a good idea to throw an error and refuse to drop the column
and index and let the DB admin decide what to do next.

If someone designs a system that regularly drops columns from tables AND
wants indexes on those columns, I'd figure requiring them to drop relevant
indexes first would be a good idea. Of course if they can optionally
configure things (triggers etc) to drop the index when dropping/altering a
column, that would be ok too.

When the admins don't know what they are doing or make a mistake - it'll
fail safe. When the admins know, as long as they are still able to set
things up accordingly, I don't think it's a big problem.

Regards,
Link.

Justin Clift wrote:
> Bruce Momjian wrote:
> > The issue here is whether dropping a column should automatically drop a
> > multi-column index of which that column is a member.
> >
> > The example shown below is particularly good because the dropped field
> > is second in the index, meaning that the index is useful for lookups on
> > field1 alone, so dropping field2 removes a useful index on field1.  I
> > don't think it is defensible to allow DROP COLUMN to remove the index.
> > Instead, I think we have to refuse the DROP COLUMN and require the user
> > to drop the index and recreate it just on field1 if desired. I don't
> > think CASCASE enters into this because of the effect on field1.
> >
> > Comments?
>
> Would it be possible/practical to have PostgreSQL recreate the
> multi-column index, but without the dropped column?


Yes, we could, but creating an index takes time, so is probably
something we would want the admin to do manually.

--
  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, Pennsylvania 19073

Added to TODO:

    * Disallow DROP COLUMN on a column that is part of a multi-column index

---------------------------------------------------------------------------

Lincoln Yeoh wrote:
> At 11:36 AM 2/15/03 +1100, Justin Clift wrote:
>
> >Bruce Momjian wrote:
> >>think CASCASE enters into this because of the effect on field1.
> >>Comments?
> >
> >Would it be possible/practical to have PostgreSQL recreate the
> >multi-column index, but without the dropped column?
>
> Wouldn't that take a long time in some cases?
>
> I think it's a good idea to throw an error and refuse to drop the column
> and index and let the DB admin decide what to do next.
>
> If someone designs a system that regularly drops columns from tables AND
> wants indexes on those columns, I'd figure requiring them to drop relevant
> indexes first would be a good idea. Of course if they can optionally
> configure things (triggers etc) to drop the index when dropping/altering a
> column, that would be ok too.
>
> When the admins don't know what they are doing or make a mistake - it'll
> fail safe. When the admins know, as long as they are still able to set
> things up accordingly, I don't think it's a big problem.
>
> Regards,
> Link.
>
>
>

--
  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, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO:
>     * Disallow DROP COLUMN on a column that is part of a multi-column index

That's poorly phrased.  What you meant to say is "require CASCADE if ..."

            regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Added to TODO:
> >     * Disallow DROP COLUMN on a column that is part of a multi-column index
>
> That's poorly phrased.  What you meant to say is "require CASCADE if ..."

Actually, no.  Does CASCADE make sense in this case?  In the multi-key
index, the index is _shared_ by several columns?  Do we have precedent
for CASCADE to remove something that is shared by others?  I don't
think so.

However, I now see in the ALTER TABLE manual page:

       CASCADE
              Automatically  drop  objects  that  depend  on  the
              dropped column or constraint  (for  example,  views
              referencing the column).

which does indicate that CASCADE delete things, like views, that are
shared.

TODO wording updated:

    * Require DROP COLUMN CASCADE for a column that is part of a
    multi-column index

--
  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, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Added to TODO:
> * Disallow DROP COLUMN on a column that is part of a multi-column index
>>
>> That's poorly phrased.  What you meant to say is "require CASCADE if ..."

> Actually, no.  Does CASCADE make sense in this case?

Yes, it does.  The multi-key index is a dependent object, and dropping
dependent objects is exactly what CASCADE is for.

> Do we have precedent
> for CASCADE to remove something that is shared by others?

Certainly.  Try dropping a function that is used in several default
expressions, for example.

            regards, tom lane