This is an automated email from the ASF dual-hosted git repository. tuhaihe pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit f5dd3b2cbbce4f0da84f374c6977465846680e9c Author: liushengsong <[email protected]> AuthorDate: Fri May 22 17:33:25 2026 +0800 Enable pg_depend corruption defense tests in create_view Uncomment the two test blocks that verify view behavior when pg_depend entries are corrupted (DELETE FROM pg_depend + ALTER TABLE + ROLLBACK). These were previously disabled because DELETE FROM pg_depend only affects the coordinator in a distributed environment. The fix uses allow_segment_DML GUC combined with a helper function marked EXECUTE ON ALL SEGMENTS to delete from segment catalogs as well. --- .../src/test/regress/expected/create_view.out | 169 +++++++++++++++------ .../regress/expected/create_view_optimizer.out | 169 +++++++++++++++------ .../src/test/regress/sql/create_view.sql | 121 +++++++++------ src/test/regress/expected/create_view.out | 169 +++++++++++++++------ .../regress/expected/create_view_optimizer.out | 169 +++++++++++++++------ src/test/regress/sql/create_view.sql | 121 +++++++++------ .../singlenode_regress/expected/create_view.out | 163 ++++++++++++++------ src/test/singlenode_regress/sql/create_view.sql | 121 +++++++++------ 8 files changed, 826 insertions(+), 376 deletions(-) diff --git a/contrib/pax_storage/src/test/regress/expected/create_view.out b/contrib/pax_storage/src/test/regress/expected/create_view.out index d9f97043b64..9fea9056fae 100644 --- a/contrib/pax_storage/src/test/regress/expected/create_view.out +++ b/contrib/pax_storage/src/test/regress/expected/create_view.out @@ -1654,62 +1654,137 @@ alter table tt14t drop column f3; -- fail, view has explicit reference to f3 ERROR: cannot drop column f3 of table tt14t because other objects depend on it DETAIL: view tt14v depends on column f3 of table tt14t HINT: Use DROP ... CASCADE to drop the dependent objects too. --- MERGE16_FIXME: delete command can only delete tuples from master, But we --- need to delete them from both master and segments - -- We used to have a bug that would allow the above to succeed, posing -- hazards for later execution of the view. Check that the internal -- defenses for those hazards haven't bit-rotted, in case some other -- bug with similar symptoms emerges. --- begin; --- --- -- destroy the dependency entry that prevents the DROP: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 3 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t drop column f3; --- --- -- column f3 is still in the view, sort of ... --- select pg_get_viewdef('tt14v', true); --- -- ... and you can even EXPLAIN it ... --- explain (verbose, costs off) select * from tt14v; --- -- but it will fail at execution --- select f1, f4 from tt14v; --- select * from tt14v; --- --- rollback; +-- Cloudberry: In a distributed environment, DELETE FROM pg_depend only affects +-- the coordinator. We use a helper function with EXECUTE ON ALL SEGMENTS plus +-- allow_segment_DML to also delete the dependency on segments, so that the +-- subsequent ALTER TABLE can succeed on all nodes. +set allow_system_table_mods = on; +set allow_segment_DML = on; +create function delete_dep_on_segs(p_objid oid, p_refobjsubid int4) +returns setof int as $$ + delete from pg_depend where objid = p_objid and refobjsubid = p_refobjsubid returning 1; +$$ language sql modifies sql data execute on all segments + set allow_system_table_mods = on + set allow_segment_DML = on; +begin; +-- destroy the dependency entry that prevents the DROP: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 3 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f3 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 3); + delete_dep_on_segs +-------------------- + 1 + 1 + 1 +(3 rows) + +-- this will now succeed: +alter table tt14t drop column f3; +-- column f3 is still in the view, sort of ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +------------------------------- + SELECT f1, + + "?dropped?column?" AS f3,+ + f4 + + FROM tt14f() t(f1, f4); +(1 row) + +-- ... and you can even EXPLAIN it ... +explain (verbose, costs off) select * from tt14v; + QUERY PLAN +---------------------------------------- + Function Scan on testviewschm2.tt14f t + Output: t.f1, t.f3, t.f4 + Function Call: tt14f() + Optimizer: Postgres query optimizer +(4 rows) + +-- but it will fail at execution +select f1, f4 from tt14v; + f1 | f4 +-----+---- + foo | 42 +(1 row) + +select * from tt14v; +ERROR: attribute 3 of type record has been dropped +rollback; -- likewise, altering a referenced column's type is prohibited ... alter table tt14t alter column f4 type integer using f4::integer; -- fail ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view tt14v depends on column "f4" -- ... but some bug might let it happen, so check defenses --- begin; --- --- -- destroy the dependency entry that prevents the ALTER: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 4 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t alter column f4 type integer using f4::integer; --- --- -- f4 is still in the view ... --- select pg_get_viewdef('tt14v', true); --- -- but will fail at execution --- select f1, f3 from tt14v; --- select * from tt14v; --- --- rollback; +begin; +-- destroy the dependency entry that prevents the ALTER: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 4 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f4 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 4); + delete_dep_on_segs +-------------------- + 1 + 1 + 1 +(3 rows) + +-- this will now succeed: +alter table tt14t alter column f4 type integer using f4::integer; +-- f4 is still in the view ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +-------------------------------- + SELECT f1, + + f3, + + f4 + + FROM tt14f() t(f1, f3, f4); +(1 row) + +-- but will fail at execution +select f1, f3 from tt14v; + f1 | f3 +-----+----- + foo | baz +(1 row) + +select * from tt14v; +ERROR: attribute 4 of type record has wrong type +DETAIL: Table has type integer, but query expects text. +rollback; +reset allow_system_table_mods; +reset allow_segment_DML; +drop function delete_dep_on_segs(oid, int4); drop view tt14v; create view tt14v as select t.f1, t.f4 from tt14f() t; select pg_get_viewdef('tt14v', true); diff --git a/contrib/pax_storage/src/test/regress/expected/create_view_optimizer.out b/contrib/pax_storage/src/test/regress/expected/create_view_optimizer.out index fa00bf24030..cf3f7e1c474 100755 --- a/contrib/pax_storage/src/test/regress/expected/create_view_optimizer.out +++ b/contrib/pax_storage/src/test/regress/expected/create_view_optimizer.out @@ -1718,62 +1718,137 @@ alter table tt14t drop column f3; -- fail, view has explicit reference to f3 ERROR: cannot drop column f3 of table tt14t because other objects depend on it DETAIL: view tt14v depends on column f3 of table tt14t HINT: Use DROP ... CASCADE to drop the dependent objects too. --- MERGE16_FIXME: delete command can only delete tuples from master, But we --- need to delete them from both master and segments - -- We used to have a bug that would allow the above to succeed, posing -- hazards for later execution of the view. Check that the internal -- defenses for those hazards haven't bit-rotted, in case some other -- bug with similar symptoms emerges. --- begin; --- --- -- destroy the dependency entry that prevents the DROP: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 3 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t drop column f3; --- --- -- column f3 is still in the view, sort of ... --- select pg_get_viewdef('tt14v', true); --- -- ... and you can even EXPLAIN it ... --- explain (verbose, costs off) select * from tt14v; --- -- but it will fail at execution --- select f1, f4 from tt14v; --- select * from tt14v; --- --- rollback; +-- Cloudberry: In a distributed environment, DELETE FROM pg_depend only affects +-- the coordinator. We use a helper function with EXECUTE ON ALL SEGMENTS plus +-- allow_segment_DML to also delete the dependency on segments, so that the +-- subsequent ALTER TABLE can succeed on all nodes. +set allow_system_table_mods = on; +set allow_segment_DML = on; +create function delete_dep_on_segs(p_objid oid, p_refobjsubid int4) +returns setof int as $$ + delete from pg_depend where objid = p_objid and refobjsubid = p_refobjsubid returning 1; +$$ language sql modifies sql data execute on all segments + set allow_system_table_mods = on + set allow_segment_DML = on; +begin; +-- destroy the dependency entry that prevents the DROP: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 3 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f3 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 3); + delete_dep_on_segs +-------------------- + 1 + 1 + 1 +(3 rows) + +-- this will now succeed: +alter table tt14t drop column f3; +-- column f3 is still in the view, sort of ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +------------------------------- + SELECT f1, + + "?dropped?column?" AS f3,+ + f4 + + FROM tt14f() t(f1, f4); +(1 row) + +-- ... and you can even EXPLAIN it ... +explain (verbose, costs off) select * from tt14v; + QUERY PLAN +---------------------------------------- + Function Scan on testviewschm2.tt14f t + Output: t.f1, t.f3, t.f4 + Function Call: tt14f() + Optimizer: Postgres query optimizer +(4 rows) + +-- but it will fail at execution +select f1, f4 from tt14v; + f1 | f4 +-----+---- + foo | 42 +(1 row) + +select * from tt14v; +ERROR: attribute 3 of type record has been dropped +rollback; -- likewise, altering a referenced column's type is prohibited ... alter table tt14t alter column f4 type integer using f4::integer; -- fail ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view tt14v depends on column "f4" -- ... but some bug might let it happen, so check defenses --- begin; --- --- -- destroy the dependency entry that prevents the ALTER: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 4 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t alter column f4 type integer using f4::integer; --- --- -- f4 is still in the view ... --- select pg_get_viewdef('tt14v', true); --- -- but will fail at execution --- select f1, f3 from tt14v; --- select * from tt14v; --- --- rollback; +begin; +-- destroy the dependency entry that prevents the ALTER: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 4 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f4 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 4); + delete_dep_on_segs +-------------------- + 1 + 1 + 1 +(3 rows) + +-- this will now succeed: +alter table tt14t alter column f4 type integer using f4::integer; +-- f4 is still in the view ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +-------------------------------- + SELECT f1, + + f3, + + f4 + + FROM tt14f() t(f1, f3, f4); +(1 row) + +-- but will fail at execution +select f1, f3 from tt14v; + f1 | f3 +-----+----- + foo | baz +(1 row) + +select * from tt14v; +ERROR: attribute 4 of type record has wrong type +DETAIL: Table has type integer, but query expects text. +rollback; +reset allow_system_table_mods; +reset allow_segment_DML; +drop function delete_dep_on_segs(oid, int4); drop view tt14v; create view tt14v as select t.f1, t.f4 from tt14f() t; select pg_get_viewdef('tt14v', true); diff --git a/contrib/pax_storage/src/test/regress/sql/create_view.sql b/contrib/pax_storage/src/test/regress/sql/create_view.sql index 9569e3a181d..ae78b9fc69c 100644 --- a/contrib/pax_storage/src/test/regress/sql/create_view.sql +++ b/contrib/pax_storage/src/test/regress/sql/create_view.sql @@ -600,62 +600,89 @@ select * from tt14v; alter table tt14t drop column f3; -- fail, view has explicit reference to f3 --- MERGE16_FIXME: delete command can only delete tuples from master, But we --- need to delete them from both master and segments - -- We used to have a bug that would allow the above to succeed, posing -- hazards for later execution of the view. Check that the internal -- defenses for those hazards haven't bit-rotted, in case some other -- bug with similar symptoms emerges. --- begin; --- --- -- destroy the dependency entry that prevents the DROP: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 3 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t drop column f3; --- --- -- column f3 is still in the view, sort of ... --- select pg_get_viewdef('tt14v', true); --- -- ... and you can even EXPLAIN it ... --- explain (verbose, costs off) select * from tt14v; --- -- but it will fail at execution --- select f1, f4 from tt14v; --- select * from tt14v; --- --- rollback; + +-- Cloudberry: In a distributed environment, DELETE FROM pg_depend only affects +-- the coordinator. We use a helper function with EXECUTE ON ALL SEGMENTS plus +-- allow_segment_DML to also delete the dependency on segments, so that the +-- subsequent ALTER TABLE can succeed on all nodes. +set allow_system_table_mods = on; +set allow_segment_DML = on; +create function delete_dep_on_segs(p_objid oid, p_refobjsubid int4) +returns setof int as $$ + delete from pg_depend where objid = p_objid and refobjsubid = p_refobjsubid returning 1; +$$ language sql modifies sql data execute on all segments + set allow_system_table_mods = on + set allow_segment_DML = on; + +begin; + +-- destroy the dependency entry that prevents the DROP: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 3 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 3); + +-- this will now succeed: +alter table tt14t drop column f3; + +-- column f3 is still in the view, sort of ... +select pg_get_viewdef('tt14v', true); +-- ... and you can even EXPLAIN it ... +explain (verbose, costs off) select * from tt14v; +-- but it will fail at execution +select f1, f4 from tt14v; +select * from tt14v; + +rollback; -- likewise, altering a referenced column's type is prohibited ... alter table tt14t alter column f4 type integer using f4::integer; -- fail -- ... but some bug might let it happen, so check defenses --- begin; --- --- -- destroy the dependency entry that prevents the ALTER: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 4 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t alter column f4 type integer using f4::integer; --- --- -- f4 is still in the view ... --- select pg_get_viewdef('tt14v', true); --- -- but will fail at execution --- select f1, f3 from tt14v; --- select * from tt14v; --- --- rollback; +begin; + +-- destroy the dependency entry that prevents the ALTER: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 4 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 4); + +-- this will now succeed: +alter table tt14t alter column f4 type integer using f4::integer; + +-- f4 is still in the view ... +select pg_get_viewdef('tt14v', true); +-- but will fail at execution +select f1, f3 from tt14v; +select * from tt14v; + +rollback; + +reset allow_system_table_mods; +reset allow_segment_DML; +drop function delete_dep_on_segs(oid, int4); drop view tt14v; diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index d9f97043b64..9fea9056fae 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -1654,62 +1654,137 @@ alter table tt14t drop column f3; -- fail, view has explicit reference to f3 ERROR: cannot drop column f3 of table tt14t because other objects depend on it DETAIL: view tt14v depends on column f3 of table tt14t HINT: Use DROP ... CASCADE to drop the dependent objects too. --- MERGE16_FIXME: delete command can only delete tuples from master, But we --- need to delete them from both master and segments - -- We used to have a bug that would allow the above to succeed, posing -- hazards for later execution of the view. Check that the internal -- defenses for those hazards haven't bit-rotted, in case some other -- bug with similar symptoms emerges. --- begin; --- --- -- destroy the dependency entry that prevents the DROP: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 3 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t drop column f3; --- --- -- column f3 is still in the view, sort of ... --- select pg_get_viewdef('tt14v', true); --- -- ... and you can even EXPLAIN it ... --- explain (verbose, costs off) select * from tt14v; --- -- but it will fail at execution --- select f1, f4 from tt14v; --- select * from tt14v; --- --- rollback; +-- Cloudberry: In a distributed environment, DELETE FROM pg_depend only affects +-- the coordinator. We use a helper function with EXECUTE ON ALL SEGMENTS plus +-- allow_segment_DML to also delete the dependency on segments, so that the +-- subsequent ALTER TABLE can succeed on all nodes. +set allow_system_table_mods = on; +set allow_segment_DML = on; +create function delete_dep_on_segs(p_objid oid, p_refobjsubid int4) +returns setof int as $$ + delete from pg_depend where objid = p_objid and refobjsubid = p_refobjsubid returning 1; +$$ language sql modifies sql data execute on all segments + set allow_system_table_mods = on + set allow_segment_DML = on; +begin; +-- destroy the dependency entry that prevents the DROP: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 3 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f3 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 3); + delete_dep_on_segs +-------------------- + 1 + 1 + 1 +(3 rows) + +-- this will now succeed: +alter table tt14t drop column f3; +-- column f3 is still in the view, sort of ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +------------------------------- + SELECT f1, + + "?dropped?column?" AS f3,+ + f4 + + FROM tt14f() t(f1, f4); +(1 row) + +-- ... and you can even EXPLAIN it ... +explain (verbose, costs off) select * from tt14v; + QUERY PLAN +---------------------------------------- + Function Scan on testviewschm2.tt14f t + Output: t.f1, t.f3, t.f4 + Function Call: tt14f() + Optimizer: Postgres query optimizer +(4 rows) + +-- but it will fail at execution +select f1, f4 from tt14v; + f1 | f4 +-----+---- + foo | 42 +(1 row) + +select * from tt14v; +ERROR: attribute 3 of type record has been dropped +rollback; -- likewise, altering a referenced column's type is prohibited ... alter table tt14t alter column f4 type integer using f4::integer; -- fail ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view tt14v depends on column "f4" -- ... but some bug might let it happen, so check defenses --- begin; --- --- -- destroy the dependency entry that prevents the ALTER: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 4 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t alter column f4 type integer using f4::integer; --- --- -- f4 is still in the view ... --- select pg_get_viewdef('tt14v', true); --- -- but will fail at execution --- select f1, f3 from tt14v; --- select * from tt14v; --- --- rollback; +begin; +-- destroy the dependency entry that prevents the ALTER: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 4 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f4 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 4); + delete_dep_on_segs +-------------------- + 1 + 1 + 1 +(3 rows) + +-- this will now succeed: +alter table tt14t alter column f4 type integer using f4::integer; +-- f4 is still in the view ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +-------------------------------- + SELECT f1, + + f3, + + f4 + + FROM tt14f() t(f1, f3, f4); +(1 row) + +-- but will fail at execution +select f1, f3 from tt14v; + f1 | f3 +-----+----- + foo | baz +(1 row) + +select * from tt14v; +ERROR: attribute 4 of type record has wrong type +DETAIL: Table has type integer, but query expects text. +rollback; +reset allow_system_table_mods; +reset allow_segment_DML; +drop function delete_dep_on_segs(oid, int4); drop view tt14v; create view tt14v as select t.f1, t.f4 from tt14f() t; select pg_get_viewdef('tt14v', true); diff --git a/src/test/regress/expected/create_view_optimizer.out b/src/test/regress/expected/create_view_optimizer.out index 59986b9a6e8..a5a0ed6cb4d 100755 --- a/src/test/regress/expected/create_view_optimizer.out +++ b/src/test/regress/expected/create_view_optimizer.out @@ -1718,62 +1718,137 @@ alter table tt14t drop column f3; -- fail, view has explicit reference to f3 ERROR: cannot drop column f3 of table tt14t because other objects depend on it DETAIL: view tt14v depends on column f3 of table tt14t HINT: Use DROP ... CASCADE to drop the dependent objects too. --- MERGE16_FIXME: delete command can only delete tuples from master, But we --- need to delete them from both master and segments - -- We used to have a bug that would allow the above to succeed, posing -- hazards for later execution of the view. Check that the internal -- defenses for those hazards haven't bit-rotted, in case some other -- bug with similar symptoms emerges. --- begin; --- --- -- destroy the dependency entry that prevents the DROP: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 3 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t drop column f3; --- --- -- column f3 is still in the view, sort of ... --- select pg_get_viewdef('tt14v', true); --- -- ... and you can even EXPLAIN it ... --- explain (verbose, costs off) select * from tt14v; --- -- but it will fail at execution --- select f1, f4 from tt14v; --- select * from tt14v; --- --- rollback; +-- Cloudberry: In a distributed environment, DELETE FROM pg_depend only affects +-- the coordinator. We use a helper function with EXECUTE ON ALL SEGMENTS plus +-- allow_segment_DML to also delete the dependency on segments, so that the +-- subsequent ALTER TABLE can succeed on all nodes. +set allow_system_table_mods = on; +set allow_segment_DML = on; +create function delete_dep_on_segs(p_objid oid, p_refobjsubid int4) +returns setof int as $$ + delete from pg_depend where objid = p_objid and refobjsubid = p_refobjsubid returning 1; +$$ language sql modifies sql data execute on all segments + set allow_system_table_mods = on + set allow_segment_DML = on; +begin; +-- destroy the dependency entry that prevents the DROP: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 3 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f3 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 3); + delete_dep_on_segs +-------------------- + 1 + 1 + 1 +(3 rows) + +-- this will now succeed: +alter table tt14t drop column f3; +-- column f3 is still in the view, sort of ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +------------------------------- + SELECT f1, + + "?dropped?column?" AS f3,+ + f4 + + FROM tt14f() t(f1, f4); +(1 row) + +-- ... and you can even EXPLAIN it ... +explain (verbose, costs off) select * from tt14v; + QUERY PLAN +---------------------------------------- + Function Scan on testviewschm2.tt14f t + Output: t.f1, t.f3, t.f4 + Function Call: tt14f() + Optimizer: Postgres query optimizer +(4 rows) + +-- but it will fail at execution +select f1, f4 from tt14v; + f1 | f4 +-----+---- + foo | 42 +(1 row) + +select * from tt14v; +ERROR: attribute 3 of type record has been dropped +rollback; -- likewise, altering a referenced column's type is prohibited ... alter table tt14t alter column f4 type integer using f4::integer; -- fail ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view tt14v depends on column "f4" -- ... but some bug might let it happen, so check defenses --- begin; --- --- -- destroy the dependency entry that prevents the ALTER: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 4 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t alter column f4 type integer using f4::integer; --- --- -- f4 is still in the view ... --- select pg_get_viewdef('tt14v', true); --- -- but will fail at execution --- select f1, f3 from tt14v; --- select * from tt14v; --- --- rollback; +begin; +-- destroy the dependency entry that prevents the ALTER: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 4 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f4 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 4); + delete_dep_on_segs +-------------------- + 1 + 1 + 1 +(3 rows) + +-- this will now succeed: +alter table tt14t alter column f4 type integer using f4::integer; +-- f4 is still in the view ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +-------------------------------- + SELECT f1, + + f3, + + f4 + + FROM tt14f() t(f1, f3, f4); +(1 row) + +-- but will fail at execution +select f1, f3 from tt14v; + f1 | f3 +-----+----- + foo | baz +(1 row) + +select * from tt14v; +ERROR: attribute 4 of type record has wrong type +DETAIL: Table has type integer, but query expects text. +rollback; +reset allow_system_table_mods; +reset allow_segment_DML; +drop function delete_dep_on_segs(oid, int4); drop view tt14v; create view tt14v as select t.f1, t.f4 from tt14f() t; select pg_get_viewdef('tt14v', true); diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql index 9569e3a181d..ae78b9fc69c 100644 --- a/src/test/regress/sql/create_view.sql +++ b/src/test/regress/sql/create_view.sql @@ -600,62 +600,89 @@ select * from tt14v; alter table tt14t drop column f3; -- fail, view has explicit reference to f3 --- MERGE16_FIXME: delete command can only delete tuples from master, But we --- need to delete them from both master and segments - -- We used to have a bug that would allow the above to succeed, posing -- hazards for later execution of the view. Check that the internal -- defenses for those hazards haven't bit-rotted, in case some other -- bug with similar symptoms emerges. --- begin; --- --- -- destroy the dependency entry that prevents the DROP: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 3 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t drop column f3; --- --- -- column f3 is still in the view, sort of ... --- select pg_get_viewdef('tt14v', true); --- -- ... and you can even EXPLAIN it ... --- explain (verbose, costs off) select * from tt14v; --- -- but it will fail at execution --- select f1, f4 from tt14v; --- select * from tt14v; --- --- rollback; + +-- Cloudberry: In a distributed environment, DELETE FROM pg_depend only affects +-- the coordinator. We use a helper function with EXECUTE ON ALL SEGMENTS plus +-- allow_segment_DML to also delete the dependency on segments, so that the +-- subsequent ALTER TABLE can succeed on all nodes. +set allow_system_table_mods = on; +set allow_segment_DML = on; +create function delete_dep_on_segs(p_objid oid, p_refobjsubid int4) +returns setof int as $$ + delete from pg_depend where objid = p_objid and refobjsubid = p_refobjsubid returning 1; +$$ language sql modifies sql data execute on all segments + set allow_system_table_mods = on + set allow_segment_DML = on; + +begin; + +-- destroy the dependency entry that prevents the DROP: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 3 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 3); + +-- this will now succeed: +alter table tt14t drop column f3; + +-- column f3 is still in the view, sort of ... +select pg_get_viewdef('tt14v', true); +-- ... and you can even EXPLAIN it ... +explain (verbose, costs off) select * from tt14v; +-- but it will fail at execution +select f1, f4 from tt14v; +select * from tt14v; + +rollback; -- likewise, altering a referenced column's type is prohibited ... alter table tt14t alter column f4 type integer using f4::integer; -- fail -- ... but some bug might let it happen, so check defenses --- begin; --- --- -- destroy the dependency entry that prevents the ALTER: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 4 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t alter column f4 type integer using f4::integer; --- --- -- f4 is still in the view ... --- select pg_get_viewdef('tt14v', true); --- -- but will fail at execution --- select f1, f3 from tt14v; --- select * from tt14v; --- --- rollback; +begin; + +-- destroy the dependency entry that prevents the ALTER: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 4 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 4); + +-- this will now succeed: +alter table tt14t alter column f4 type integer using f4::integer; + +-- f4 is still in the view ... +select pg_get_viewdef('tt14v', true); +-- but will fail at execution +select f1, f3 from tt14v; +select * from tt14v; + +rollback; + +reset allow_system_table_mods; +reset allow_segment_DML; +drop function delete_dep_on_segs(oid, int4); drop view tt14v; diff --git a/src/test/singlenode_regress/expected/create_view.out b/src/test/singlenode_regress/expected/create_view.out index 84533660331..fe0302fe9d3 100644 --- a/src/test/singlenode_regress/expected/create_view.out +++ b/src/test/singlenode_regress/expected/create_view.out @@ -1633,62 +1633,131 @@ alter table tt14t drop column f3; -- fail, view has explicit reference to f3 ERROR: cannot drop column f3 of table tt14t because other objects depend on it DETAIL: view tt14v depends on column f3 of table tt14t HINT: Use DROP ... CASCADE to drop the dependent objects too. --- MERGE16_FIXME: delete command can only delete tuples from master, But we --- need to delete them from both master and segments - -- We used to have a bug that would allow the above to succeed, posing -- hazards for later execution of the view. Check that the internal -- defenses for those hazards haven't bit-rotted, in case some other -- bug with similar symptoms emerges. --- begin; --- --- -- destroy the dependency entry that prevents the DROP: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 3 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t drop column f3; --- --- -- column f3 is still in the view, sort of ... --- select pg_get_viewdef('tt14v', true); --- -- ... and you can even EXPLAIN it ... --- explain (verbose, costs off) select * from tt14v; --- -- but it will fail at execution --- select f1, f4 from tt14v; --- select * from tt14v; --- --- rollback; +-- Cloudberry: In a distributed environment, DELETE FROM pg_depend only affects +-- the coordinator. We use a helper function with EXECUTE ON ALL SEGMENTS plus +-- allow_segment_DML to also delete the dependency on segments, so that the +-- subsequent ALTER TABLE can succeed on all nodes. +set allow_system_table_mods = on; +set allow_segment_DML = on; +create function delete_dep_on_segs(p_objid oid, p_refobjsubid int4) +returns setof int as $$ + delete from pg_depend where objid = p_objid and refobjsubid = p_refobjsubid returning 1; +$$ language sql modifies sql data execute on all segments + set allow_system_table_mods = on + set allow_segment_DML = on; +begin; +-- destroy the dependency entry that prevents the DROP: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 3 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f3 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 3); + delete_dep_on_segs +-------------------- +(0 rows) + +-- this will now succeed: +alter table tt14t drop column f3; +-- column f3 is still in the view, sort of ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +------------------------------- + SELECT f1, + + "?dropped?column?" AS f3,+ + f4 + + FROM tt14f() t(f1, f4); +(1 row) + +-- ... and you can even EXPLAIN it ... +explain (verbose, costs off) select * from tt14v; + QUERY PLAN +---------------------------------------- + Function Scan on testviewschm2.tt14f t + Output: t.f1, t.f3, t.f4 + Function Call: tt14f() + Optimizer: Postgres query optimizer +(4 rows) + +-- but it will fail at execution +select f1, f4 from tt14v; + f1 | f4 +-----+---- + foo | 42 +(1 row) + +select * from tt14v; +ERROR: attribute 3 of type record has been dropped +rollback; -- likewise, altering a referenced column's type is prohibited ... alter table tt14t alter column f4 type integer using f4::integer; -- fail ERROR: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view tt14v depends on column "f4" -- ... but some bug might let it happen, so check defenses --- begin; --- --- -- destroy the dependency entry that prevents the ALTER: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 4 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t alter column f4 type integer using f4::integer; --- --- -- f4 is still in the view ... --- select pg_get_viewdef('tt14v', true); --- -- but will fail at execution --- select f1, f3 from tt14v; --- select * from tt14v; --- --- rollback; +begin; +-- destroy the dependency entry that prevents the ALTER: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 4 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + obj | ref | deptype +----------------------------+--------------------------+--------- + rule _RETURN on view tt14v | column f4 of table tt14t | n +(1 row) + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 4); + delete_dep_on_segs +-------------------- +(0 rows) + +-- this will now succeed: +alter table tt14t alter column f4 type integer using f4::integer; +-- f4 is still in the view ... +select pg_get_viewdef('tt14v', true); + pg_get_viewdef +-------------------------------- + SELECT f1, + + f3, + + f4 + + FROM tt14f() t(f1, f3, f4); +(1 row) + +-- but will fail at execution +select f1, f3 from tt14v; + f1 | f3 +-----+----- + foo | baz +(1 row) + +select * from tt14v; +ERROR: attribute 4 of type record has wrong type +DETAIL: Table has type integer, but query expects text. +rollback; +reset allow_system_table_mods; +reset allow_segment_DML; +drop function delete_dep_on_segs(oid, int4); drop view tt14v; create view tt14v as select t.f1, t.f4 from tt14f() t; select pg_get_viewdef('tt14v', true); diff --git a/src/test/singlenode_regress/sql/create_view.sql b/src/test/singlenode_regress/sql/create_view.sql index 674f361e2c8..bceca8b5ca1 100644 --- a/src/test/singlenode_regress/sql/create_view.sql +++ b/src/test/singlenode_regress/sql/create_view.sql @@ -573,62 +573,89 @@ select * from tt14v; alter table tt14t drop column f3; -- fail, view has explicit reference to f3 --- MERGE16_FIXME: delete command can only delete tuples from master, But we --- need to delete them from both master and segments - -- We used to have a bug that would allow the above to succeed, posing -- hazards for later execution of the view. Check that the internal -- defenses for those hazards haven't bit-rotted, in case some other -- bug with similar symptoms emerges. --- begin; --- --- -- destroy the dependency entry that prevents the DROP: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 3 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t drop column f3; --- --- -- column f3 is still in the view, sort of ... --- select pg_get_viewdef('tt14v', true); --- -- ... and you can even EXPLAIN it ... --- explain (verbose, costs off) select * from tt14v; --- -- but it will fail at execution --- select f1, f4 from tt14v; --- select * from tt14v; --- --- rollback; + +-- Cloudberry: In a distributed environment, DELETE FROM pg_depend only affects +-- the coordinator. We use a helper function with EXECUTE ON ALL SEGMENTS plus +-- allow_segment_DML to also delete the dependency on segments, so that the +-- subsequent ALTER TABLE can succeed on all nodes. +set allow_system_table_mods = on; +set allow_segment_DML = on; +create function delete_dep_on_segs(p_objid oid, p_refobjsubid int4) +returns setof int as $$ + delete from pg_depend where objid = p_objid and refobjsubid = p_refobjsubid returning 1; +$$ language sql modifies sql data execute on all segments + set allow_system_table_mods = on + set allow_segment_DML = on; + +begin; + +-- destroy the dependency entry that prevents the DROP: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 3 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 3); + +-- this will now succeed: +alter table tt14t drop column f3; + +-- column f3 is still in the view, sort of ... +select pg_get_viewdef('tt14v', true); +-- ... and you can even EXPLAIN it ... +explain (verbose, costs off) select * from tt14v; +-- but it will fail at execution +select f1, f4 from tt14v; +select * from tt14v; + +rollback; -- likewise, altering a referenced column's type is prohibited ... alter table tt14t alter column f4 type integer using f4::integer; -- fail -- ... but some bug might let it happen, so check defenses --- begin; --- --- -- destroy the dependency entry that prevents the ALTER: --- delete from pg_depend where --- objid = (select oid from pg_rewrite --- where ev_class = 'tt14v'::regclass and rulename = '_RETURN') --- and refobjsubid = 4 --- returning pg_describe_object(classid, objid, objsubid) as obj, --- pg_describe_object(refclassid, refobjid, refobjsubid) as ref, --- deptype; --- --- -- this will now succeed: --- alter table tt14t alter column f4 type integer using f4::integer; --- --- -- f4 is still in the view ... --- select pg_get_viewdef('tt14v', true); --- -- but will fail at execution --- select f1, f3 from tt14v; --- select * from tt14v; --- --- rollback; +begin; + +-- destroy the dependency entry that prevents the ALTER: +delete from pg_depend where + objid = (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN') + and refobjsubid = 4 +returning pg_describe_object(classid, objid, objsubid) as obj, + pg_describe_object(refclassid, refobjid, refobjsubid) as ref, + deptype; + +-- Cloudberry: also delete from segments +select delete_dep_on_segs( + (select oid from pg_rewrite + where ev_class = 'tt14v'::regclass and rulename = '_RETURN'), + 4); + +-- this will now succeed: +alter table tt14t alter column f4 type integer using f4::integer; + +-- f4 is still in the view ... +select pg_get_viewdef('tt14v', true); +-- but will fail at execution +select f1, f3 from tt14v; +select * from tt14v; + +rollback; + +reset allow_system_table_mods; +reset allow_segment_DML; +drop function delete_dep_on_segs(oid, int4); drop view tt14v; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
