Re: unique indexes on partitioned tables

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: unique indexes on partitioned tables
Date: 2018-02-12 20:26:15
Message-ID: 20180212202615.q33ula7dpeyti2ee@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


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

Peter Eisentraut wrote:

> + if (key->partattrs[i] == 0)
> + ereport(ERROR,
> + 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.


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
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
v5-0001-allow-indexes-on-partitioned-tables-to-be-unique.patch text/plain 71.8 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2018-02-12 20:30:26 rename sgml files?
Previous Message Thomas Munro 2018-02-12 20:23:52 Re: Removing shm_mq.c's volatile qualifiers