Re: unique indexes on partitioned tables

Hello,

Thanks, Peter, Jesper, Amit, for reviewing the patch. Replying to
all review comments at once:

Jesper Pedersen wrote:

> Maybe add a test case to indexing.sql that highlights that hash indexes
> doesn't support UNIQUE; although not unique to partitioned indexes.

I'm not sure about this. If one day unique is supported by hash, why
would this test have to be modified? Other than to add a few relevant
test cases, that is! Lack of support is already tested elsewhere (one
hopes).

Peter Eisentraut wrote:

> + if (key->partattrs[i] == 0)
> + ereport(ERROR,
> + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("unsupported %s constraint with
> partition key definition",
> + constraint_type),
> + errmsg("%s constraints cannot be used when
> partition keys include expressions.",
> + constraint_type)));
>
> Double errmsg(). (Maybe an Assert somewhere should help catch this?)

Ooh, great catch! Fixed.

> +alter table idxpart add primary key (a); -- not an incomplete one tho
>
> "though"?

Fixed :-)

> I would like to see some tests that the unique constraints are actually
> enforced. That is, insert some duplicate values and see it fail. Throw
> some null values in, to check PK behavior as well. It should be
> trivial, but seems kind of useful.

Added.

Amit Langote said:

> That said, I think that it might be a good idea to include the above
> detail in the documentation of CREATE INDEX and ALTER TABLE ADD UNIQUE.

Added some text there.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services