agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: PG Doc comments form <noreply@postgresql.org>
To: pgsql-docs@lists.postgresql.org
Cc: mark@markandruth.co.uk
Subject: Documentation correction suggestion for primary key hashing on partitioned tables
Date: Thu, 24 Apr 2025 09:48:25 +0000
Message-ID: <174548810572.676.2745231019536109132@wrigleys.postgresql.org> (raw)
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/17/ddl-partitioning.html
Description:
https://www.postgresql.org/docs/current/ddl-partitioning.html / 5.12.2.3.
Limitations says:
> To create a unique or primary key constraint on a partitioned table, the
partition keys must not include any expressions or function calls and the
constraint's columns must include all of 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.
This seems to imply that if I have a table of primary key (a, b, c) in
b-tree index, then a partition-by-hash method would require this full set of
columns to be used in order for the constraints to be enforcable. However
logically this should not be the case - only a hash of a prefix of the
primary key - eg (a) or (a, b) should be needed to target the same table
each time and hence get the constraint enforced. Testing this in postgres 16
seems to show that it does work like this.
view thread (2+ messages) latest in thread
Message-ID: <174548810572.676.2745231019536109132@wrigleys.postgresql.org>
Permalink: ../174548810572.676.2745231019536109132@wrigleys.postgresql.org/
Also on: postgresql.org/message-id/174548810572.676.2745231019536109132@wrigleys.postgresql.org
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: noreply@postgresql.org, pgsql-docs@lists.postgresql.org, mark@markandruth.co.uk
Subject: Re: Documentation correction suggestion for primary key hashing on partitioned tables
In-Reply-To: <174548810572.676.2745231019536109132@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox