Re: Dropping column silently kills multi-coumn index (was - Mailing list pgsql-general

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