Thread: unable to drop index because it does not exists

Hi,
running 11.5 I've got a partitioned table where I want to destroy an
index (on a column that has nothing to do with the partition):


respidb=# \d+ respi.root

    Column    |            Type             |
--------------+-----------------------------+-
 sen_id       | integer                     |
 mis_flusso   | integer                     |
 mis_tasso    | integer                     |
 mis_velocita | integer                     |
 mis_ora      | timestamp without time zone |
 pk           | bigint                      |
 ts           | timestamp without time zone |
Partition key: LIST (date_part('year'::text, mis_ora))
Indexes:
    "idx_root_sensore" btree (sen_id)
    "idx_ts" btree (ts)
Partitions: respi.y2018 FOR VALUES IN ('2018'), PARTITIONED,
            respi.y2019 FOR VALUES IN ('2019'), PARTITIONED,
            respi.y2020 FOR VALUES IN ('2020'), PARTITIONED

respidb=# drop index idx_root_sensore;
ERROR:  index "idx_root_sensore" does not exist

Of course the index exists:

SELECT oid, relname, relkind FROM pg_class WHERE relname = 'idx_root_sensore';
-[ RECORD 1 ]-------------
oid     | 40950
relname | idx_root_sensore
relkind | I


I already did a manual vacuum on the table.
Any idea?

Luca

On 9/23/19 6:39 AM, Luca Ferrari wrote:
> Hi,
> running 11.5 I've got a partitioned table where I want to destroy an
> index (on a column that has nothing to do with the partition):
>
>
> respidb=# \d+ respi.root
>
>      Column    |            Type             |
> --------------+-----------------------------+-
>   sen_id       | integer                     |
>   mis_flusso   | integer                     |
>   mis_tasso    | integer                     |
>   mis_velocita | integer                     |
>   mis_ora      | timestamp without time zone |
>   pk           | bigint                      |
>   ts           | timestamp without time zone |
> Partition key: LIST (date_part('year'::text, mis_ora))
> Indexes:
>      "idx_root_sensore" btree (sen_id)
>      "idx_ts" btree (ts)
> Partitions: respi.y2018 FOR VALUES IN ('2018'), PARTITIONED,
>              respi.y2019 FOR VALUES IN ('2019'), PARTITIONED,
>              respi.y2020 FOR VALUES IN ('2020'), PARTITIONED
>
> respidb=# drop index idx_root_sensore;
> ERROR:  index "idx_root_sensore" does not exist
>
> Of course the index exists:
>
> SELECT oid, relname, relkind FROM pg_class WHERE relname = 'idx_root_sensore';
> -[ RECORD 1 ]-------------
> oid     | 40950
> relname | idx_root_sensore
> relkind | I
>
>
> I already did a manual vacuum on the table.
> Any idea?

Try specifying the schema name:

drop index respi.idx_root_sensore;


-- 
Angular momentum makes the world go 'round.

On Mon, Sep 23, 2019 at 2:23 PM Ron <ronljohnsonjr@gmail.com> wrote:
> Try specifying the schema name:

Yeah, shame on me, I always forget the schema for indexes!

Thanks,
Luca