Thread: cascading column drop to index predicates
Hey Tom, With regards to our previous conversation about dropping columns now properly dropping indexes that contain predicates that reference that column, I now find it a bit disconcerting that such indexes are automatically removed when the column is dropped, instead of requiring a CASCADE. The thing is, if you drop a column that is used in a normal index, yes the index is now useless - drop it. However, since you can have (and I have) indexes like this: CREATE INDEX asdf ON table (a, b, c) WHERE d IS NOT NULL; If I drop column d, there is no way I want that index to just disappear! This has already caught me out... Can we change it to requiring a CASCADE? Is that a good idea? Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > The thing is, if you drop a column that is used in a normal index, yes > the index is now useless - drop it. > However, since you can have (and I have) indexes like this: > CREATE INDEX asdf ON table (a, b, c) WHERE d IS NOT NULL; > If I drop column d, there is no way I want that index to just disappear! Uh, why not? I don't quite see the argument why d stands in a different relationship to this index than a,b,c do. The index is equally meaningless without any of them. > Can we change it to requiring a CASCADE? It'd likely be a simple code change, but first let's have the argument why it's a good idea. regards, tom lane
Tom Lane wrote: >Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > >>The thing is, if you drop a column that is used in a normal index, yes >>the index is now useless - drop it. >>However, since you can have (and I have) indexes like this: >>CREATE INDEX asdf ON table (a, b, c) WHERE d IS NOT NULL; >>If I drop column d, there is no way I want that index to just disappear! >> >> > >Uh, why not? I don't quite see the argument why d stands in a different >relationship to this index than a,b,c do. The index is equally >meaningless without any of them. > > > >>Can we change it to requiring a CASCADE? >> >> > >It'd likely be a simple code change, but first let's have the argument >why it's a good idea. > > In that sample mentioned the index might be used mostly with a,b columns. Dropping the index silently might damage the application because it relies on an (a,b) index to be present. IMHO only Indexes that span that single column should be dropped without CASCADE. Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes: > In that sample mentioned the index might be used mostly with a,b > columns. Dropping the index silently might damage the application > because it relies on an (a,b) index to be present. IMHO only Indexes > that span that single column should be dropped without CASCADE. That argument makes no sense to me at all. If you drop the *column* a or b, and do not thereby break your application, how is the disappearance of the index on it going to break anything? The index is meaningless without something to index. I think the question at hand is whether the same logic applies to partial indexes: if the index's condition is no longer meaningful, is the index meaningful? I think we can handle both cases the same way. But clearly an index condition isn't quite the same thing as an index column, so maybe someone can make a good argument for treating them differently. regards, tom lane
On Mon, 2003-12-22 at 10:55, Tom Lane wrote: > Andreas Pflug <pgadmin@pse-consulting.de> writes: > > In that sample mentioned the index might be used mostly with a,b > > columns. Dropping the index silently might damage the application > > because it relies on an (a,b) index to be present. IMHO only Indexes > > that span that single column should be dropped without CASCADE. > > That argument makes no sense to me at all. If you drop the *column* > a or b, and do not thereby break your application, how is the > disappearance of the index on it going to break anything? The index > is meaningless without something to index. I think Andreas is trying to argue that if you drop column b from index (a, b) that the index should be converted into index(a) -- assuming of course there isn't already an index(a).
Rod Taylor <pg@rbt.ca> writes: > I think Andreas is trying to argue that if you drop column b from index > (a, b) that the index should be converted into index(a) -- assuming of > course there isn't already an index(a). That seems to be well outside the charter of DROP CASCADE. I think we either drop or don't drop; we don't go building new indexes, which is what this would take. There are also definitional problems --- for instance, if the index is UNIQUE, does it transmogrify into a UNIQUE constraint on A alone (which would most likely fail)? regards, tom lane
Tom Lane wrote: >Rod Taylor <pg@rbt.ca> writes: > > >>I think Andreas is trying to argue that if you drop column b from index >>(a, b) that the index should be converted into index(a) -- assuming of >>course there isn't already an index(a). >> >> > >That seems to be well outside the charter of DROP CASCADE. I think we >either drop or don't drop; we don't go building new indexes, which is >what this would take. There are also definitional problems --- for >instance, if the index is UNIQUE, does it transmogrify into a UNIQUE >constraint on A alone (which would most likely fail)? > > Agreed, auto creation wouldn't be necessary/expected. If you drop, objects disappear, you don't expect them to morph. But I'd like to be inhibited to drop the column if it requires a somewhat recreated index on (a). So IMHO a DROP INDEX [RESTRICT] should drop only dependent objects if this won't affect others. Regards, Andreas