The following bug has been logged online:
Bug reference: 3723
Logged by: Sam Mason
Email address: [EMAIL PROTECTED]
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
