1

I have a table with many partitions that I wanted to add a new index to. Following the instructions from Postgres I created the new index on the main table using ON ONLY and then proceeded to create indexes on all the partitions of the table and attach them.

When I got done, I expected the overall index to automatically be marked as valid as per the documentation, however this query was still returning that index (and only that index) as invalid:

SELECT *
FROM pg_class,
     pg_index
WHERE pg_index.indisvalid = false
  AND pg_index.indexrelid = pg_class.oid;

I'm assuming in my manual creation of the indexes on the partitions that I missed attaching one (I already verified they're there on every partition, including default). Is there a way to have Postgres tell me which partition it's missing?

1 Answer 1

1

EDIT: Pgpartman has a function that will fill these partitions in for you: partition_gap_fill. Also relevant is the partition_data_proc and partition_data functions that will actually create partitions and move data from the default partition or other table into the new partitions.

Original Answer:

While I was unable to actually figure out a way to get Postgres to tell me which partition was missing, I did come across the relispartition flag on the pg_class table that I was able to query on and find the partition indexes that I had created but weren't attached to the overall index. Attaching those two partition indexes, made the overall index valid.

Example query to find non-attached indexes:

SELECT relname, relpages, reltuples, *
FROM pg_class
WHERE relname ilike '%SOME_PATTERN_IN_ALL_INDEX_NAMES%'
and relispartition is false;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.