Document fix_pre_citus10_partitioned_table_constraint_names()

[hanefi]

Why are we implementing it? (sales eng)

If a user created a distributed partitioned table on an earlier version of Citus (<10), then added/altered constraints, we can hit a bug that blocks new partition creation. See https://github.com/citusdata/citus/issues/3970

What are the typical use cases?

User:

• creates a Citus Cluster on an earlier version with constraint naming bug (<10)
• creates a distributed partitioned table
• adds/alters some constraints on said table
• upgrades Citus to >=10
• uses the new UDF to fix the wrong constraints on the worker partition table shards.

Communication goals (e.g. detailed howto vs orientation)

Good locations for content in docs structure

Common error messages -> "child table is missing constraint "constraintName_shardId"

an example:

mydb=# CREATE TABLE part_table_p202009 PARTITION OF part_table  FOR VALUES FROM ('2020-09-01 00:00:00') TO ('2020-10-01 00:00:00');
ERROR:  child table is missing constraint "my_seq_103225"
CONTEXT:  while executing command on dev-hanccitus005-ncl:6432

How does this work? (devs)

It sends out the original names of CHECK constraints of partitioned tables to worker nodes, and runs RENAME CONSTRAINT commands if the constraint was erroneously appended a shardId in the earlier versions of Citus.

Example sql

Solutions:

• SELECT fix_pre_citus10_partitioned_table_constraint_names('part_table') to fix constraint names of partition shards of a given distributed partitioned table.
• SELECT fix_pre_citus10_partitioned_table_constraint_names() to fix constraint names of all partition shards.

Corner cases, gotchas

Are there relevant blog posts or outside documentation about the concept/feature?

Link to relevant commits and regression tests if applicable

https://github.com/citusdata/citus/pull/4410

See upgrade test results to understand the behaviour.

• upgrade_partition_constraints_before.out contains the test output of upgrade_partition_constraints_before.sql that is ran on a v8.0.0 cluster.
• upgrade_partition_constraints_after.out contains the test output of upgrade_partition_constraints_after.sql that is ran on the same cluster after an upgrade to the latest version available.

Observe that the usage of the UDF unblocks new partition creation on affected distributed partitioned tables.