public inbox for [email protected]
help / color / mirror / Atom feedFrom: Michael Paquier <[email protected]>
To: Postgres hackers <[email protected]>
Subject: Funny behavior in event trigger code with CREATE OR REPLACE VIEW and column additions
Date: Fri, 27 Feb 2026 16:25:41 +0900
Message-ID: <[email protected]> (raw)
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
Attachments:
[text/plain] 0001-Add-tests-for-CREATE-OR-REPLACE-VIEW-with-column-add.patch (4.7K, ../[email protected]/2-0001-Add-tests-for-CREATE-OR-REPLACE-VIEW-with-column-add.patch)
download | inline diff:
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
[application/pgp-signature] signature.asc (833B, ../[email protected]/3-signature.asc)
download
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Funny behavior in event trigger code with CREATE OR REPLACE VIEW and column additions
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox