This release includes a feature I added [1] to support partial foreign key updates in referential integrity triggers!<p>This is useful for schemas that use a denormalized tenant id across multiple tables, as might be common in a multi-tenant application:<p><pre><code> CREATE TABLE tenants (id serial PRIMARY KEY);
CREATE TABLE users (
tenant_id int REFERENCES tenants ON DELETE CASCADE,
id serial,
PRIMARY KEY (tenant_id, id),
);
CREATE TABLE posts (
tenant_id int REFERENCES tenants ON DELETE CASCADE,
id serial,
author_id int,
PRIMARY KEY (tenant_id, id),
FOREIGN KEY (tenant_id, author_id)
REFERENCES users ON DELETE SET NULL
);
</code></pre>
This schema has a problem. When you delete a user, it will try to set both the tenant_id and author_id columns on the posts table to NULL:<p><pre><code> INSERT INTO tenants VALUES (1);
INSERT INTO users VALUES (1, 101);
INSERT INTO posts VALUES (1, 201, 101);
DELETE FROM users WHERE id = 101;
ERROR: null value in column "tenant_id" violates not-null constraint
DETAIL: Failing row contains (null, 201, null).
</code></pre>
When we delete a user, we really only want to clear the author_id column in the posts table, and we want to leave the tenant_id column untouched. The feature I added is a small syntax extension to support doing exactly this. You can provide an explicit column list to the ON DELETE SET NULL / ON DELETE SET DEFAULT actions:<p><pre><code> CREATE TABLE posts (
tenant_id int REFERENCES tenants ON DELETE CASCADE,
id serial,
author_id int,
PRIMARY KEY (tenant_id, id),
FOREIGN KEY (tenant_id, author_id)
-- Clear only author_id, not tenant_id
REFERENCES users ON DELETE SET NULL (author_id)
-- ^^^^^^^^^^^
);
</code></pre>
I initially encountered this problem while converting a database to use composite primary keys in preparation for migrating to Citus [2], and it required adding custom triggers for every single foreign key we created. Now it can be handled entirely by Postgres!<p>[1]: <a href="https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV%3DnjbSMxf%2BrbDHpx%3DW%3DB7AEaMKn8dWn9OZJY7w%40mail.gmail.com" rel="nofollow">https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV%3D...</a><p>[2]: <a href="https://www.citusdata.com/" rel="nofollow">https://www.citusdata.com/</a>