Обсуждение: 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
Partition key: LIST (date_part('year'::text, mis_ora))
As an aside, you may benefit from switching to range partitioning* depending on how your queries are written. If you have conditions such as "WHERE mis_ora BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE" or similar, then the fact that your partitioning is defined as a function result will mean all partitions gets scanned instead of partitioned being pruned as early as possible in the process. That's my understanding anyway. If you always include date_part( 'year', mis_ora) comparison in your where/join conditions, then you'll likely be just fine. Do as you need.
*eg '01/01/2018' to '01/01/2019' for the 2018 partition since upper bound is always exclusive