[Postgres-xl-general] Help understanding DISTRIBUTE

Pavan Deolasee pavan.deolasee at gmail.com
Wed Mar 30 23:28:24 PDT 2016


On Wed, Mar 30, 2016 at 7:25 PM, Florian Iragne <florian at iragne.fr> wrote:

> Hi,
>
> I try to understand how to use the DISTRIBUTE BY HASH instruction at
> table creation.
>
> I have the folowing schema:
>
> CREATE TABLE bindatabase_bin (
>      id integer DEFAULT nextval('bindatabase_bin_id_seq'::regclass) NOT
> NULL,
>      bindatabase_id integer NOT NULL,
>      bin_id integer NOT NULL,
>      CONSTRAINT bindatabase_bin_pkey PRIMARY KEY (id),
>      CONSTRAINT bindatabase_bin_bin_id FOREIGN KEY (bin_id) REFERENCES
> bin(id) DEFERRABLE INITIALLY DEFERRED,
>      CONSTRAINT bindatabase_bin_bindatabase_id FOREIGN KEY
> (bindatabase_id) REFERENCES bindatabase(id) DEFERRABLE INITIALLY DEFERRED
> ) DISTRIBUTE BY HASH(bindatabase_id);
>
> bindatabase table is created with DISTRIBUTE BY HASH(id)
> bin table is created with DISTRIBUTE BY HASH(id)
>
> every table in my schema has an id column that is its primary key.
>
> I've found a message of someone doing this successfully (or at least,
> appears to be successful), so i wonder if i have wrongly understood how
> to achieve this, or maybe it's just not possible?
>
>
Postgres-XL currently does not support global constraints, both PRIMARY KEY
as well as FOREIGN KEY. That imposes certain restrictions on the way these
constraints can be used.

- The PRIMARY KEY must contain the distribution key. This ensures that the
constraint can be checked and enforced locally on a datanode
- The FOREIGN KEY of the referencing table must be its distribution key and
it must refer to the distribution key of the referred table.

In your example, you can add bindatabase_id to the PRIMARY KEY to tackle
the first problem. The bindatabase_bin_bindatabase_id constraint should
work fine. But I don't see a way to also have the second FK because there
is no way today to enforce both the FKs locally on the datanode.

Now XL has a hidden GUC called "loose_constraints" which can be turned ON
to accept the DDL in its current form. But this must be used with great
caution because XL will only enforce constraints locally, which may or may
not be enough for database integrity.

Another option is to make some tables replicated. For example, if the "bin"
table is not very large and not frequently updated, you can make that a
replicated table.

Thanks,
Pavan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20160331/3f4496dd/attachment.htm>


More information about the postgres-xl-general mailing list