Feedback-ID: i0fe9450f:Fastmail
Date: Fri, 27 Feb 2026 16:25:41 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Postgres hackers <pgsql-hackers@lists.postgresql.org>
Subject: Funny behavior in event trigger code with CREATE OR REPLACE VIEW and
 column additions
Message-ID: <aaFG9bqkEn0RhLJG@paquier.xyz>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="t78TGZSYbWr5GCB3"
Content-Disposition: inline
Archived-At: <https://www.postgresql.org/message-id/aaFG9bqkEn0RhLJG%40paquier.xyz>
Precedence: bulk


--t78TGZSYbWr5GCB3
Content-Type: multipart/mixed; boundary="klXRyCX3v+PtqdTX"
Content-Disposition: inline


--klXRyCX3v+PtqdTX
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--klXRyCX3v+PtqdTX
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
	filename=0001-Add-tests-for-CREATE-OR-REPLACE-VIEW-with-column-add.patch
Content-Transfer-Encoding: quoted-printable

=46rom 00ef70b05f2d230d522341f761ddebb7e7e11a5b Mon Sep 17 00:00:00 2001
=46rom: Michael Paquier <michael@paquier.xyz>
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/s=
rc/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 <NUL=
L>
 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 dat=
atype_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/te=
st/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;
=20
+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=3DCREATE SCHEMA type=3Dschema identity=3Devttrig
 NOTICE:  END: command_tag=3DCREATE SEQUENCE type=3Dsequence identity=3Devt=
trig.one_col_a_seq
 NOTICE:  END: command_tag=3DCREATE SEQUENCE type=3Dsequence identity=3Devt=
trig.one_col_c_seq
@@ -429,7 +430,14 @@ NOTICE:  END: command_tag=3DALTER TABLE type=3Dtable i=
dentity=3Devttrig.two
 NOTICE:  END: command_tag=3DCREATE SEQUENCE type=3Dsequence identity=3Devt=
trig.id_col_d_seq
 NOTICE:  END: command_tag=3DCREATE TABLE type=3Dtable identity=3Devttrig.id
 NOTICE:  END: command_tag=3DALTER SEQUENCE type=3Dsequence identity=3Devtt=
rig.id_col_d_seq
+NOTICE:  END: command_tag=3DCREATE VIEW type=3Dview identity=3Devttrig.one=
_view
 NOTICE:  END: command_tag=3DCREATE INDEX type=3Dindex identity=3Devttrig.o=
ne_idx
+-- View with column additions
+CREATE OR REPLACE VIEW evttrig.one_view AS SELECT * FROM evttrig.two, evtt=
rig.id;
+NOTICE:  END: command_tag=3DCREATE VIEW type=3Dview identity=3Devttrig.one=
_view
+NOTICE:  END: command_tag=3DCREATE VIEW type=3Dview identity=3Devttrig.one=
_view
+DROP VIEW evttrig.one_view;
+NOTICE:  NORMAL: orig=3Dt normal=3Df istemp=3Df type=3Dview identity=3Devt=
trig.one_view schema=3Devttrig name=3Done_view addr=3D{evttrig,one_view} ar=
gs=3D{}
 -- 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, evtt=
rig.id;
+DROP VIEW evttrig.one_view;
=20
 -- Partitioned tables with a partitioned index
 CREATE TABLE evttrig.parted (
--=20
2.53.0


--klXRyCX3v+PtqdTX--

--t78TGZSYbWr5GCB3
Content-Type: application/pgp-signature; name=signature.asc

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmmhRvUACgkQnvQgOdby
QH1oGQ/9H6RbsRFCtaCEc18cwmxZm6hnJHZxXafmOlZk17Xn3KxQCVopomh7z/Yp
FAs5sutSyyEWL1xxo5W5gOSS3ZqXIAQUFQPu486GrBrQVt6f9AECiMMoa7CcWIOD
nHxH7hWRipwyityOcCIu3TxTUJlQXVqnJfTrUgqhZkwrCZ2dx4y0/LJwNX060efK
u+Jg29C4MRofJ5wV/0EZW0TUPYhdtSTqQafnorJ/j0kB0kx907ia1mgS4XWsS5Nd
ZEQYT9mGuOxXqNP9m2Po1BepOIyBOF+Hs8ucmyr4GqIwoWLGcmSsiwSbM2wY61Tf
+e08NqFeJWmSASBWUAwWweot7BrjJ61JrHcFq131UEUrVMK+FPaQbtdWQUJ/oSeU
9rbCmqrNPOv2BFkGGl0tgKr6coBC8JHEQLJUm138HdGGZhB0vIo4Rr9GDjio3key
iVAP5Gp6nZSqBNK9LG82PLThMcF7NLwOAJwk1Um4mteHmEecjidbrVJEvSt07JXu
Srrfp7vPaIwhNlDRdzVWkitGNPYSfcVjy3vm2jA28AgkZbgTN5GJlX3gI7Nwlkwd
FPETPbCEJpvzCGY7NInujRxGmBfVMWGw6mCmRPl23iGAXdMDFSsOK9zSLvmNQOuz
AhrD6VQ12e1AFa48LoXJGDUiqgBmJJ9r4pgvcdWJQ3E6nTr93Ug=
=pvYM
-----END PGP SIGNATURE-----

--t78TGZSYbWr5GCB3--