Question 137179 in PostgreSQL Question posted in
The official documentation can be found here.

Cannot drop index on partition table in postgresql

I have a table called Customer with three columns and table was partitioned by score into 10 partitions, from customer_0 to customer_9

         Customer table
      id  |  name  | score
       1  | Light  |  90
       2  | Missa  |  10

There are indexes created on each partition and global index on column score

 customer_0
 cust_score_0_idx"  "CREATE INDEX cust_score_0_idx ON customer_0 USING btree (score)
 customer
 cust_score_idx"    "CREATE INDEX cust_score_idx ON customer_0 USING btree (score)

As you can see in the above the partition-0 has index and table as well has same index with different name, now when i’m trying to delete the one on partition-0 i’m facing issue with a unknown constraint

ERROR:  cannot drop index cust_score_0_idx because index cust_score_idx requires it
HINT:  You can drop index cust_score_idx instead.
SQL state: 2BP01

So these tables are created long back and i don’t know what sql/constraint they used, but now i’m trying to figure out what is stopping dropping the partition index independently.

But as to note, once i drop index on table, it automatically drops the index on partitions, and after that i’m able to create and drop index independently on table and partition.

so i’m more curious how to setup partition index as dependent on global index ?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Finally found from the PostgreSQL documentation, for the partitioned tables, indices have to be created individually on each partition and then finally need to be attached to index created on table. That is the specific limitation from postgres and if partition indices are not attached then table index remains invalid

    As explained above, it is possible to create indexes on partitioned tables so that they are applied automatically to the entire hierarchy. This is very convenient, as not only will the existing partitions become indexed, but also any partitions that are created in the future will. One limitation is that it's not possible to use the CONCURRENTLY qualifier when creating such a partitioned index. To avoid long lock times, it is possible to use CREATE INDEX ON ONLY the partitioned table; such an index is marked invalid, and the partitions do not get the index applied automatically. The indexes on partitions can be created individually using CONCURRENTLY, and then attached to the index on the parent using ALTER INDEX .. ATTACH PARTITION. Once indexes for all partitions are attached to the parent index, the parent index is marked valid automatically.

    Example :

     CREATE INDEX measurement_usls_idx ON ONLY measurement (unitsales);

 CREATE INDEX measurement_usls_200602_idx
       ON measurement_y2006m02 (unitsales);
 ALTER INDEX measurement_usls_idx
       ATTACH PARTITION measurement_usls_200602_idx;

  2. 0

    An index on a partitioned table is a partitioned index, that is, it consists of an index on every partition. You cannot drop cust_score_0_idx because it is a part of cust_score_idx, and without it cust_score_idx would be incomplete.

    To get rid of cust_score_0_idx, you have to drop the index on the partitioned table (cust_score_idx); then all its partitions are dropped automatically.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search