Date: Fri, 26 Jul 2024 16:07:14 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: pgsql-hackers@postgresql.org
Subject: EphemeralNamedRelation and materialized view
Message-Id: <20240726160714.e74d0db579f2c017e1ca0b7e@sraoss.co.jp>
Content-Type: multipart/mixed;
 boundary="Multipart=_Fri__26_Jul_2024_16_07_14_+0900_nNG3cwxTyTVMvGey"
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?us-ascii?Q?Lec4AtAIsGJgzYfcUggcK1pqR4kJoAgBz/IeetNoFCfYjjWKY0ztzL6UtAGG?=
 =?us-ascii?Q?YjxfGE5nJ/k/fq9i7R78QoUsbNWr1Xtz8M6EW+W2KOl87HUOP+C5NGc8OFYv?=
 =?us-ascii?Q?REo15KUzphzY9/YVVrwHiFrYIOkXZaHOVUyKz3PQd+vntXg91T3mwwqyA/6f?=
 =?us-ascii?Q?Ff6rWrHsOfuD44NIdWF/9DyB5cjRrLy1BC+1MLrZT22j/6uPk/DrbtY8D0qj?=
 =?us-ascii?Q?/qMzBS752woSH1ZrPps/izL4SeM50H/uf4p+4mK4gcCa0dqrDyMnHJ58u2ap?=
 =?us-ascii?Q?AoDnrmcT9uPMxabMeiz5/qxWB70e2kZsMlncG1i0cEgSpkKnxSIGqZ5RCKvW?=
 =?us-ascii?Q?M1FwDM3TM/AvN6yXS5OGP/Q8BO/6dhjHtUkcb9tZHEtCxTPhp8DzYQ2s+Zw5?=
 =?us-ascii?Q?SCxdPzjkZNJgV4Va2KOoREZhUIBmNJeIVRHcl2a3mUFOn2D3KwLMwjfURYKG?=
 =?us-ascii?Q?bX6guYIdK0MRrpAKWvZN1gUj9KdNr32HU3VDsdsX7Ck8LLXkp/l/1y0bye/N?=
 =?us-ascii?Q?LyQNOJ/JI6iX1DWP7PuSie7DM1CXHsJCxT5vi5sBwNiO4yc0XkH3R4lZAEz4?=
 =?us-ascii?Q?fwwcOsLbB1+opw9szUT0h+kTFD41Npwn/Ur2TupPsMd85SrCemN9/Cgk/rW2?=
 =?us-ascii?Q?u1bb5NRBfmLOVm/7j80qDwTYo1aCtbMmLTzlClmQ7MK80dTORjDDRFlYRJa/?=
 =?us-ascii?Q?FpdtcCxs9vMyUCv5u5aoiOPmLf+bQUPrS4Walpog+tX9SaQ6tII5PV5fxgzH?=
 =?us-ascii?Q?Clb3qHvAB2MuK7rXmTMPn8DVj9ZIrk69WEtD/gRELefSnR7WCYSYkRqY/rYG?=
 =?us-ascii?Q?KbKTXWql64bqvmrE3EqHBfIIZkCmWL3XESOCAGg1P3bjBWzNd3DHHCPCe3gL?=
 =?us-ascii?Q?v7YM0xKSCa3Ung/EOljVvmp4vuTj4lXFPR9HJQMYUqtRJfrvZxQoxLJ1Ibz0?=
 =?us-ascii?Q?ZOCXBERozvTQ8yQBSO74Z1feFwvd9AaneCj+vYNHcHgpX++A1betPjjjakoR?=
 =?us-ascii?Q?NxVcAd/FBjvCi3Fl/z4okhKB8kXLmLviMpC3gk0zpOGNNlfNkB5/lw3aSQE7?=
 =?us-ascii?Q?VAP3AuPwuaGL8oa2oLlLRTmrZ3HHMjxBiFPA6zy7n2S46Bdy6Ko6lV7J+h/p?=
 =?us-ascii?Q?7QaxWBS9d0KN3T3aNPB4W9lYwhsUirrC+2JPFxj8bongFR9lVcwANfbNpvNI?=
 =?us-ascii?Q?f4wyGVDHFMVi+znxlXVMkkNEsOfBwAHGhaLD9ScFFU/2o3u74IyacezXFhrF?=
 =?us-ascii?Q?xgIsnvx+ZJ5NGvISs7APk10224n6hIhb0MYFwqs6brMscEt0z8+Ks0CNF8a0?=
 =?us-ascii?Q?kJuSi4Ew+YyyAMRgTWXTqKQlOB18Hp5LkZNQkCT4PmRBJZYSBWFgFJ+c30jM?=
 =?us-ascii?Q?AVge7LqKNiVlqHRGEm5YcNBY4aA9B4k/E3xtrRbFcZJOvHongFsqSVJaJjiQ?=
 =?us-ascii?Q?pKk4hlkFpNyM7EbweoxgfMdezTOqDK9I2HDWV57dkTwQQt1Om10X0EZSCNLc?=
 =?us-ascii?Q?r5g3Qlr9TQ8cNYYSeHuXe2w1GNbR8XbSnTuRcCgl3ffiEbglFog7w2QEs0hP?=
 =?us-ascii?Q?KJGvlwY8qMiKi6s+4x67bSj+ZdVoN37dMA5prIDEykePZSHvHsoDIETqf1jg?=
 =?us-ascii?Q?DuWE+JHogv1KJgQEfJhf/5zBMXc2tQRNf8aygzqUNWGz?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 123ff4b0-fdf5-4d96-2c55-08dcad4193cc
