On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@gmail.com> wrote:
> 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the
non-partitioneddefinition of the table) once to document and enforce that the partitions simply inherit the id pk. This
wouldseem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be
definedas pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this
practical/valid?
Unfortunately, it's not as easy as you think. It's not a matter of
dropping the check that requires all PRIMARY KEY columns are present
in the PARTITION BY clause. For this to work a *single* index (i.e.
non-partitioned index) would have to index all partitions. Otherwise,
how would Postgres ensure that the value being inserted doesn't exist
in some other partition?
The problem with a single index is that it kinda defeats the purpose
of partitioning, i.e., "my table is large and I want to split it up".
Operations such as DETACH PARTITION would have to become more than
just a metadata operation when you consider having to trawl through
the index and remove all records belonging to a single partition.
It may be possible to still have it work by doing a speculative record
in the index for the target table then go and check all of the other
indexes before marking the speculative entry as valid. I think it
would be very tricky to make it work well, however. Imagine how
expensive ATTACH PARTITION would be! There are probably other race
conditions I've not thought about too. Likely, we'd get more
complaints about this being a terrible feature than we do due to the
fact that it's unsupported.
David
