The following bug has been logged online: Bug reference: 3723 Logged by: Sam Mason Email address: sam@samason.me.uk PostgreSQL version: 8.2.5 Operating system: Linux Description: dropping an index that doesn't refer to table's columns Details: Hi, I've just discovered that an index that doesn't refer to any of its table's columns isn't automatically dropped when its table is. The test case for me is: CREATE TABLE foo ( id INTEGER NOT NULL ); CREATE UNIQUE INDEX foo_id ON foo (id); CREATE UNIQUE INDEX foo_exp ON foo ((1)); DROP TABLE foo; -- foo_id will have gone, but foo_exp will still be there \di foo_id \di foo_exp AndrewSN suggested the following query to show indexes that have missing tables: SELECT indexrelid::regclass FROM pg_index i LEFT JOIN pg_class c ON i.indrelid=c.oid WHERE c.oid IS NULL; He also showed me which system catalogs to change in order to delete these indexes which I'm happy with at the moment. Thanks, Sam p.s. the reason for creating this strange index was to ensure that a maximum of one row was inserted into the table---I can do this different ways for now. ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

"Sam Mason" <sam@samason.me.uk> writes: > I've just discovered that an index that doesn't refer to any of its table's > columns isn't automatically dropped when its table is. A straightforward solution would be to ban such "indexes". > p.s. the reason for creating this strange index was to ensure that a maximum > of one row was inserted into the table---I can do this different ways for > now. Please explain how you thought it would help you do that, because without some evidence that there's a use-case, I'm inclined to fix it as above ... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Heikki Linnakangas <heikki@enterprisedb.com> writes: > Tom Lane wrote: >> Please explain how you thought it would help you do that, because >> without some evidence that there's a use-case, I'm inclined to fix it >> as above ... > Note that it was a unique index: Missed that --- obviously need more caffeine ... > Not sure that's enough of a use case to justify not banning it... Yeah, it probably is. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

"Sam Mason" <sam@samason.me.uk> writes: > I've just discovered that an index that doesn't refer to any of its table's > columns isn't automatically dropped when its table is. I've applied a patch for this: http://archives.postgresql.org/pgsql-committers/2007-11/msg00137.php regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq