EphemeralNamedRelation and materialized view

From 6c72c884954a4223fad192ff021803bf7835e7d7 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Fri, 15 Nov 2024 15:07:17 +0900
Subject: [PATCH] Prohibit materialized views to use ephemeral named relations

---
 src/backend/parser/analyze.c        | 11 ++++++--
 src/backend/parser/parse_relation.c | 43 ++++++++++++++++++++++++++++-
 src/include/parser/parse_relation.h |  1 +
 3 files changed, 51 insertions(+), 4 deletions(-)

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 506e063161..c96088c2cc 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -3125,15 +3125,20 @@ transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt)
 					 errmsg("materialized views must not use data-modifying statements in WITH")));
 
 		/*
-		 * Check whether any temporary database objects are used in the
-		 * creation query. It would be hard to refresh data or incrementally
-		 * maintain it if a source disappeared.
+		 * Check whether any temporary database objects or ephemeral named
+		 * relations are used in the creation query. It would be hard to refresh
+		 * data or incrementally maintain it if a source disappeared.
 		 */
 		if (isQueryUsingTempRelation(query))
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 					 errmsg("materialized views must not use temporary tables or views")));
 
+		if (isQueryUsingENR(query))
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("materialized views must not use ephemeral named relations")));
+
 		/*
 		 * A materialized view would either need to save parameters for use in
 		 * maintaining/loading the data or prohibit them entirely.  The latter
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index 8075b1b8a1..0e9cb7a1c6 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -102,6 +102,7 @@ static int	specialAttNum(const char *attname);
 static bool rte_visible_if_lateral(ParseState *pstate, RangeTblEntry *rte);
 static bool rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte);
 static bool isQueryUsingTempRelation_walker(Node *node, void *context);
+static bool isQueryUsingENR_walker(Node *node, void *context);
 
 
 /*
@@ -3893,7 +3894,7 @@ rte_visible_if_qualified(ParseState *pstate, RangeTblEntry *rte)
 
 /*
  * Examine a fully-parsed query, and return true iff any relation underlying
- * the query is a temporary relation (table, view, or materialized view).
+ * the query is an ephemeral named relation.
  */
 bool
 isQueryUsingTempRelation(Query *query)
@@ -3938,6 +3939,46 @@ isQueryUsingTempRelation_walker(Node *node, void *context)
 								  context);
 }
 
+/*
+ * Examine a fully-parsed query, and return true iff any relation underlying
+ * the query is an ephemeral named relation.
+ */
+bool
+isQueryUsingENR(Query *query)
+{
+	return isQueryUsingENR_walker((Node *) query, NULL);
+}
+
+static bool
+isQueryUsingENR_walker(Node *node, void *context)
+{
+	if (node == NULL)
+		return false;
+
+	if (IsA(node, Query))
+	{
+		Query	   *query = (Query *) node;
+		ListCell   *rtable;
+
+		foreach(rtable, query->rtable)
+		{
+			RangeTblEntry *rte = lfirst(rtable);
+
+			if (rte->rtekind == RTE_NAMEDTUPLESTORE)
+				return true;
+		}
+
+		return query_tree_walker(query,
+								 isQueryUsingENR_walker,
+								 context,
+								 QTW_IGNORE_JOINALIASES);
+	}
+
+	return expression_tree_walker(node,
+								  isQueryUsingENR_walker,
+								  context);
+}
+
 /*
  * addRTEPermissionInfo
  *		Creates RTEPermissionInfo for a given RTE and adds it into the
diff --git a/src/include/parser/parse_relation.h b/src/include/parser/parse_relation.h
index 91fd8e243b..2849da7fbd 100644
--- a/src/include/parser/parse_relation.h
+++ b/src/include/parser/parse_relation.h
@@ -127,5 +127,6 @@ extern const NameData *attnumAttName(Relation rd, int attid);
 extern Oid	attnumTypeId(Relation rd, int attid);
 extern Oid	attnumCollationId(Relation rd, int attid);
 extern bool isQueryUsingTempRelation(Query *query);
+extern bool isQueryUsingENR(Query *query);
 
 #endif							/* PARSE_RELATION_H */
-- 
2.34.1

