On 3/1/22 10:54, David G. Johnston wrote:
On Tue, Mar 1, 2022 at 8:37 AM Andrew Zakharov <andrew...@mail.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.
Hi David,
Are there any concrete plans to address that particular limitation? That
limitation can be re-stated as "PostgreSQL doesn't support global
indexes on the partitioned tables" and I've have also run into it. My
way around it was not to use partitioning but to use much larger machine
with the NVME disks, which can handle the necesary I/O. Are there any
plans to allow global indexes? I am aware that this is not a small
change but is the only real advantage that Oracle holds over PostgreSQL.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
