Reapply all configuration from template table to old partitions (PG11)

[hedgss]

Hi,

I use partman 4.2.2. for PostgreSQL 11 in native partitioning mode with a template table.

1. Reapplying all configuration from template table to old partitions. I just want to check that I understand this concept right. All changes in the template table will be applied automatically for the partitions. But how to apply all changes for already existing partitions? What can be changed:

1. indexes
2. constrains
3. permissions
4. Other Alter table ... commands

Option 1, 4. will be handled automatically by PostgreSQL. All changes should be applied to the main table Option 2 can be handled by script check_unique_constraint.py Option 3 can be handled by reapply_privileges(). It applies all permissions from the main table to all partitons. e.g.

select partman.reapply_privileges('payment');

2. Unique constraints for partitions I checked the second option and it doesn't work for me as expected. I have a partitoned table payment. All partions had UNIQUE index. I deleted manually a unique index from one partition

The index DDL

CREATE UNIQUE INDEX
    payment_p2020_06_instance_key_idx
ON
    payment_p2020_06
USING
    btree
    (
        instance_key
    );

I executed command, but it hasn't found that instance_key doesn't have UNIQUE constraint in the table payment_p2020_07. Is it a bug?

# check_unique_constraint.py -p payment -l instance_key -c "user=myuser" --index_scan
Dumping out column data to temp file...
Checking for dupes...
No constraint violations found

[keithf4]

If you need to re-create indexes, you should be using the reapply_indexes.py script. As you said, with native partitioning most indexes should be handled if you just apply them to the parent. But if it's a unique index or primary key that doesn't include the partition key, then you cannot apply it to the native parent and must use the template table. The reapply_indexes.py script will use the template table properly to do this.

The check_unique_contraint.py script is for checking that the data for a given column is in fact unique across the entire partition set since postgresql cannot currently enforce that for columns that are not part of the partition key.

Also, just FYI, for Option 3 and privilege inheritance, this is only actually required if you need direct access to the child tables without going through the parent. If you do not need that, then any child table's data in a native set should be available through the parent without having to do any additional work with managing privileges.