From 34d09775e92b0ac39cd8656d69164b13c92f6fa2 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Mon, 2 Dec 2024 09:39:34 +0900
Subject: [PATCH v2] Prohibit materialized views to use ephemeral named
 relations

---
 src/backend/catalog/dependency.c       | 24 ++++++++++++++++++++++++
 src/test/regress/expected/triggers.out | 13 +++++++++++++
 src/test/regress/sql/triggers.sql      | 13 +++++++++++++
 3 files changed, 50 insertions(+)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 2afc550540..f323e8c91e 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -138,6 +138,7 @@ typedef struct
 /* for find_expr_references_walker */
 typedef struct
 {
+	const ObjectAddress *depender;	/* depender object */
 	ObjectAddresses *addrs;		/* addresses being accumulated */
 	List	   *rtables;		/* list of rangetables to resolve Vars */
 } find_expr_references_context;
@@ -1556,6 +1557,7 @@ recordDependencyOnExpr(const ObjectAddress *depender,
 {
 	find_expr_references_context context;
 
+	context.depender = depender;
 	context.addrs = new_object_addresses();
 
 	/* Set up interpretation for Vars at varlevelsup = 0 */
@@ -1602,6 +1604,7 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
 	find_expr_references_context context;
 	RangeTblEntry rte = {0};
 
+	context.depender = depender;
 	context.addrs = new_object_addresses();
 
 	/* We gin up a rather bogus rangetable list to handle Vars */
@@ -1739,6 +1742,17 @@ find_expr_references_walker(Node *node,
 			/* (done out of line, because it's a bit bulky) */
 			process_function_rte_ref(rte, var->varattno, context);
 		}
+		else if (rte->rtekind == RTE_NAMEDTUPLESTORE)
+		{
+			/*
+			 * Catalog objects cannot depend on a transtion table which has
+			 * no catalog representation.
+			 */
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("transition table cannot be used in %s",
+							getObjectTypeDescription(context->depender, true))));
+		}
 
 		/*
 		 * Vars referencing other RTE types require no additional work.  In
@@ -2191,6 +2205,16 @@ find_expr_references_walker(Node *node,
 					}
 					context->rtables = list_delete_first(context->rtables);
 					break;
+				case RTE_NAMEDTUPLESTORE:
+					/*
+					 * Catalog objects cannot depend on a transtion table
+					 * which has no catalog representation.
+					 */
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("transition table cannot be used in %s",
+									getObjectTypeDescription(context->depender, true))));
+					break;
 				default:
 					break;
 			}
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index a044d6afe2..e3522cf3bb 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -3728,3 +3728,16 @@ Inherits: parent
 
 drop table parent, child;
 drop function f();
+-- Verify prohibition of materialized views defined using tansition table
+create table my_table (a int);
+create function make_matview() returns trigger as
+$$ begin create materialized view mv as select * from new_table; return new; end $$
+language plpgsql;
+create trigger make_matview after insert on my_table
+referencing new table as new_table
+for each statement execute function make_matview();
+insert into my_table values (42);
+ERROR:  transition tables cannot be used in rule
+CONTEXT:  SQL statement "create materialized view mv as select * from new_table"
+PL/pgSQL function make_matview() line 1 at SQL statement
+drop table my_table;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 51610788b2..f83bd84e6c 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -2551,6 +2551,7 @@ merge into merge_target_table t
 using merge_source_table s
 on t.a = s.a
 when matched and s.a <= 2 then
+
 	update set b = t.b || ' updated by merge'
 when matched and s.a > 2 then
 	delete
@@ -2820,3 +2821,15 @@ alter trigger parenttrig on parent rename to anothertrig;
 
 drop table parent, child;
 drop function f();
+
+-- Verify prohibition of materialized views defined using tansition table
+
+create table my_table (a int);
+create function make_matview() returns trigger as
+$$ begin create materialized view mv as select * from new_table; return new; end $$
+language plpgsql;
+create trigger make_matview after insert on my_table
+referencing new table as new_table
+for each statement execute function make_matview();
+insert into my_table values (42);
+drop table my_table;
-- 
2.34.1

From a0014121e3ca72cb60a7fb627a19c71c85ca84ae Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon, 30 Dec 2024 15:59:32 -0500
Subject: [PATCH v3] Disallow NAMEDTUPLESTORE RTEs in stored views, rules, etc.

