From 96c6dddfbee75200321ad6a7d0eee6c49a544f74 Mon Sep 17 00:00:00 2001 From: jian he Date: Wed, 18 Sep 2024 10:29:46 +0800 Subject: [PATCH v6 1/1] virtual generated columns misc changes. * change tests on contrib/pageinspect/sql/page.sql * also change relcache.c make cached constr->has_not_null, constr->has_generated_stored, constr->has_generated_virtual more accurate. Discussion: https://www.postgresql.org/message-id/flat/a368248e-69e4-40be-9c07-6c3b5880b0a6@eisentraut.org --- contrib/pageinspect/expected/page.out | 20 +++++++++++-------- contrib/pageinspect/sql/page.sql | 8 ++++++-- src/backend/commands/tablecmds.c | 2 +- src/backend/utils/cache/relcache.c | 12 +++++++++-- .../regress/expected/generated_virtual.out | 4 ++-- 5 files changed, 31 insertions(+), 15 deletions(-) diff --git a/contrib/pageinspect/expected/page.out b/contrib/pageinspect/expected/page.out index bacd371a9d..02b1bbb1ab 100644 --- a/contrib/pageinspect/expected/page.out +++ b/contrib/pageinspect/expected/page.out @@ -212,10 +212,12 @@ drop table test8; -- stored create table test9s (a int not null, b int generated always as (a * 2) stored); insert into test9s values (131584); -select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9s', 0)); - t_infomask | t_bits | t_data -------------+--------+-------------------- - 2048 | | \x0002020000040400 +select sub.raw_flags as t_infomask_info, t_bits is null as expect_true, t_data +from heap_page_items(get_raw_page('test9s', 0)), + heap_tuple_infomask_flags(t_infomask, 0) sub; + t_infomask_info | expect_true | t_data +---------------------+-------------+-------------------- + {HEAP_XMAX_INVALID} | t | \x0002020000040400 (1 row) select tuple_data_split('test9s'::regclass, t_data, t_infomask, t_infomask2, t_bits) @@ -229,10 +231,12 @@ drop table test9s; -- virtual create table test9v (a int not null, b int generated always as (a * 2) virtual); insert into test9v values (131584); -select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9v', 0)); - t_infomask | t_bits | t_data -------------+----------+------------ - 2049 | 10000000 | \x00020200 +select sub.raw_flags as t_infomask_info, t_bits is not null as expect_true, t_data +from heap_page_items(get_raw_page('test9v', 0)), + heap_tuple_infomask_flags(t_infomask, 0) sub; + t_infomask_info | expect_true | t_data +----------------------------------+-------------+------------ + {HEAP_HASNULL,HEAP_XMAX_INVALID} | t | \x00020200 (1 row) select tuple_data_split('test9v'::regclass, t_data, t_infomask, t_infomask2, t_bits) diff --git a/contrib/pageinspect/sql/page.sql b/contrib/pageinspect/sql/page.sql index 923687d063..81866db25a 100644 --- a/contrib/pageinspect/sql/page.sql +++ b/contrib/pageinspect/sql/page.sql @@ -88,7 +88,9 @@ drop table test8; -- stored create table test9s (a int not null, b int generated always as (a * 2) stored); insert into test9s values (131584); -select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9s', 0)); +select sub.raw_flags as t_infomask_info, t_bits is null as expect_true, t_data +from heap_page_items(get_raw_page('test9s', 0)), + heap_tuple_infomask_flags(t_infomask, 0) sub; select tuple_data_split('test9s'::regclass, t_data, t_infomask, t_infomask2, t_bits) from heap_page_items(get_raw_page('test9s', 0)); drop table test9s; @@ -96,7 +98,9 @@ drop table test9s; -- virtual create table test9v (a int not null, b int generated always as (a * 2) virtual); insert into test9v values (131584); -select t_infomask, t_bits, t_data from heap_page_items(get_raw_page('test9v', 0)); +select sub.raw_flags as t_infomask_info, t_bits is not null as expect_true, t_data +from heap_page_items(get_raw_page('test9v', 0)), + heap_tuple_infomask_flags(t_infomask, 0) sub; select tuple_data_split('test9v'::regclass, t_data, t_infomask, t_infomask2, t_bits) from heap_page_items(get_raw_page('test9v', 0)); drop table test9v; diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index b01e431355..03f9376d8b 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -9731,7 +9731,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel, { char attgenerated = TupleDescAttr(RelationGetDescr(rel), fkattnum[i] - 1)->attgenerated; - if (attgenerated) + if (attgenerated == ATTRIBUTE_GENERATED_STORED) { /* * Check restrictions on UPDATE/DELETE actions, per SQL standard diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index c8a711cb7e..a421ee6d38 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -3541,6 +3541,8 @@ RelationBuildLocalRelation(const char *relname, int natts = tupDesc->natts; int i; bool has_not_null; + bool has_generated_stored; + bool has_generated_virtual; bool nailit; Assert(natts >= 0); @@ -3617,6 +3619,8 @@ RelationBuildLocalRelation(const char *relname, rel->rd_att = CreateTupleDescCopy(tupDesc); rel->rd_att->tdrefcount = 1; /* mark as refcounted */ has_not_null = false; + has_generated_stored = false; + has_generated_virtual = false; for (i = 0; i < natts; i++) { Form_pg_attribute satt = TupleDescAttr(tupDesc, i); @@ -3626,13 +3630,17 @@ RelationBuildLocalRelation(const char *relname, datt->attgenerated = satt->attgenerated; datt->attnotnull = satt->attnotnull; has_not_null |= satt->attnotnull; + has_generated_stored |= (satt->attgenerated == ATTRIBUTE_GENERATED_STORED); + has_generated_virtual |= (satt->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL); } - if (has_not_null) + if (has_not_null || has_generated_stored || has_generated_virtual) { TupleConstr *constr = (TupleConstr *) palloc0(sizeof(TupleConstr)); - constr->has_not_null = true; + constr->has_not_null = has_not_null; + constr->has_generated_stored = has_generated_stored; + constr->has_generated_virtual = has_generated_virtual; rel->rd_att->constr = constr; } diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 4cf4f8118f..c32ef7f787 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -678,9 +678,9 @@ CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); CREATE TABLE gtest23a (x int PRIMARY KEY, y int); --INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33); CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error -ERROR: invalid ON UPDATE action for foreign key constraint containing generated column +ERROR: foreign key constraints on virtual generated columns are not supported CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x) ON DELETE SET NULL); -- error -ERROR: invalid ON DELETE action for foreign key constraint containing generated column +ERROR: foreign key constraints on virtual generated columns are not supported CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) VIRTUAL REFERENCES gtest23a (x)); ERROR: foreign key constraints on virtual generated columns are not supported --\d gtest23b -- 2.34.1