Foreign keys from regular to distributed tables trigger failures in SPI queries
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Because we only intercept `ALTER TABLE` commands that are performed *on* distributed tables, it's currently possible to create a foreign key on a regular table that points *to* a distributed table.
Since foreign keys are implemented using SPI, Citus intercepts the underlying queries used to check whether the foreign key holds and throws errors because the queries are unsupported.
```
postgres=# CREATE TABLE ref (y int primary key, name text);
postgres=# SELECT create_distributed_table('ref', 'y');
postgres=# CREATE TABLE test (x int, y int);
postgres=# ALTER TABLE test ADD CONSTRAINT ref FOREIGN KEY(x) REFERENCES ref(y);
ERROR: cannot plan queries that include both regular and partitioned relations
CONTEXT: SQL statement "SELECT fk."x" FROM ONLY "public"."test" fk LEFT OUTER JOIN ONLY "public"."ref" pk ON ( pk."y" OPERATOR(pg_catalog.=) fk."x") WHERE pk."y" IS NULL AND (fk."x" IS NOT NULL)"
postgres=# ALTER TABLE test ADD CONSTRAINT ref FOREIGN KEY(x) REFERENCES ref(y) NOT VALID;
postgres=# INSERT INTO test VALUES (1,1);
ERROR: cannot perform distributed planning on this query
DETAIL: For Update/Share commands are currently unsupported
CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."ref" x WHERE "y" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
```
We could actually support these queries, with some risk of losing this feature when the underlying implementation in PostgreSQL changes. Otherwise, we should probably have a more appropriate error message.
(low priority, more of a curiosity)
Contributor guide
Assessment
This issue has not been assessed yet.