Обсуждение: foreign key on virtual generated column

Поиск
Список
Период
Сортировка

foreign key on virtual generated column

От
jian he
Дата:
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

Вложения

Re: foreign key on virtual generated column

От
Srinath Reddy Sadipiralla
Дата:
Hi Jian , Thanks for working on this , I started reviewing these patches ,
just need a very small rebase to v1-0002 because a recent
commit(cc2ac0e) removed catalog/pg_proc.h header from ri_triggers.c .

--
Thanks,
Srinath Reddy Sadipiralla
EDB: https://www.enterprisedb.com/