From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | foreign key on virtual generated column |
Date: | 2025-05-28 05:13:50 |
Message-ID: | CACJufxEfzfERVwoUaFODz9AOabDOLkNkfTbajB82dSJVuTQQOw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
hi.
attached patch is implement a TODO (foreign key on virtual generated
column) left on [1]
for foreign key on virtual generated column, we only support
ON UPDATE NO ACTION
ON UPDATE RESTRICT
ON DELETE CASCADE
ON DELETE NO ACTION
ON DELETE RESTRICT
demo:
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33), (131072, 44);
CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a
* 1) VIRTUAL REFERENCES gtest23a (x) ON DELETE CASCADE); --ok
INSERT INTO gtest23b VALUES (1); -- ok
INSERT INTO gtest23b VALUES (5); -- error
UPDATE gtest23b SET a = 5 WHERE a = 1; --error
DELETE FROM gtest23a WHERE x = 1; --ok
ALTER TABLE ALTER COLUMN SET EXPRESSION
ALTER TABLE ALTER COLUMN SET DATA TYPE
if foreign key on virtual generated column, the above two will not cause table
rewrite,but will do foreign key constraint validation.
[1] https://git.postgresql.org/cgit/postgresql.git/commit/?id=83ea6c54025bea67bcd4949a6d58d3fc11c3e21b
Attachment | Content-Type | Size |
---|---|---|
v1-0002-foreign-key-on-virtual-generated-column.patch | text/x-patch | 26.9 KB |
v1-0001-rename-ExecComputeStoredGenerated-to-ExecComputeGenerated.patch | text/x-patch | 5.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Masahiko Sawada | 2025-05-28 06:25:18 | Re: Replication slot is not able to sync up |
Previous Message | Zhijie Hou (Fujitsu) | 2025-05-28 04:15:49 | RE: Replication slot is not able to sync up |