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


Do we want the same behaviour for PRIMARY KEY(col1, col2)? CHECK(col1 >
col2)? etc. as well?

I'm thinking probably...

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
22 Feb 2003 23:31:24 -0500
Rod Taylor <rbt@rbt.ca>
TODO: DROP COLUMN .. CASCADE
Rod Taylor <rbt@rbt.ca> writes:
> Require DROP COLUMN CASCADE for a column that is part of a multi-column
> index

> Do we want the same behaviour for PRIMARY KEY(col1, col2)? CHECK(col1 >
> col2)? etc. as well?

Personally I'm not sold on the sensefulness of the TODO item to begin
with.

			regards, tom lane

Sun, 23 Feb 2003 00:07:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: TODO: DROP COLUMN .. CASCADE 
Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > Require DROP COLUMN CASCADE for a column that is part of a multi-column
> > index
> 
> > Do we want the same behaviour for PRIMARY KEY(col1, col2)? CHECK(col1 >
> > col2)? etc. as well?
> 
> Personally I'm not sold on the sensefulness of the TODO item to begin
> with.

The current code just drops any index that inludes the dropped column,
even if the column is the second column in a multi-column index.  Does
that seem OK to you?

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

Thu, 6 Mar 2003 12:15:50 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: TODO: DROP COLUMN .. CASCADE
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Personally I'm not sold on the sensefulness of the TODO item to begin
>> with.

> The current code just drops any index that inludes the dropped column,
> even if the column is the second column in a multi-column index.  Does
> that seem OK to you?

What's wrong with it?  Any unique constraint the index might have
carried is no longer interesting, so there's no semantic reason for
treating the index as an independent object.  And queries that might
have referenced the column aren't going to work anymore, so the query
mix changes and hence the index setup will really need rethinking anyhow.

Basically I think this proposal would introduce a weird, confusing
dichotomy of behavior between single- and multi-column indexes.
And as Rod pointed out, you'd logically have to do the same for CHECK
constraints depending on whether they mention one or several columns.
(And what of multicolumn foreign keys?)  I see much confusion ahead,
and no payback. 

			regards, tom lane

Thu, 06 Mar 2003 12:25:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: TODO: DROP COLUMN .. CASCADE 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Personally I'm not sold on the sensefulness of the TODO item to begin
> >> with.
> 
> > The current code just drops any index that inludes the dropped column,
> > even if the column is the second column in a multi-column index.  Does
> > that seem OK to you?
> 
> What's wrong with it?  Any unique constraint the index might have
> carried is no longer interesting, so there's no semantic reason for
> treating the index as an independent object.  And queries that might
> have referenced the column aren't going to work anymore, so the query
> mix changes and hence the index setup will really need rethinking anyhow.
> 
> Basically I think this proposal would introduce a weird, confusing
> dichotomy of behavior between single- and multi-column indexes.
> And as Rod pointed out, you'd logically have to do the same for CHECK
> constraints depending on whether they mention one or several columns.
> (And what of multicolumn foreign keys?)  I see much confusion ahead,
> and no payback. 

I do see the confusion argument, but I also see cases where folks are
losing the use of an index for single-column lookups.  Others?

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

Thu, 6 Mar 2003 13:01:17 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: TODO: DROP COLUMN .. CASCADE
> > Basically I think this proposal would introduce a weird, confusing
> > dichotomy of behavior between single- and multi-column indexes.
> > And as Rod pointed out, you'd logically have to do the same for CHECK
> > constraints depending on whether they mention one or several columns.
> > (And what of multicolumn foreign keys?)  I see much confusion ahead,
> > and no payback. 
> 
> I do see the confusion argument, but I also see cases where folks are
> losing the use of an index for single-column lookups.  Others?

So long as the user has NOTICES enabled, they should see what else was
dropped along with it. 

Besides, we should encourage schema modifications to be done in
transactions, thus they can rollback if they're not happy with the
results.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
06 Mar 2003 13:13:28 -0500
Rod Taylor <rbt@rbt.ca>
Re: TODO: DROP COLUMN .. CASCADE

On Thu, 6 Mar 2003, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Tom Lane wrote:
> > >> Personally I'm not sold on the sensefulness of the TODO item to begin
> > >> with.
> > 
> > > The current code just drops any index that inludes the dropped column,
> > > even if the column is the second column in a multi-column index.  Does
> > > that seem OK to you?
> > 
> > What's wrong with it?  Any unique constraint the index might have
> > carried is no longer interesting, so there's no semantic reason for
> > treating the index as an independent object.  And queries that might
> > have referenced the column aren't going to work anymore, so the query
> > mix changes and hence the index setup will really need rethinking anyhow.
>
> I do see the confusion argument, but I also see cases where folks are
> losing the use of an index for single-column lookups.  Others?

If I had table x:

create table dev (
	major int,
	minor int
);

and I CREATEd UNIQUE INDEX ON dev(major,minor), and then dropped col
minor, postgresql would silently create a new index on dev(major), or even
worse, use the existing index which has satellite data haunting from
minor? That would really be strange.

What if I noticed that I need to change type of b to int8 in the early
design stages? I drop col b and then add again col b with type int8, and
CREATE new UNIQUE INDEX ON dev(major, minor). Perhaps I don't even notice
that I have (redundant) index still on major, which only eats CPU time and
disk space.

-- 
Antti Haapala


Thu, 6 Mar 2003 20:30:57 +0200 (EET)
Antti Haapala <antti.haapala@iki.fi>
Re: TODO: DROP COLUMN .. CASCADE

OK, I will remove the TODO item:

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

Can we promote the index drop to a warning if it is multi-column?

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

Rod Taylor wrote:
-- Start of PGP signed section.
> > > Basically I think this proposal would introduce a weird, confusing
> > > dichotomy of behavior between single- and multi-column indexes.
> > > And as Rod pointed out, you'd logically have to do the same for CHECK
> > > constraints depending on whether they mention one or several columns.
> > > (And what of multicolumn foreign keys?)  I see much confusion ahead,
> > > and no payback. 
> > 
> > I do see the confusion argument, but I also see cases where folks are
> > losing the use of an index for single-column lookups.  Others?
> 
> So long as the user has NOTICES enabled, they should see what else was
> dropped along with it. 
> 
> Besides, we should encourage schema modifications to be done in
> transactions, thus they can rollback if they're not happy with the
> results.
> 
> --
> Rod Taylor <rbt@rbt.ca>
> 
> PGP Key: http://www.rbt.ca/rbtpub.asc
-- End of PGP section, PGP failed!

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

Thu, 6 Mar 2003 13:40:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: TODO: DROP COLUMN .. CASCADE