[Postgres-xl-developers] UNIQUE constraint

Pavan Deolasee pavan.deolasee at gmail.com
Thu May 18 00:38:00 PDT 2017

On Thu, May 18, 2017 at 3:53 AM, tiredpixel <tiredpixel at posteo.de> wrote:

> Paulo henrique França Silva (2017-05-17 21:52):
> > Hi there, my name is Paulo, and I'm DBA at tjdft.jus.br
> >
> >
> > We use postgres EDB for our main system. I would like to use Postgres-XL
> as well.
> >
> > I'v been trying to put it to work, but the UNIQUE constraint limitation
> has been a roadblock to us.
> >
> >
> > Is there any plan to be able to create UNIQUE constraints that is no
> part of HASH distribution key?
> >
> > I say that because many tables has unique keys that is no primary keys.
> >
> >
> > Best Regards
> >
> > Paulo H
> Dear Paulo,
> Others may well be able to answer better, but I'm a little confused about
> what you're asking, I'm afraid. Are you asking about whether there could be
> some constraint with cluster scope, rather than node scope? Where something
> is enforced despite not being part of a distribution key? Would such not
> require cross-cluster round-trips on every constraint-check, thus leading
> to abysmal performance for INSERTs and UPDATEs due to the network latency?
Right. Any check that requires cross node roundtrips could be slow.
Moreover we lack that capability today. XL can only enforce node-local
constraints today and hence rejects any attempt to create constraint which
will require cross node verification.

We have a GUC named "loose_constraints" which can be turned ON if the user
is confident that the node-local checks are enough for his purpose. If set,
XL would let you create UNIQUE indexes which don't include the distribution
key. But remember that XL will only enforce the constraint locally. For
example, on a 2-datanode cluster:

postgres=# show loose_constraints ;
(1 row)

postgres=# CREATE TABLE test (a int PRIMARY KEY, b int) DISTRIBUTE BY
postgres=# CREATE UNIQUE INDEX testindx ON test(b);
WARNING:  Unique index of partitioned table must contain the hash/modulo
distribution column.
postgres=# INSERT INTO test VALUES (1, 2), (2,2);
ERROR:  duplicate key value violates unique constraint "testindx"
DETAIL:  Key (b)=(2) already exists.
postgres=# INSERT INTO test VALUES (1, 2), (3,2);

So while first INSERT failed with UNIQUE key violation because both rows
are mapped to the same node, the second INSERT succeeded because those rows
are mapped to two different datanodes and the system could not enforce the

Since we can't enforce such global constraint, we don't recommend to use
the GUC. But if you understand the implications and if the "loose"
behaviour is acceptable to the application, it could be a way to bypass the


 Pavan Deolasee                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20170518/3cc27c6d/attachment.htm>

More information about the Postgres-xl-developers mailing list