citusdata / citusdata/citus

Foreign keys from regular to distributed tables trigger failures in SPI queries

Open
#1,051 1 comment 0 reactions 0 assignees View on GitHub
bug feature
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.