BUG #3723: dropping an index that doesn't refer to table's columns

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.