Re: Simple task with partitioning which I can't realize

On Tue, Mar 1, 2022 at 8:37 AM Andrew Zakharov <Andrew898(at)mail(dot)ru> wrote:

> create table region_hierarchy(
>
> gid uuid not null default uuid_generate_v1mc(),
>
> parent_gid uuid null,
>
> region_code int2,
>
>
>

> I’ve carefully looked thru docs/faqs/google/communities and found out that
> I must include “gid” field into partition key because a primary key field.
>

Yes, you are coming up against the following limitation:

"Unique constraints (and hence primary keys) on partitioned tables must
include all the partition key columns. This limitation exists because the
individual indexes making up the constraint can only directly enforce
uniqueness within their own partitions; therefore, the partition structure
itself must guarantee that there are not duplicates in different
partitions."

https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

That limitation is independent of partitioning; i.e., the legacy
inheritance option doesn't bypass it.

Thus, your true "key" is composite: (region, identifier). Thus you need to
add a "parent_region_code" column as well, redefine the PK as (region_code,
gid), and the REFERENCES clause to link the two paired fields.

You can decide whether that is sufficient or if you want some added comfort
in ensuring that a gid cannot appear in multiple regions by creating a
single non-partitioned table containing all gid values and add a unique
constraint there.

Or maybe allow for duplicates across region codes and save space by using a
smaller data type (int or bigint - while renaming the column to "rid" or
some such) - combined with having the non-partitioned reference table being
defined as (region_code, rid, gid).

David J.