Обсуждение: Clarify restriction on partitioned tables primary key / unique indexes

Over on [1], there was a question about why it wasn't possible to
create the following table:

CREATE TABLE foobar(
    id BIGINT NOT NULL PRIMARY KEY,
    baz VARCHAR NULL DEFAULT NULL
) PARTITION BY HASH(my_func(id));

The above is disallowed by 2 checks in DefineIndex().

1. If the partitioned key contains an expression we disallow the
addition of the constraint, per:

/*
* It may be possible to support UNIQUE constraints when partition
* keys are expressions, but is it worth it?  Give up for now.
*/
if (key->partattrs[i] == 0)
    ereport(ERROR,

2. We insist that the primary key / unique constraint contain all of
the columns that the partitioned key does.

We only mention #2 in the docs [2], but we don't mention anything
about if the columns can be part of a function call or expression or
not, per:

"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."

The attached attempts to clarify these restrictions more accurately
based on the current code's restrictions.

If there's no objections or suggestions for better wording, I'd like
to commit the attached.

David


[1] https://www.postgresql.org/message-id/CAH7vdhNF0EdYZz3GLpgE3RSJLwWLhEk7A_fiKS9dPBT3Dz_3eA@mail.gmail.com
[2] https://www.postgresql.org/docs/devel/ddl-partitioning.html

Op 02-09-2022 om 11:44 schreef David Rowley:
> Over on [1], there was a question about why it wasn't possible to
> create the following table:
> 
> CREATE TABLE foobar(
>      id BIGINT NOT NULL PRIMARY KEY,
>      baz VARCHAR NULL DEFAULT NULL
> ) PARTITION BY HASH(my_func(id));
> 
> 
> The attached attempts to clarify these restrictions more accurately
> based on the current code's restrictions.
> 
> If there's no objections or suggestions for better wording, I'd like
> to commit the attached.

Minimal changes:

'To create a unique or primary key constraints on partitioned table'

should be

'To create unique or primary key constraints on partitioned tables'


Erik

On Fri, 2 Sept 2022 at 22:01, Erik Rijkers <er@xs4all.nl> wrote:
> Minimal changes:
>
> 'To create a unique or primary key constraints on partitioned table'
>
> should be
>
> 'To create unique or primary key constraints on partitioned tables'

Thanks.  I ended up adjusting it to:

"To create a unique or primary key constraint on a partitioned table,"

David

On Fri, 2 Sept 2022 at 22:06, David Rowley <dgrowleyml@gmail.com> wrote:
> Thanks.  I ended up adjusting it to:
>
> "To create a unique or primary key constraint on a partitioned table,"

and pushed.

Thanks for having a look at this Erik.

David