Date: Fri, 15 Nov 2024 17:36:47 +0900
From: Yugo NAGATA <nagata@sraoss.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: EphemeralNamedRelation and materialized view
Message-Id: <20241115173647.a62d3eaa0eaa9c4502b2504b@sraoss.co.jp>
In-Reply-To: <2436264.1730659353@sss.pgh.pa.us>
References: <20240726160714.e74d0db579f2c017e1ca0b7e@sraoss.co.jp>
	<2436264.1730659353@sss.pgh.pa.us>
Content-Type: multipart/mixed;
 boundary="Multipart=_Fri__15_Nov_2024_17_36_47_+0900_nYB.H7awhF6u6AfH"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?us-ascii?Q?uKnzVLhLvCrrZTWcnGJOnkyJJXEktNyStBArQfm7VT9UbMZqE0nb2bjHrE2O?=
 =?us-ascii?Q?kRWwXBt1IbOn8qCuFtJiVRxzYL1cf4c4ROxEIA8imy1GI5F8JuGGqhHbHSuA?=
 =?us-ascii?Q?DQNHxpe4k4uEK//Y62ovNzQrkvMcCKg4bAeNh5J/YFfIS6E27Zf0bZZY9Zte?=
 =?us-ascii?Q?segCcwgNmvYTDiMjLbuIdGR68lcdYuqhXgGXcQwyzFbbAfWqCfFPGA1R9lrD?=
 =?us-ascii?Q?djlwJDTP2NgprB7lT0sgPmUJAb15d0e1a4KssMs08Ti3YnUQSFWVgP8ZIRNN?=
 =?us-ascii?Q?VEmbH08tVc3232fIC+zsySE2qUJsAAcHMcB9Yg0lYN6/UbO2IXoDvVM0vpyu?=
 =?us-ascii?Q?Mmrp3M9SJX4UbudTRwdZq+JPuzgt9u9xd2A9yLpMeiXyljcmJ25Rt8GfM08m?=
 =?us-ascii?Q?5ik4PJIfQE7Bj9Mw10NBwThzjQXyCQ/ZnM08eItfcinMJvMadHcyeO2djmQC?=
 =?us-ascii?Q?KyqlI0WwqYar1c2Wba62RBdRPu6GFLV5B4KN81Uy1vZoQN6Dx7Br0O+xPKm4?=
 =?us-ascii?Q?5LPkK//HRCCHPKOZ5n0/4jERdNUtKW21vlfUQ2+ebEso62vXl97/mGx6sNzQ?=
 =?us-ascii?Q?7AwJUn4U/i5kQlNmunhYzazand3mnhXq6+jfBHt3QPqTS9JDGsrccl5iVVZ9?=
 =?us-ascii?Q?y5t/s1nKU9sXPSGYr0Pv8BmaGKQuA35qWg748yrBpZ6kaR0NcyIF/dMbq0eo?=
 =?us-ascii?Q?HLUiCSZofiAERMRrSdkwKDg3fepKSsqsHWfajhsAVAeY666CA66O3rA3vIeB?=
 =?us-ascii?Q?5Jk6xLXpHvT9BUyDD8AxFgK3cw9OoPJrm9fh5qutT7RCcDqaddFq543rX9RQ?=
 =?us-ascii?Q?0e/HAmYVMPnKbjFDEnm/4NCJvJqEmJ+dptdUBc7yshi4THdXK3M/SwW2UAQU?=
 =?us-ascii?Q?5c1CFCqTwrzyUuK2NMzBd5sGIg38qQqQta7ewUqJt8FVqKwNM5zE8DMrE2zQ?=
 =?us-ascii?Q?VUhSYFtyOH7gKBIxgR2igyJGmhbLfxYRG0m/UjMzFwz4kT+gzi2E05Nn/MmO?=
 =?us-ascii?Q?jzfpcXkxKqmkKUBDT752Du8/RsisWEJOKruBNfMcaEOEuZJs//oeb4G2GsyO?=
 =?us-ascii?Q?S9HArU3ttTMaq9RqAxtmER6jABIHiW3TdTQQxpymmDLrEeWDXoVKC7eqRbcH?=
 =?us-ascii?Q?g0b4P1heIf8ylAWfDHzBWlWWO6L84yejO0JCge4hSRuT9gcnk3zlvxF71/ZA?=
 =?us-ascii?Q?OgiLRFlouQ3vQRfsREp2tnQYX6rvhYuD7VviPOScsqCmEucn0M64WdknUdo5?=
 =?us-ascii?Q?hOd/uGZ14XdfhE1NPPxlGfOYT9WtS/bmMJTxxdt0bUy68R/5HHDXuSBZQBD1?=
 =?us-ascii?Q?0cWa8PxPUhv9aCoHenQp6kTpw3a31GpCr8Tf9TTcg7fVVezZWgh7vGuX+RoH?=
 =?us-ascii?Q?oTpnoetiB9kEpTj7juTlOunEuxBuVf1lc6s0QcCLjM2OPekNhAr4+P/e/V4+?=
 =?us-ascii?Q?8zdvdZwoudaqKpExWfzR3xeV6X4wN22RlCYJNxCam3PdaoUQFVrp+kaCvZBp?=
 =?us-ascii?Q?lOoX4CvTpaoTyC+ZvHY8fm1UTul9f6Tg8rlEDHDcj/uaOdUBM57Io0Z8EKoR?=
 =?us-ascii?Q?Y7sgeszlaR6FtodM6IEzJItae7PhoevWxtMub1+5?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: b77ddec3-e8b0-49a4-b3ed-08dd0550aa3b
