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

[marcocitus]

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)

[marcocitus]

We no longer get SPI errors when creating a foreign key to a reference table that is replicated to the coordinator. However, ON UPDATE/DELETE do not work correctly. We should probably map the foreign key directly to the shard.