CODEWITHSUNDEEP
citus
Alexis Hassler
Alexis Hassler

Reputation: 751

Cannot create distributed table with foreign key on Citus 11.1

I want to migrate from Citus 11.0 to 11.1. I'm using the docker-compose file from Citus' github.

The following script works fine on 11.0, but not on 11.1:

create table tmp_table1 (
    id varchar(36) not null,
    tenant_key varchar(36) not null,
    value varchar(255),
    primary key (tenant_key, id)
);
create table tmp_table2 (
    id varchar(36) not null,
    tenant_key varchar(36) not null,
    value varchar(255),
    primary key (tenant_key, id)
);
alter table tmp_table2 add constraint fkxxx foreign key (tenant_key, id) references tmp_table1;

SELECT create_distributed_table('tmp_table1', 'tenant_key');
SELECT create_distributed_table('tmp_table2', 'tenant_key', colocate_with => 'tmp_table1');

The last line gives the following error message:

ERROR:  complex joins are only supported when all distributed tables are co-located 
    and joined on their distribution columns
CONTEXT:  SQL statement "SELECT fk."tenant_key", fk."id" FROM ONLY "public"."tmp_table2" fk 
    LEFT OUTER JOIN ONLY "public"."tmp_table1" pk ON ( pk."tenant_key"::pg_catalog.text 
    OPERATOR(pg_catalog.=) fk."tenant_key"::pg_catalog.text AND pk."id"::pg_catalog.text 
    OPERATOR(pg_catalog.=) fk."id"::pg_catalog.text) WHERE pk."tenant_key" IS NULL 
    AND (fk."tenant_key" IS NOT NULL AND fk."id" IS NOT NULL)"

What is wrong in my script that make it fail on Citus 11.1 ?

Upvotes: 2

Views: 511

Answers (1)

Codewarrior
Codewarrior

Reputation: 121

Try changing the varchar to uuid.

create table tmp_table1 (
    id uuid not null,
    tenant_key uuid not null,
    primary key (tenant_key, id)
);
create table tmp_table2 (
     id uuid not null,
     tenant_key uuid not null,
     primary key (tenant_key, id)
);

SELECT create_distributed_table('tmp_table1', 'tenant_key');
SELECT create_distributed_table('tmp_table2', 'tenant_key', colocate_with => 'tmp_table1');
alter table tmp_table2 add constraint fk21 foreign key (tenant_key, id) references tmp_table1;

this works.

I created an issue https://github.com/citusdata/citus_docs/issues/1068.

Upvotes: 1

Related Questions