Support foreign key ON DELETE SET NULL (col)

[DS-AdamMilazzo]

Citus currently doesn't allow foreign keys on distributed tables to use ON DELETE SET NULL (or SET DEFAULT) when the distribution column is in the referencing columns (as it generally is) because, per @onderkalaci, Citus doesn't support NULL values for the distribution column. However, PostgreSQL 15+ supports the syntax ON DELETE SET NULL (col1, col2, ...) where it will only set the specified columns to NULL (ditto for SET DEFAULT). Citus could support this syntax and allow foreign keys to be created when the distribution column is not in the list of columns to set null.

For example, this currently fails:

CREATE TABLE t (
    p INT NOT NULL,
    i INT NOT NULL,
    r INT,
    PRIMARY KEY (p, i)
);
SELECT create_distributed_table('t', 'p');
ALTER TABLE t ADD FOREIGN KEY (p, r) REFERENCES t (p, i) ON DELETE SET NULL (r);

with this error:

ERROR:  cannot create foreign key constraint
DETAIL:  SET NULL or SET DEFAULT is not supported in ON DELETE operation when distribution key is included
in the foreign key constraint

It'd be really nice if it worked. Otherwise, it's hard in some cases to guarantee referential integrity in Citus databases.

Thank you.

[onurctirtir]

I'm noting this as a feature request, thank you for opening this issue!

[onderkalaci]

marking as relevant to https://github.com/citusdata/citus/issues/6162 where this limitation is listed.

And, it turns out we already added support for reference/citus local tables: https://github.com/citusdata/citus/pull/6319

[DS-AdamMilazzo]

Hello, again. Can you guys provide any kind of an ETA on this? Thank you.

[onderkalaci]

@onurctirtir might have a better idea