Thread: TODO: DROP COLUMN .. CASCADE
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
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
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, Pennsylvania19073
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
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, Pennsylvania19073
> > 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
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
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, Pennsylvania19073