Hi all, While removing some dust from my stack of patches, I have bumped into a weird interaction with CREATE OR REPLACE VIEW due to AT_AddColumnToView: CREATE VIEW one_view AS SELECT * FROM one; -- Adds some columns. CREATE OR REPLACE VIEW one_view AS SELECT * FROM one, two;
The funny behavior is with the second VIEW query, that has the idea to stack twice a ViewStmt in event triggers: once in a EventTriggerCollectSimpleCommand() and a second time in EventTriggerAlterTableEnd(). Now, why would the second one be stacked? Well, EventTriggerAlterTableEnd() would add it because it has some AT_AddColumnToView subcommands. First, I was wondering if we should tweak EventTriggerAlterTableEnd() so as subcommands that add columns to a view should not be duplicated, applying some filtering. However, at the end, I have come to accept the fact that event triggers are a representation at SQL level of the internal structures of these commands. Hence, the current code is fine, still we had better have some tests to document that. And we don't do that now. This issue has come up while looking at a portion of my sequence AM patch, where I was not sure what the right representation should be in event triggers. I just noticed that column additions for views do already the same thing. So, please find attached a small-ish patch to close the gap with a few tests: - One thing for event_trigger.sql, with column additions for view, to check the collection. - One for test_ddl_deparse, that shows the stack of both a "simple" and an "alter table". Thoughts or comments? -- Michael
From 00ef70b05f2d230d522341f761ddebb7e7e11a5b Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Fri, 27 Feb 2026 16:24:18 +0900 Subject: [PATCH] Add tests for CREATE OR REPLACE VIEW with column additions --- .../modules/test_ddl_deparse/expected/create_view.out | 6 ++++++ src/test/modules/test_ddl_deparse/sql/create_view.sql | 3 +++ src/test/regress/expected/event_trigger.out | 10 +++++++++- src/test/regress/sql/event_trigger.sql | 7 ++++++- 4 files changed, 24 insertions(+), 2 deletions(-) diff --git a/src/test/modules/test_ddl_deparse/expected/create_view.out b/src/test/modules/test_ddl_deparse/expected/create_view.out index 4ae0f4978ec1..0a8cc4627a8b 100644 --- a/src/test/modules/test_ddl_deparse/expected/create_view.out +++ b/src/test/modules/test_ddl_deparse/expected/create_view.out @@ -12,6 +12,12 @@ NOTICE: subcommand: type REPLACE RELOPTIONS desc <NULL> CREATE VIEW datatype_view AS SELECT * FROM datatype_table; NOTICE: DDL test: type simple, tag CREATE VIEW +CREATE OR REPLACE VIEW datatype_view AS + SELECT * FROM datatype_table, static_view; +NOTICE: DDL test: type simple, tag CREATE VIEW +NOTICE: DDL test: type alter table, tag CREATE VIEW +NOTICE: subcommand: type ADD COLUMN TO VIEW desc column col of view datatype_view +NOTICE: subcommand: type REPLACE RELOPTIONS desc <NULL> CREATE RECURSIVE VIEW nums_1_100 (n) AS VALUES (1) UNION ALL diff --git a/src/test/modules/test_ddl_deparse/sql/create_view.sql b/src/test/modules/test_ddl_deparse/sql/create_view.sql index 030b76f86fa6..f473dd74171d 100644 --- a/src/test/modules/test_ddl_deparse/sql/create_view.sql +++ b/src/test/modules/test_ddl_deparse/sql/create_view.sql @@ -11,6 +11,9 @@ CREATE OR REPLACE VIEW static_view AS CREATE VIEW datatype_view AS SELECT * FROM datatype_table; +CREATE OR REPLACE VIEW datatype_view AS + SELECT * FROM datatype_table, static_view; + CREATE RECURSIVE VIEW nums_1_100 (n) AS VALUES (1) UNION ALL diff --git a/src/test/regress/expected/event_trigger.out b/src/test/regress/expected/event_trigger.out index 16e4530708cc..f897b079e67e 100644 --- a/src/test/regress/expected/event_trigger.out +++ b/src/test/regress/expected/event_trigger.out @@ -416,7 +416,8 @@ CREATE SCHEMA evttrig CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two', col_c SERIAL) CREATE INDEX one_idx ON one (col_b) CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42) - CREATE TABLE id (col_d int NOT NULL GENERATED ALWAYS AS IDENTITY); + CREATE TABLE id (col_d int NOT NULL GENERATED ALWAYS AS IDENTITY) + CREATE VIEW one_view AS SELECT * FROM two; NOTICE: END: command_tag=CREATE SCHEMA type=schema identity=evttrig NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_a_seq NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.one_col_c_seq @@ -429,7 +430,14 @@ NOTICE: END: command_tag=ALTER TABLE type=table identity=evttrig.two NOTICE: END: command_tag=CREATE SEQUENCE type=sequence identity=evttrig.id_col_d_seq NOTICE: END: command_tag=CREATE TABLE type=table identity=evttrig.id NOTICE: END: command_tag=ALTER SEQUENCE type=sequence identity=evttrig.id_col_d_seq +NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view NOTICE: END: command_tag=CREATE INDEX type=index identity=evttrig.one_idx +-- View with column additions +CREATE OR REPLACE VIEW evttrig.one_view AS SELECT * FROM evttrig.two, evttrig.id; +NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view +NOTICE: END: command_tag=CREATE VIEW type=view identity=evttrig.one_view +DROP VIEW evttrig.one_view; +NOTICE: NORMAL: orig=t normal=f istemp=f type=view identity=evttrig.one_view schema=evttrig name=one_view addr={evttrig,one_view} args={} -- Partitioned tables with a partitioned index CREATE TABLE evttrig.parted ( id int PRIMARY KEY) diff --git a/src/test/regress/sql/event_trigger.sql b/src/test/regress/sql/event_trigger.sql index c613c0cfd439..32e9bb58c5e9 100644 --- a/src/test/regress/sql/event_trigger.sql +++ b/src/test/regress/sql/event_trigger.sql @@ -324,7 +324,12 @@ CREATE SCHEMA evttrig CREATE TABLE one (col_a SERIAL PRIMARY KEY, col_b text DEFAULT 'forty two', col_c SERIAL) CREATE INDEX one_idx ON one (col_b) CREATE TABLE two (col_c INTEGER CHECK (col_c > 0) REFERENCES one DEFAULT 42) - CREATE TABLE id (col_d int NOT NULL GENERATED ALWAYS AS IDENTITY); + CREATE TABLE id (col_d int NOT NULL GENERATED ALWAYS AS IDENTITY) + CREATE VIEW one_view AS SELECT * FROM two; + +-- View with column additions +CREATE OR REPLACE VIEW evttrig.one_view AS SELECT * FROM evttrig.two, evttrig.id; +DROP VIEW evttrig.one_view; -- Partitioned tables with a partitioned index CREATE TABLE evttrig.parted ( -- 2.53.0
signature.asc
Description: PGP signature