A named tuplestore is necessarily a transient object, so it makes
no sense to reference one in a persistent object such as a view.
We didn't previously prevent that, with the result that if you
tried you would get some weird failure about how the executor
couldn't find the tuplestore.

We can mechanize a check for this case cheaply by making dependency
extraction complain if it comes across such an RTE.  This is a
plausible way of dealing with it since part of the problem is that we
have no way to make a pg_depend representation of a named tuplestore.

Report and fix by Yugo Nagata.  Although this is an old problem,
it's a very weird corner case and there have been no reports from
end users.  So it seems sufficient to fix it in master.

Discussion: https://postgr.es/m/20240726160714.e74d0db579f2c017e1ca0b7e@sraoss.co.jp
---
 src/backend/catalog/dependency.c       | 28 ++++++++++++++++++++++++++
 src/test/regress/expected/triggers.out | 20 ++++++++++++++++++
 src/test/regress/sql/triggers.sql      | 19 +++++++++++++++++
 3 files changed, 67 insertions(+)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 2afc550540..8355481e82 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1739,6 +1739,19 @@ find_expr_references_walker(Node *node,
 			/* (done out of line, because it's a bit bulky) */
 			process_function_rte_ref(rte, var->varattno, context);
 		}
+		else if (rte->rtekind == RTE_NAMEDTUPLESTORE)
+		{
+			/*
+			 * Cataloged objects cannot depend on tuplestores, because those
+			 * have no cataloged representation.  For now we can call the
+			 * tuplestore a "transition table" because that's the only kind
+			 * exposed to SQL, but someday we might have to work harder.
+			 */
+			ereport(ERROR,
+					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					 errmsg("transition table \"%s\" cannot be referenced in a persistent object",
+							rte->eref->aliasname)));
+		}
 
 		/*
 		 * Vars referencing other RTE types require no additional work.  In
@@ -2191,7 +2204,22 @@ find_expr_references_walker(Node *node,
 					}
 					context->rtables = list_delete_first(context->rtables);
 					break;
+				case RTE_NAMEDTUPLESTORE:
+
+					/*
+					 * Cataloged objects cannot depend on tuplestores, because
+					 * those have no cataloged representation.  For now we can
+					 * call the tuplestore a "transition table" because that's
+					 * the only kind exposed to SQL, but someday we might have
+					 * to work harder.
+					 */
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("transition table \"%s\" cannot be referenced in a persistent object",
+									rte->eref->aliasname)));
+					break;
 				default:
+					/* Other RTE types can be ignored here */
 					break;
 			}
 		}
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index a044d6afe2..0657da1757 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -3315,6 +3315,26 @@ create trigger my_table_col_update_trig
 ERROR:  transition tables cannot be specified for triggers with column lists
 drop table my_table;
 --
+-- Verify that transition tables can't be used in, eg, a view.
+--
+create table my_table (a int);
+create function make_bogus_matview() returns trigger as
+$$ begin
+  create materialized view transition_test_mv as select * from new_table;
+  return new;
+end $$
+language plpgsql;
+create trigger make_bogus_matview
+  after insert on my_table
+  referencing new table as new_table
+  for each statement execute function make_bogus_matview();
+insert into my_table values (42);  -- error
+ERROR:  transition table "new_table" cannot be referenced in a persistent object
+CONTEXT:  SQL statement "create materialized view transition_test_mv as select * from new_table"
+PL/pgSQL function make_bogus_matview() line 2 at SQL statement
+drop table my_table;
+drop function make_bogus_matview();
+--
 -- Test firing of triggers with transition tables by foreign key cascades
 --
 create table refd_table (a int primary key, b text);
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 51610788b2..7e2f7597c1 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -2434,6 +2434,25 @@ create trigger my_table_col_update_trig
 
 drop table my_table;
 