X-MS-Exchange-CrossTenant-AuthSource: TYBP286MB0111.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 15 Nov 2024 08:36:57.0237
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: I8ryrHOiD4L8pZ/JyuSBXGeQLfAcPWMS3ArpVInPDw8ad8VFwdFkJZN2V6lz0T65RT/U6e2nn6p+qau99knQkg==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TY1P286MB3360
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/20241115173647.a62d3eaa0eaa9c4502b2504b%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Fri__15_Nov_2024_17_36_47_+0900_nYB.H7awhF6u6AfH
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Sun, 03 Nov 2024 13:42:33 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Yugo Nagata <nagata@sraoss.co.jp> writes:
> > While looking into the commit b4da732fd64e936970f38c792f8b32c4bdf2bcd5,
> > I noticed that we can create a materialized view using Ephemeral Named
> > Relation in PostgreSQL 16 or earler. 
> 
> Yeah, we should reject that, but I feel like this patch is not
> ambitious enough, because the 17-and-up behavior isn't exactly
> polished either.
> 
> I tried variants of this function in HEAD:
> 
> 1. With "create table mv as select * from enr", it works and
> does what you'd expect.
> 
> 2. With "create view mv as select * from enr", you get
> 
> regression=# insert into tbl values (10);
> ERROR:  relation "enr" does not exist
> LINE 1: create view mv as select * from enr
>                                         ^
> QUERY:  create view mv as select * from enr
> CONTEXT:  PL/pgSQL function f() line 2 at SQL statement
> regression=# \errverbose 
> ERROR:  42P01: relation "enr" does not exist
> LINE 1: create view mv as select * from enr
>                                         ^
> QUERY:  create view mv as select * from enr
> CONTEXT:  PL/pgSQL function f() line 2 at SQL statement
> LOCATION:  parserOpenTable, parse_relation.c:1452
> 
> 3. With "create materialized view ..." you get
> 
> regression=# insert into tbl values (10);
> ERROR:  executor could not find named tuplestore "enr"
> CONTEXT:  SQL statement "create materialized view mv as select * from enr"
> PL/pgSQL function f() line 2 at SQL statement
> regression=# \errverbose 
> ERROR:  XX000: executor could not find named tuplestore "enr"
> CONTEXT:  SQL statement "create materialized view mv as select * from enr"
> PL/pgSQL function f() line 2 at SQL statement
> LOCATION:  ExecInitNamedTuplestoreScan, nodeNamedtuplestorescan.c:107
> 
> I don't think hitting an internal error is good enough.
> Why doesn't this case act like case 2?

I agree that raising an internal error is not enough. I attached a updated
patch that outputs a message saying that an ENR can't be used in a matview.

> You could even argue that case 2 isn't good enough either,
> and we should be delivering a specific error message saying
> that an ENR can't be used in a view/matview.  To do that,
> we'd likely need to pass down the QueryEnvironment in more
> places not fewer.

We can raise a similar error for (not materialized) views by passing
QueryEnv to DefineView() (or in ealier stage) , but there are other
objects that can contain ENR in their definition, for examle, functions,
cursor, or RLS policies. Is it worth introducing this version of error
message for all these objects? 

Regards,
Yugo Nagata

-- 
Yugo NAGATA <nagata@sraoss.co.jp>

--Multipart=_Fri__15_Nov_2024_17_36_47_+0900_nYB.H7awhF6u6AfH
Content-Type: text/x-diff;
 name="0001-Prohibit-materialized-views-to-use-ephemeral-named-r.patch"
Content-Disposition: attachment;
 filename="0001-Prohibit-materialized-views-to-use-ephemeral-named-r.patch"
Content-Transfer-Encoding: 7bit

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


--Multipart=_Fri__15_Nov_2024_17_36_47_+0900_nYB.H7awhF6u6AfH--