X-MS-Exchange-CrossTenant-AuthSource: TYBP286MB0111.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 26 Jul 2024 07:07:14.6161
 (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: KfiJskARLLFvYN32MnvnwtUL0EvYWDgTxm6ZKpgvPpGrBI8p+HIPK6BVdklouAztVfCHvqStv3CVAWSvLRAT6w==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYRP286MB4915
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/20240726160714.e74d0db579f2c017e1ca0b7e%40sraoss.co.jp>
Precedence: bulk

--Multipart=_Fri__26_Jul_2024_16_07_14_+0900_nNG3cwxTyTVMvGey
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi,

While looking into the commit b4da732fd64e936970f38c792f8b32c4bdf2bcd5,
I noticed that we can create a materialized view using Ephemeral Named
Relation in PostgreSQL 16 or earler. 


postgres=# create table tbl (i int);
CREATE TABLE
                                                     ^
postgres=# create or replace function f() returns trigger as $$ begin 
 create materialized view mv as select * from enr; return new; end; $$ language plpgsql;
CREATE FUNCTION

postgres=# create trigger trig after insert on tbl referencing new table as enr execute function f();
CREATE TRIGGER

postgres=# insert into tbl values (10);

postgres=# \d
             List of relations
 Schema | Name |       Type        | Owner  
--------+------+-------------------+--------
 public | mv   | materialized view | yugo-n
 public | tbl  | table             | yugo-n
(2 rows)


We cannot refresh or get the deinition of it, though.

postgres=# refresh materialized view mv;
ERROR:  executor could not find named tuplestore "enr"

postgres=# \d+ mv
ERROR:  unrecognized RTE kind: 7

In PostgreSQL 17, materialized view using ENR cannot be created 
because queryEnv is not pass to RefreshMatViewByOid introduced by b4da732fd64.
When we try to create it, the  error is raised.

 ERROR: executor could not find named tuplestore "enr"

Although it is hard to imagine users actually try to create materialized view
using ENR, how about prohibiting it even in PG16 or earlier by passing NULL
as queryEnv arg in CreateQueryDesc to avoid to create useless matviews accidentally,
as the attached patch?


Regards,
Yugo Nagata

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

--Multipart=_Fri__26_Jul_2024_16_07_14_+0900_nNG3cwxTyTVMvGey
Content-Type: text/x-diff;
 name="prohibit_use_enr_in_matview.patch"
Content-Disposition: attachment;
 filename="prohibit_use_enr_in_matview.patch"
Content-Transfer-Encoding: 7bit

diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index e91920ca14..fda1e4d92b 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -327,7 +327,7 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
 		/* Create a QueryDesc, redirecting output to our tuple receiver */
 		queryDesc = CreateQueryDesc(plan, pstate->p_sourcetext,
 									GetActiveSnapshot(), InvalidSnapshot,
-									dest, params, queryEnv, 0);
+									dest, params, is_matview ? NULL : queryEnv, 0);
 
 		/* call ExecutorStart to prepare the plan for execution */
 		ExecutorStart(queryDesc, GetIntoRelEFlags(into));

--Multipart=_Fri__26_Jul_2024_16_07_14_+0900_nNG3cwxTyTVMvGey--