Hi,
While reviewing a patch I noticed that SQL functions defined with BEGIN
ATOMIC can reference temporary relations, and such functions are
(rightfully) dropped at session end --- but without any notification to
the user:
$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.
postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1
postgres=# CREATE FUNCTION tmpval_atomic()
RETURNS int LANGUAGE sql
BEGIN ATOMIC;
SELECT val FROM tmp;
END;
CREATE FUNCTION
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+---------------+------------------+---------------------+------
public | tmpval_atomic | integer | | func
(1 row)
postgres=# \q
$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.
postgres=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
Although this behaviour is expected, it can be surprising. A NOTICE or
WARNING at CREATE FUNCTION time could save some head-scratching later.
We already have a precedent. When creating a view that depends on a
temporary relation, postgres automatically makes it a temporary view and
emits a NOTICE:
postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1
postgres=# CREATE VIEW v AS SELECT * FROM tmp;
NOTICE: view "v" will be a temporary view
CREATE VIEW
postgres=# \d
List of relations
Schema | Name | Type | Owner
------------+------+-------+-------
pg_temp_74 | tmp | table | jim
pg_temp_74 | v | view | jim
(2 rows)
postgres=# \q
$ /usr/local/postgres-dev/bin/psql postgres
psql (19devel)
Type "help" for help.
postgres=# \d
Did not find any relations.
Attached a PoC that issues a WARNING if a BEGIN ATOMIC function is
created using temporary objects:
postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
SELECT 1
postgres=# CREATE FUNCTION tmpval_atomic()
RETURNS int LANGUAGE sql
BEGIN ATOMIC;
SELECT val FROM tmp;
END;
WARNING: function defined with BEGIN ATOMIC depends on temporary
relation "tmp"
DETAIL: the function will be dropped automatically at session end.
CREATE FUNCTION
This PoC adds a parameter to check_sql_fn_statements() and
check_sql_fn_statement(), so I’m not entirely sure if that’s the best
approach. I’m also not sure whether a NOTICE would be a better fit than
a WARNING here. Feedback is welcome.
Any thoughts?
Best regards, Jim
From 86c13fdf447f090ba208cc93b9c68562364cac97 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Sun, 21 Sep 2025 13:07:06 +0200
Subject: [PATCH v1] Add WARNING on BEGIN ATOMIC SQL functions using temp
relations
When a SQL function is defined with a BEGIN ATOMIC block and
references a temporary relation, the function is dropped at
session end along with the temp relation. From the user's
perspective, the function looks like a persistent database
object in a permanent schema, yet it disappears without notice.
To reduce confusion, emit a WARNING at CREATE FUNCTION time if
a BEGIN ATOMIC SQL function depends on a relation in a temporary
namespace.
This mirrors the behavior of CREATE VIEW, which issues a NOTICE
when a view depends on temporary relations and is therefore made
temporary itself.
Regular SQL functions without BEGIN ATOMIC are unaffected: they
may also reference temporary objects, but they remain present
after session end and simply fail at execution time if called.
Regression tests are updated to cover the new WARNING.
---
src/backend/catalog/pg_proc.c | 2 +-
src/backend/executor/functions.c | 55 ++++++++++++++++++++++---
src/include/executor/functions.h | 2 +-
src/test/regress/expected/returning.out | 2 +
4 files changed, 53 insertions(+), 8 deletions(-)
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 75b17fed15..1b42a6f4ee 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -974,7 +974,7 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
Oid rettype;
TupleDesc rettupdesc;
- check_sql_fn_statements(querytree_list);
+ check_sql_fn_statements(querytree_list, !isnull);
(void) get_func_result_type(funcoid, &rettype, &rettupdesc);
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 630d708d2a..204c8f371a 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -223,7 +223,7 @@ static void sql_compile_error_callback(void *arg);
static void sql_exec_error_callback(void *arg);
static void ShutdownSQLFunction(Datum arg);
static void RemoveSQLFunctionCache(void *arg);
-static void check_sql_fn_statement(List *queryTreeList);
+static void check_sql_fn_statement(List *queryTreeList, bool is_atomic);
static bool check_sql_stmt_retval(List *queryTreeList,
Oid rettype, TupleDesc rettupdesc,
char prokind, bool insertDroppedCols);
@@ -950,7 +950,7 @@ prepare_next_query(SQLFunctionHashEntry *func)
/*
* Check that there are no statements we don't want to allow.
*/
- check_sql_fn_statement(queryTree_list);
+ check_sql_fn_statement(queryTree_list, false);
/*
* If this is the last query, check that the function returns the type it
@@ -1247,7 +1247,7 @@ sql_postrewrite_callback(List *querytree_list, void *arg)
* there's no real point in this because the result can't change from what
* we saw originally. But it's cheap and maybe someday it will matter.)
*/
- check_sql_fn_statement(querytree_list);
+ check_sql_fn_statement(querytree_list, false);
/*
* If this is the last query, we must re-do what check_sql_stmt_retval did
@@ -2030,9 +2030,13 @@ RemoveSQLFunctionCache(void *arg)
*
* Check statements in an SQL function. Error out if there is anything that
* is not acceptable.
+ *
+ * The is_atomic flag indicates whether the function was defined using
+ * SQL-standard BEGIN ATOMIC. In that case we additionally check for
+ * dependencies on temporary relations and issue a WARNING if found.
*/
void
-check_sql_fn_statements(List *queryTreeLists)
+check_sql_fn_statements(List *queryTreeLists, bool is_atomic)
{
ListCell *lc;
@@ -2041,7 +2045,7 @@ check_sql_fn_statements(List *queryTreeLists)
{
List *sublist = lfirst_node(List, lc);
- check_sql_fn_statement(sublist);
+ check_sql_fn_statement(sublist, is_atomic);
}
}
@@ -2049,7 +2053,7 @@ check_sql_fn_statements(List *queryTreeLists)
* As above, for a single sublist of Queries.
*/
static void
-check_sql_fn_statement(List *queryTreeList)
+check_sql_fn_statement(List *queryTreeList, bool is_atomic)
{
ListCell *lc;
@@ -2057,6 +2061,45 @@ check_sql_fn_statement(List *queryTreeList)
{
Query *query = lfirst_node(Query, lc);
+ if (is_atomic)
+ {
+ ListCell *lc2;
+
+ /*
+ * Walk the range table of this query to detect references to
+ * temporary relations.
+ *
+ * For ordinary SQL functions, the function definition is stored
+ * permanently even if it refers to temporary relations. Once
+ * the session ends and the temp relation vanishes, the function
+ * still exists but will fail at execution time with "relation
+ * does not exist". We do not issue any warning at creation
+ * time in that case; the function is simply left in place.
+ *
+ * For functions USING SQL-standard BEGIN ATOMIC definition,
+ * this situation is more confusing: the function appears to be
+ * a permanent database object, but in fact its lifetime is tied
+ * to session-local objects that vanish at session end. To avoid
+ * silent surprises, we emit a WARNING at CREATE FUNCTION time if
+ * an ATOMIC function depends on a temp relation.
+ */
+ foreach (lc2, query->rtable)
+ {
+ RangeTblEntry *rte = lfirst_node(RangeTblEntry, lc2);
+
+ if (rte->rtekind == RTE_RELATION)
+ {
+ Oid relnsp = get_rel_namespace(rte->relid);
+
+ if (isAnyTempNamespace(relnsp))
+ ereport(WARNING,
+ (errmsg("function defined with BEGIN ATOMIC depends on temporary relation \"%s\"",
+ get_rel_name(rte->relid)),
+ errdetail("the function will be dropped automatically at session end.")));
+ }
+ }
+ }
+
/*
* Disallow calling procedures with output arguments. The current
* implementation would just throw the output values away, unless the
diff --git a/src/include/executor/functions.h b/src/include/executor/functions.h
index 58bdff9b03..466e69fe24 100644
--- a/src/include/executor/functions.h
+++ b/src/include/executor/functions.h
@@ -43,7 +43,7 @@ extern SQLFunctionParseInfoPtr prepare_sql_fn_parse_info(HeapTuple procedureTupl
extern void sql_fn_parser_setup(struct ParseState *pstate,
SQLFunctionParseInfoPtr pinfo);
-extern void check_sql_fn_statements(List *queryTreeLists);
+extern void check_sql_fn_statements(List *queryTreeLists, bool is_atomic);
extern bool check_sql_fn_retval(List *queryTreeLists,
Oid rettype, TupleDesc rettupdesc,
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index 341b689f76..731e6cb782 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -930,6 +930,8 @@ BEGIN ATOMIC
(SELECT count(*) FROM foo WHERE foo = o),
(SELECT count(*) FROM foo WHERE foo = n);
END;
+WARNING: function defined with BEGIN ATOMIC depends on temporary relation "foo"
+DETAIL: the function will be dropped automatically at session end.
\sf foo_update
CREATE OR REPLACE FUNCTION public.foo_update()
RETURNS void
--
2.43.0
