Re: pg_restore error with partitioned table having exclude constraint

On Wed, 16 Apr 2025 at 23:11, Keith Paskett <keith(dot)paskett(at)logansw(dot)com> wrote:
> Postgresql 17.4
>
> A table partitioned by LIST with an exclusion constraint errors on creating the constraint on the parent table when doing
> a pg_dump/pg_restore
>
> ERROR:
>
> pg_restore: error: could not execute query: ERROR: cannot attach index
> "pkg708_had_working_hist_context_id_had_person_id_active_ts_excl" as a partition of index "had_working_hist_tsr_excl"
>
> DETAIL: The index definitions do not match.
>
> Command was: ALTER INDEX apps.had_working_hist_tsr_excl ATTACH PARTITION
> apps_part.pkg708_had_working_hist_context_id_had_person_id_active_ts_excl;
>
> TABLE AFTER pg_restore
>
> \d+ apps.had_working_hist
>
> Partitioned table "apps.had_working_hist"
>
> Column | Type | Collation | Nullable | Default |
> Storage | Compression | Stats target | Description
>
> ---------------------+--------------------------+-----------+----------+------------------------------------------------+----------+-------------+--------------+-------------
>
>
> had_working_hist_id | integer | | not null | nextval('apps.had_working_hist_seq'::regclass) |
> plain | | |
>
> context_id | integer | | not null | |
> plain | | |
>
> had_person_id | integer | | not null | |
> plain | | |
>
> comment | text | | | |
> extended | | |
>
> active_tsr | tstzrange | | not null | |
> extended | | |
>
> add_tstz | timestamp with time zone | | not null | CURRENT_TIMESTAMP |
> plain | | |
>
> add_by_id | integer | | not null | |
> plain | | |
>
> mod_tstz | timestamp with time zone | | not null | CURRENT_TIMESTAMP |
> plain | | |
>
> mod_by_id | integer | | not null | |
> plain | | |
>
> Partition key: LIST (context_id)
>
> Indexes:
>
> "had_working_hist_pkey" PRIMARY KEY, btree (had_working_hist_id, context_id)
>
> "had_working_hist_add_by_id_idx" btree (add_by_id)
>
> "had_working_hist_had_person_id_idx" btree (had_person_id)
>
> "had_working_hist_mod_by_id_idx" btree (mod_by_id)
>
> "had_working_hist_tsr_excl" EXCLUDE USING gist (context_id WITH =, had_person_id WITH =, active_tsr WITH &&) INVALID
>
> Foreign-key constraints:
>
> "had_working_hist__add_by_id_fk" FOREIGN KEY (add_by_id) REFERENCES persons(person_id)
>
> "had_working_hist__context_id_fk" FOREIGN KEY (context_id) REFERENCES apm_packages(package_id)
>
> "had_working_hist__had_person_id_fk" FOREIGN KEY (had_person_id, context_id) REFERENCES apps.had_person
> (had_person_id, context_id)
>
> "had_working_hist__mod_by_id_fk" FOREIGN KEY (mod_by_id) REFERENCES persons(person_id)
>
> Partitions: apps_part.pkg708_had_working_hist FOR VALUES IN (708)
>

Hi, Keith

I can replicate this issue on the current master branch. After some investigation,
I found the following code at the end of the CompareIndexInfo() function:

/* No support currently for comparing exclusion indexes. */
if (info1->ii_ExclusionOps != NULL || info2->ii_ExclusionOps != NULL)
return false;

I believe this is why the exclusion index is rejected. Commit 8b08f7d482
introduces a change that disables the creation of exclusion constraints on
partitioned tables, while commit 8c852ba9a4 allows some exclusion consistency
on partitions.

Here is a patch to fix it. It just compares the OIDs of two exclusion constraints.