[Postgres-xl-developers] UNIQUE constraint

Paulo henrique França Silva phmasters at hotmail.com
Fri May 19 10:43:37 PDT 2017


>On Thu, May 18, 2017 at 3:53 AM, tiredpixel <tiredpixel at ...> 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 ;
>  loose_constraints
> -------------------
>  on
> (1 row)

> postgres=# CREATE TABLE test (a int PRIMARY KEY, b int) DISTRIBUTE BY
> HASH(a);
> CREATE TABLE
> postgres=# CREATE UNIQUE INDEX testindx ON test(b);
> WARNING:  Unique index of partitioned table must contain the hash/modulo
> distribution column.
> CREATE INDEX
> 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);
> INSERT 0 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
> constraint.

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

> Thanks,
> Pavan

> --
> Pavan Deolasee                   http://www.2ndQuadrant.com/<http://www.2ndquadrant.com/>
> PostgreSQL Development, 24x7 Support, Training & Services

Hi Pavan tiredpixel,

 Thank you very much for your response.

One question, and if we store the key data in coordinator? So we wouldn't need to do  round-trips? If you think that it is possible, I could make a patch. I'm studying the code and willing to help.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-developers-postgres-xl.org/attachments/20170519/8ae41fc5/attachment.htm>


More information about the Postgres-xl-developers mailing list