Distribution key as varchar causing problems

[hghammoud]

I am currently facing an issue with tenant_id being a varchar instead of integer. I simplified the example 👍

This works

CREATE TABLE event (
  tenant_id int,
  id bigint,
  primary key (tenant_id, id)
);

CREATE TABLE page (
  tenant_id int,
  id int,
  primary key (tenant_id, id)
);

SELECT create_distributed_table('event', 'tenant_id');
SELECT create_distributed_table('page', 'tenant_id', colocate_with => 'event');
alter table page add constraint fk21 foreign key (tenant_id, id) references event;

this does NOT work

CREATE TABLE event (
  tenant_id varchar,
  id bigint,
  primary key (tenant_id, id)
);

CREATE TABLE page (
  tenant_id varchar,
  id int,
  primary key (tenant_id, id)
);

SELECT create_distributed_table('event', 'tenant_id');
SELECT create_distributed_table('page', 'tenant_id', colocate_with => 'event');
alter table page add constraint fk21 foreign key (tenant_id, id) references event;

I am getting the following error

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_id", fk."id" FROM ONLY "public"."page" fk LEFT OUTER JOIN ONLY "public"."event" pk ON ( pk."tenant_id"::pg_catalog.text OPERATOR(pg_catalog.=) fk."tenant_id"::pg_catalog.text AND pk."id"::pg_catalog.text OPERATOR(pg_catalog.=) fk."id"::pg_catalog.text) WHERE pk."tenant_id" IS NULL AND (fk."tenant_id" IS NOT NULL AND fk."id" IS NOT NULL)"
while executing command on docker-citus_worker-1:5432

I am using the docker-compose found on the root of the github (citus 11.1)

What am I missing?

thank you.

[TsinghuaLucky912]

Dear Hackers, I have investigated this issue, and now I will tell you the results of my investigation for your reference:

0. Environment: Ubuntu22+pg15.0+citus main
1. I think the root cause of the error is: there is no pg_operator tuple for varchar type in postgresql (if the two tables here use text type, there is no such problem, because pg_operator has the content of text=text and its oid =98)
2. Because of the reason in 1, the generate_operator_clause function converts the varchar type to the text type, which leads to the following error. If you execute this select command (manually replace the text type with the varchar type, the query can be successful)
3. If you have any new ideas, please let me know, and I will see if I can solve this problem. Thank you!

src/backend/utils/adt/ruleutils.c

void
generate_operator_clause(StringInfo buf,
						 const char *leftop, Oid leftoptype,
						 Oid opoid,
						 const char *rightop, Oid rightoptype)
{
    // here
}

[marcocitus]

I feel like there are 2 separate issues here:

1. Co-located LEFT JOIN is erroring out for varchar distribution columns, edit: +when casting the column to text
2. We should not be doing the LEFT JOIN to begin with because we should skip validation when creating the foreign key on shell tables

[TsinghuaLucky912]

Hello, I don't quite understand your meaning. At present, such SQL statements (left join) do not report errors

[marcocitus]

I think the issue is specifically with the cast from varchar to text in the join clause.

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

[TsinghuaLucky912]

I think the issue is specifically with the cast from varchar to text in the join clause.

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

Yes, if you still keep the varchar type, there is no problem. But this doesn't seem to be caused by citus, and maybe I haven't found it (if you know, you can tell me)

[TsinghuaLucky912]

@marcocitus My feeling is that the problem is not citus, but PostgreSQL's missing handling of varchar types

[JelteF]

The commit that introduced this regression is: eadc88a

[JelteF]

I have a fix ready in #6550