+--
+-- Verify that transition tables can't be used in, eg, a view.
+--
+
+create table my_table (a int);
+create function make_bogus_matview() returns trigger as
+$$ begin
+  create materialized view transition_test_mv as select * from new_table;
+  return new;
+end $$
+language plpgsql;
+create trigger make_bogus_matview
+  after insert on my_table
+  referencing new table as new_table
+  for each statement execute function make_bogus_matview();
+insert into my_table values (42);  -- error
+drop table my_table;
+drop function make_bogus_matview();
+
 --
 -- Test firing of triggers with transition tables by foreign key cascades
 --
-- 
2.43.5

From 40f96b4ce8e93e8920e840e52b6a78f60a319efd Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon, 30 Dec 2024 15:59:32 -0500
Subject: [PATCH v4] Disallow NAMEDTUPLESTORE RTEs in stored views, rules, etc.

A named tuplestore is necessarily a transient object, so it makes
no sense to reference one in a persistent object such as a view.
We didn't previously prevent that, with the result that if you
tried you would get some weird failure about how the executor
couldn't find the tuplestore.

We can mechanize a check for this case cheaply by making dependency
extraction complain if it comes across such an RTE.  This is a
plausible way of dealing with it since part of the problem is that we
have no way to make a pg_depend representation of a named tuplestore.

Report and fix by Yugo Nagata.  Although this is an old problem,
it's a very weird corner case and there have been no reports from
end users.  So it seems sufficient to fix it in master.

Discussion: https://postgr.es/m/20240726160714.e74d0db579f2c017e1ca0b7e@sraoss.co.jp
---
 src/backend/catalog/dependency.c       | 15 +++++++++++++++
 src/test/regress/expected/triggers.out | 20 ++++++++++++++++++++
 src/test/regress/sql/triggers.sql      | 19 +++++++++++++++++++
 3 files changed, 54 insertions(+)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 096b68c7f3..18316a3968 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -2191,7 +2191,22 @@ find_expr_references_walker(Node *node,
 					}
 					context->rtables = list_delete_first(context->rtables);
 					break;
+				case RTE_NAMEDTUPLESTORE:
+
+					/*
+					 * Cataloged objects cannot depend on tuplestores, because
+					 * those have no cataloged representation.  For now we can
+					 * call the tuplestore a "transition table" because that's
+					 * the only kind exposed to SQL, but someday we might have
+					 * to work harder.
+					 */
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+							 errmsg("transition table \"%s\" cannot be referenced in a persistent object",
+									rte->eref->aliasname)));
+					break;
 				default:
+					/* Other RTE types can be ignored here */
 					break;
 			}
 		}
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index a044d6afe2..0657da1757 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -3315,6 +3315,26 @@ create trigger my_table_col_update_trig
 ERROR:  transition tables cannot be specified for triggers with column lists
 drop table my_table;
 --
+-- Verify that transition tables can't be used in, eg, a view.
+--
+create table my_table (a int);
+create function make_bogus_matview() returns trigger as
+$$ begin
+  create materialized view transition_test_mv as select * from new_table;
+  return new;
+end $$
+language plpgsql;
+create trigger make_bogus_matview
+  after insert on my_table
+  referencing new table as new_table
+  for each statement execute function make_bogus_matview();
+insert into my_table values (42);  -- error
+ERROR:  transition table "new_table" cannot be referenced in a persistent object
+CONTEXT:  SQL statement "create materialized view transition_test_mv as select * from new_table"
+PL/pgSQL function make_bogus_matview() line 2 at SQL statement
+drop table my_table;
+drop function make_bogus_matview();
+--
 -- Test firing of triggers with transition tables by foreign key cascades
 --
 create table refd_table (a int primary key, b text);
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 51610788b2..7e2f7597c1 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -2434,6 +2434,25 @@ create trigger my_table_col_update_trig
 
 drop table my_table;
 
+--
+-- Verify that transition tables can't be used in, eg, a view.
+--
+
+create table my_table (a int);
+create function make_bogus_matview() returns trigger as
+$$ begin
+  create materialized view transition_test_mv as select * from new_table;
+  return new;
+end $$
+language plpgsql;
+create trigger make_bogus_matview
+  after insert on my_table
+  referencing new table as new_table
+  for each statement execute function make_bogus_matview();
+insert into my_table values (42);  -- error
+drop table my_table;
+drop function make_bogus_matview();
+
 --
 -- Test firing of triggers with transition tables by foreign key cascades
 --
-- 
2.34.1