Thread: 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.

"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

Tom Lane wrote:
> "Sam Mason" <sam@samason.me.uk> writes:
>> 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 ...

Note that it was a unique index:

postgres=# CREATE TABLE silly (id integer);
CREATE TABLE
postgres=# CREATE UNIQUE INDEX i_silly ON silly ((1));
CREATE INDEX
postgres=# INSERT INTO silly VALUES (1);
INSERT 0 1
postgres=# INSERT INTO silly VALUES (2);
ERROR:  duplicate key value violates unique constraint "i_silly"

Not sure that's enough of a use case to justify not banning it...

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

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

On Tue, Nov 06, 2007 at 10:00:43AM -0500, Tom Lane wrote:
> Heikki Linnakangas <heikki@enterprisedb.com> writes:
> > Not sure that's enough of a use case to justify not banning it...
>
> Yeah, it probably is.

It's reasonably easy to do this instead:

  CREATE TABLE foo (
    one INTEGER NOT NULL UNIQUE CHECK (one = 1)
  );

The bug (for me) was that it's possible to get the database into an
inconsisant state, with no warning or obvious way to back out.


  Sam

"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