Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Mon, 19 Feb 2024 at 22:07, Darryl Green <darryl(dot)green(at)gmail(dot)com> wrote:
>
> On Mon, 19 Feb 2024 at 14:23, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> >
> > On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl(dot)green(at)gmail(dot)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-partitioned definition of the table) once to document and enforce that the partitions simply inherit the id pk. This would seem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be defined as 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.
>
> Now that would be bad. And I do understand that is exactly why in the general case the existing rule is correct. You snipped my specific usage/question which was about the uniqueness of a column that was specified as "id int generated always as identity not null" and relying on all partitions using the same sequence to populate to guarantee that there are never(*) duplicates in other partitions.

Using such proofs that an index can only ever contain unique values is
just not worth talking about. It's just nowhere near project standard.
We'd just be forever answering questions on this list from people with
unique violation problems. There are just too many reasons to list for
ways this could go wrong.

David