20

In PostgreSQL 9.2 / PostGIS 2.0.2 I had an index on a spatial column, created with

CREATE INDEX tiger_data_sld_the_geom_gist ON tiger_data.sld USING gist(the_geom);

Subsequently dropped the index with

DROP INDEX tiger_data_sld_the_geom_gist;

But now, when I try to recreate, I get this error:

#  CREATE INDEX tiger_data_sld_the_geom_gist ON tiger_data.sld USING gist(the_geom);
ERROR:  relation "tiger_data_sld_the_geom_gist" already exists

Dropping again doesn't work. It says that the index doesn't exist:

# DROP INDEX tiger_data_sld_the_geom_gist;
ERROR:  index "tiger_data_sld_the_geom_gist" does not exist

I haven't found the relation "tiger_data_sld_the_geom_gist" in any list of database objects, have tried DROP TABLE, and searched around for solutions.

What is this mystery relation "tiger_data_sld_the_geom_gist", and how do I remove it so that I can create the index?

Edit:

Also have tried restarting the server, and dumping / dropping / reloading the table (dropped with CASCADE).

kentr
  • 969
  • 1
  • 11
  • 21
  • Try a "SELECT * FROM pg_class" and see what's listed. Try it with and without a condition on relname,relnamespace and see if the index usage makes a difference. – Richard Huxton Sep 02 '13 at 21:31
  • Sorry, I didn't see your comment before I posted my solution. I imagine that performing the SELECT won't reveal clues now, since I've rebuilt everything. – kentr Sep 03 '13 at 00:38

2 Answers2

35

Unless you are setting the search_path GUC to (or at least including) the tiger_data schema, you need to add the schema to the index name to issue the DROP INDEX (I'd use it in any case for safety):

DROP INDEX tiger_data.tiger_data_sld_the_geom_gist;

That's because the index always go to the same schema of the table it belongs to. If the above doesn't solve your problem, you can check if this relation name exists and on each schema it is in with the following:

SELECT r.relname, r.relkind, n.nspname
FROM pg_class r INNER JOIN pg_namespace n ON r.relnamespace = n.oid
WHERE r.relname = 'tiger_data_sld_the_geom_gist';

It will return the kind (i for indexes, r for tables, S for sequences and v for views) of any relation that has the name tiger_data_sld_the_geom_gist and name of the schema it belongs to.

MatheusOl
  • 10,870
  • 3
  • 30
  • 28
  • Your answer came in while I was writing mine. I don't know if this is correct or not, so I'll let the community vote it up if so. – kentr Sep 03 '13 at 00:40
  • @kentr: I cannot guarantee with 100% that it was the case, but seems to be, your (strange, IMHO) approach seems to have hidden the real problem... – MatheusOl Sep 03 '13 at 00:52
  • :-). When a hammer doesn't work, use a sledgehammer... (that's meant to be self-deprecating humor). I suspected there was a better method than mine. – kentr Sep 03 '13 at 01:48
  • @kentr adding the schema_name prior to the index name did the trick, thanks. – George. Jun 19 '20 at 21:09
-4

Though not particularly efficient, this appears to have done the trick:

  1. Dump the table with pg_dump.
  2. Drop the table.
  3. Dump the database with pg_dump.
  4. Drop the database.
  5. Recreate the database and reload from dump files.
kentr
  • 969
  • 1
  • 11
  • 21
  • That might be a solution for a small, personal side-project, not for a running enterprise database. – adrz Apr 21 '21 at 11:02