reapply_indexes.py does not work with PG11

[keithf4]

The reapply_indexes.py script does not work properly with PG11. It only looks at the template table to see which indexes should be dropped/created on the children. Since most indexes don't need to be created on the template in 11 anymore, this causes it to throw errors when it tries to drop the natively inherited indexes.

[keithf4]

This script will now only work on PG10 and lower and will only work with trigger-based partitioning in PG11+. Index management for native partitioning in PG11+ is much better and trying to make this script work with it is not trivial. If it's needed, please either respond to this issue or create a new issue with your use-case and I may consider putting in the work for it.

[tbe]

Hi @keithf4 , creating new indexes on huge partition sets is a pain in the a**. And error prone. In a production env, days may pass between creating the index in QA until the scripts move to prod.

Having a -procedure or- script, that allows concurrent index recreation is nearly a must have, as CREATE INDEX CONCURRENTLY does not work on partitioned tables:

Concurrent builds for indexes on partitioned tables are currently not supported. However, you may concurrently build the index on each partition individually and then finally create the partitioned index non-concurrently in order to reduce the time where writes to the partitioned table will be locked out. In this case, building the partitioned index is a metadata only operation.

https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

I know, this issue is old, and i hate zombie resurrection, but it took a while until this issue reached us in production.

Also: In some cases, unique indexes are created on partitions. This is only possible via template tables.

[keithf4]

I can give this another look as part of the 5.x refactor. It's been a while, so I cannot recall what the complexities were at the time, so they may still be a factor and this may not make it in the initial new release, but I'll definitely give it another look.

[keithf4]

For reference, the 5.x refactor - https://github.com/pgpartman/pg_partman/discussions/360

[keithf4]

Notes for development:

• This function will have to be changed to only reapply unique indexes from the template table. Other indexes on the parent (or others accidentally created on the template) should not be reapplied.
• A separate script/procedure should be made to do the reindex process on all the children. This is not actually reapplying the indexes, it's asking for a REINDEX operation to be done on all existing children