Horton

Re: Add notification on BEGIN ATOMIC SQL functions using temp relations

Jim Jones <jim.jones@uni-muenster.de>

View thread
From: Jim Jones <jim.jones@uni-muenster.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, Pavel Stehule <pavel.stehule@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-11-22T23:09:03Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Improve detection of implicitly-temporary views.

  2. Issue a NOTICE if a created function depends on any temp objects.

Attachments


On 22/11/2025 19:37, Tom Lane wrote:
> After sleeping on it, I'm inclined to word the notice like
> 
> NOTICE:  function "f" will be effectively temporary
> DETAIL:  It depends on temporary <object descriptor>.


I changed the error level to NOTICE:

postgres=# CREATE TEMPORARY TABLE temp_table AS SELECT 1 AS val;
SELECT 1
postgres=# CREATE FUNCTION temp_func() RETURNS int LANGUAGE sql
BEGIN ATOMIC;
  SELECT val FROM temp_table;
END;
NOTICE:  function "temp_func" will be effectively temporary
DETAIL:  It depends on temporary table temp_table.
CREATE FUNCTION

I reverted the changes in the other test cases, with the exception of
this change in returning.sql (although unrelated to this patch):


diff --git a/src/test/regress/sql/returning.sql
b/src/test/regress/sql/returning.sql
index cc99cb53f6..f1c85a9731 100644
--- a/src/test/regress/sql/returning.sql
+++ b/src/test/regress/sql/returning.sql
@@ -2,6 +2,11 @@
 -- Test INSERT/UPDATE/DELETE RETURNING
 --

+-- This script is full of poorly-chosen object names.
+-- Put them in a separate schema to avoid collisions with concurrent
scripts.
+CREATE SCHEMA returning_test;
+SET search_path = returning_test, public;
+
 -- Simple cases

 CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
@@ -407,4 +412,7 @@ BEGIN ATOMIC
 END;

 \sf foo_update
-DROP FUNCTION foo_update;
+
+-- Clean up
+RESET search_path;
+DROP SCHEMA returning_test CASCADE;
-- 


I also significantly reduced the tests I previously added to
create_function_sql.sql, leaving only tests for temporary views,
sequences, temporary functions (in pg_temp schema), and domains.

v8 attached.

Thanks!

Best, Jim