rebased
Jim
From 0ace0c986371a3bc13b4e0e7128b06e751382aa6 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Wed, 8 Oct 2025 21:12:32 +0200
Subject: [PATCH v3] Disallow ATOMIC functions depending on 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 silently.
To avoid confusion and inconsistent behavior, reject creation
of such functions with an ERROR at CREATE FUNCTION time.
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.
This restriction prevents surprising behavior and aligns with
the expectation that permanent objects should not depend on
session-local state.
This patch also slightly modifies the regress tests in returning.sql,
as it creates a BEGIN ATOMIC SQL function in its tests. The table
used in the test is now permanent.
---
src/backend/catalog/pg_proc.c | 9 ++-
src/backend/executor/functions.c | 55 +++++++++++++++++--
src/include/executor/functions.h | 2 +-
.../regress/expected/create_function_sql.out | 13 +++++
src/test/regress/expected/returning.out | 52 +++++++++---------
src/test/regress/sql/create_function_sql.sql | 13 +++++
src/test/regress/sql/returning.sql | 3 +-
7 files changed, 113 insertions(+), 34 deletions(-)
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index b89b9ccda0..67a0ff3c2b 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -27,6 +27,7 @@
#include "catalog/pg_transform.h"
#include "catalog/pg_type.h"
#include "executor/functions.h"
+#include "catalog/namespace.h"
#include "funcapi.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
@@ -974,7 +975,13 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
Oid rettype;
TupleDesc rettupdesc;
- check_sql_fn_statements(querytree_list);
+ /* If true, validator will error if the function body references any
+ * temporary relation. Computed here as: function has a prosqlbody (i.e.
+ * defined with BEGIN ATOMIC) and is being created in a non-temp schema.
+ */
+ bool error_on_temp_deps = !isnull && !isAnyTempNamespace(proc->pronamespace);
+
+ check_sql_fn_statements(querytree_list, error_on_temp_deps);
(void) get_func_result_type(funcoid, &rettype, &rettupdesc);
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 630d708d2a..71416a0812 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 enforce_no_temp_deps);
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 enforce_no_temp_deps flag indicates whether the function was defined
+ * using SQL-standard BEGIN ATOMIC. In that case we additionally check for
+ * dependencies on temporary relations and throw an ERROR if found.
*/
void
-check_sql_fn_statements(List *queryTreeLists)
+check_sql_fn_statements(List *queryTreeLists, bool enforce_no_temp_deps)
{
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, enforce_no_temp_deps);
}
}
@@ -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 enforce_no_temp_deps)
{
ListCell *lc;
@@ -2057,6 +2061,45 @@ check_sql_fn_statement(List *queryTreeList)
{
Query *query = lfirst_node(Query, lc);
+ if (enforce_no_temp_deps)
+ {
+ 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 throw any error 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 throw an ERROR at CREATE FUNCTION time if
+ * an ATOMIC function depends on a temporary 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(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("SQL function defined with BEGIN ATOMIC cannot depend on temporary relation \"%s\"",
+ get_rel_name(rte->relid))));
+ }
+ }
+ }
+
/*
* 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..ebc73ff40d 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 enforce_no_temp_deps);
extern bool check_sql_fn_retval(List *queryTreeLists,
Oid rettype, TupleDesc rettupdesc,
diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out
index 73c6730d45..0269175ea6 100644
--- a/src/test/regress/expected/create_function_sql.out
+++ b/src/test/regress/expected/create_function_sql.out
@@ -292,6 +292,19 @@ CREATE FUNCTION functest_S_xxx(x int) RETURNS int
AS $$ SELECT x * 2 $$
RETURN x * 3;
ERROR: duplicate function body specified
+-- error: BEGIN ATOMIC SQL-functions cannot depend on temporary tables
+CREATE TEMPORARY TABLE functest_temp AS SELECT 1 AS val;
+CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM functest_temp;
+END;
+ERROR: SQL function defined with BEGIN ATOMIC cannot depend on temporary relation "functest_temp"
+CONTEXT: SQL function "functest_temp_dep"
+-- this should work, as the function is created in a temp schema
+CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM functest_temp;
+END;
-- polymorphic arguments not allowed in this form
CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
LANGUAGE SQL
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index d02c2ceab5..30538c8526 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -2,7 +2,7 @@
-- Test INSERT/UPDATE/DELETE RETURNING
--
-- Simple cases
-CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
+CREATE TABLE foo (f1 serial, f2 text, f3 int default 42);
INSERT INTO foo (f2,f3)
VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9)
RETURNING *, f1+f3 AS sum;
@@ -447,7 +447,7 @@ INSERT INTO foo VALUES (4)
new.tableoid::regclass, new.ctid, new.*, *;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Insert on pg_temp.foo
+ Insert on public.foo
Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo.f1, foo.f2, foo.f3, foo.f4
-> Result
Output: 4, NULL::text, 42, '99'::bigint
@@ -471,7 +471,7 @@ INSERT INTO foo VALUES (4, 'conflict'), (5, 'ok')
n.tableoid::regclass, n.ctid, n.*, *;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
- Insert on pg_temp.foo
+ Insert on public.foo
Output: (o.tableoid)::regclass, o.ctid, o.f1, o.f2, o.f3, o.f4, (n.tableoid)::regclass, n.ctid, n.f1, n.f2, n.f3, n.f4, foo.f1, foo.f2, foo.f3, foo.f4
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: foo_f1_idx
@@ -498,12 +498,12 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5
old.f4::text||'->'||new.f4::text AS change;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Update on pg_temp.foo
+ Update on public.foo
Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, old.*, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, new.*, (((old.f4)::text || '->'::text) || (new.f4)::text)
- Update on pg_temp.foo foo_1
+ Update on public.foo foo_1
-> Result
Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
- -> Seq Scan on pg_temp.foo foo_1
+ -> Seq Scan on public.foo foo_1
Output: foo_1.tableoid, foo_1.ctid
Filter: (foo_1.f1 = 5)
(8 rows)
@@ -524,10 +524,10 @@ DELETE FROM foo WHERE f1 = 5
new.tableoid::regclass, new.ctid, new.*, *;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Delete on pg_temp.foo
+ Delete on public.foo
Output: (old.tableoid)::regclass, old.ctid, old.f1, old.f2, old.f3, old.f4, (new.tableoid)::regclass, new.ctid, new.f1, new.f2, new.f3, new.f4, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4
- Delete on pg_temp.foo foo_1
- -> Seq Scan on pg_temp.foo foo_1
+ Delete on public.foo foo_1
+ -> Seq Scan on public.foo foo_1
Output: foo_1.tableoid, foo_1.ctid
Filter: (foo_1.f1 = 5)
(6 rows)
@@ -547,7 +547,7 @@ INSERT INTO foo VALUES (5, 'subquery test')
(SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
QUERY PLAN
---------------------------------------------------------------
- Insert on pg_temp.foo
+ Insert on public.foo
Output: (SubPlan expr_1), (SubPlan expr_2)
-> Result
Output: 5, 'subquery test'::text, 42, '99'::bigint
@@ -580,12 +580,12 @@ UPDATE foo SET f4 = 100 WHERE f1 = 5
(SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
QUERY PLAN
----------------------------------------------------------------
- Update on pg_temp.foo
+ Update on public.foo
Output: (SubPlan expr_1), (SubPlan expr_2), (SubPlan expr_3)
- Update on pg_temp.foo foo_1
+ Update on public.foo foo_1
-> Result
Output: '100'::bigint, foo_1.tableoid, foo_1.ctid
- -> Seq Scan on pg_temp.foo foo_1
+ -> Seq Scan on public.foo foo_1
Output: foo_1.tableoid, foo_1.ctid
Filter: (foo_1.f1 = 5)
SubPlan expr_1
@@ -620,10 +620,10 @@ DELETE FROM foo WHERE f1 = 5
(SELECT max(new.f4 + x) FROM generate_series(1, 10) x) new_max;
QUERY PLAN
---------------------------------------------------------------
- Delete on pg_temp.foo
+ Delete on public.foo
Output: (SubPlan expr_1), (SubPlan expr_2)
- Delete on pg_temp.foo foo_1
- -> Seq Scan on pg_temp.foo foo_1
+ Delete on public.foo foo_1
+ -> Seq Scan on public.foo foo_1
Output: foo_1.tableoid, foo_1.ctid
Filter: (foo_1.f1 = 5)
SubPlan expr_1
@@ -656,15 +656,15 @@ EXPLAIN (verbose, costs off)
DELETE FROM foo WHERE f1 = 4 RETURNING old.*,new.*, *;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
- Update on pg_temp.foo
+ Update on public.foo
Output: old.f1, old.f2, old.f3, old.f4, new.f1, new.f2, new.f3, new.f4, foo_2.f1, foo_2.f2, foo_2.f3, foo_2.f4
- Update on pg_temp.foo foo_2
+ Update on public.foo foo_2
-> Nested Loop
Output: (foo_2.f2 || ' (deleted)'::text), '-1'::integer, '-1'::bigint, foo_1.ctid, foo_1.tableoid, foo_2.tableoid, foo_2.ctid
- -> Seq Scan on pg_temp.foo foo_2
+ -> Seq Scan on public.foo foo_2
Output: foo_2.f2, foo_2.f1, foo_2.tableoid, foo_2.ctid
Filter: (foo_2.f1 = 4)
- -> Seq Scan on pg_temp.foo foo_1
+ -> Seq Scan on public.foo foo_1
Output: foo_1.ctid, foo_1.f1, foo_1.tableoid
Filter: (foo_1.f1 = 4)
(11 rows)
@@ -681,9 +681,9 @@ UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57
RETURNING old.*, new.*, *, new.f3 - old.f3 AS delta_f3;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Update on pg_temp.foo
+ Update on public.foo
Output: old.f1, old.f2, old.f3, old.f4, joinme.other, new.f1, new.f2, new.f3, new.f4, joinme.other, foo_1.f1, foo_1.f2, foo_1.f3, foo_1.f4, joinme.other, (new.f3 - old.f3)
- Update on pg_temp.foo foo_1
+ Update on public.foo foo_1
-> Hash Join
Output: foo_2.f1, (foo_2.f3 + 1), joinme.ctid, foo_2.ctid, joinme_1.ctid, joinme.other, foo_1.tableoid, foo_1.ctid, foo_2.tableoid
Hash Cond: (foo_1.f2 = joinme.f2j)
@@ -694,7 +694,7 @@ UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57
Output: joinme_1.ctid, joinme_1.f2j
-> Hash
Output: foo_1.f2, foo_1.tableoid, foo_1.ctid
- -> Seq Scan on pg_temp.foo foo_1
+ -> Seq Scan on public.foo foo_1
Output: foo_1.f2, foo_1.tableoid, foo_1.ctid
-> Hash
Output: joinme.ctid, joinme.other, joinme.f2j, foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid
@@ -705,7 +705,7 @@ UPDATE joinview SET f3 = f3 + 1 WHERE f3 = 57
Output: joinme.ctid, joinme.other, joinme.f2j
-> Hash
Output: foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid
- -> Seq Scan on pg_temp.foo foo_2
+ -> Seq Scan on public.foo foo_2
Output: foo_2.f1, foo_2.f3, foo_2.ctid, foo_2.f2, foo_2.tableoid
Filter: (foo_2.f3 = 57)
(27 rows)
@@ -768,7 +768,7 @@ UPDATE joinview SET f3 = f3 + 1, f4 = 7 WHERE f3 = 58
Output: joinme.other, joinme.ctid, joinme.f2j
-> Hash
Output: foo.f3, foo.f1, foo.f2, foo.f4, foo.ctid, foo.tableoid
- -> Seq Scan on pg_temp.foo
+ -> Seq Scan on public.foo
Output: foo.f3, foo.f1, foo.f2, foo.f4, foo.ctid, foo.tableoid
Filter: (foo.f3 = 58)
(12 rows)
@@ -986,3 +986,5 @@ BEGIN ATOMIC
WHERE (foo_1.* = n.*)) AS count;
END
DROP FUNCTION foo_update;
+DROP TABLE foo CASCADE;
+NOTICE: drop cascades to view voo
diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql
index 3d5f2a9209..f16844ee29 100644
--- a/src/test/regress/sql/create_function_sql.sql
+++ b/src/test/regress/sql/create_function_sql.sql
@@ -194,6 +194,19 @@ CREATE FUNCTION functest_S_xxx(x int) RETURNS int
AS $$ SELECT x * 2 $$
RETURN x * 3;
+-- error: BEGIN ATOMIC SQL-functions cannot depend on temporary tables
+CREATE TEMPORARY TABLE functest_temp AS SELECT 1 AS val;
+CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM functest_temp;
+END;
+
+-- this should work, as the function is created in a temp schema
+CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM functest_temp;
+END;
+
-- polymorphic arguments not allowed in this form
CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
LANGUAGE SQL
diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql
index cc99cb53f6..193ce6275e 100644
--- a/src/test/regress/sql/returning.sql
+++ b/src/test/regress/sql/returning.sql
@@ -4,7 +4,7 @@
-- Simple cases
-CREATE TEMP TABLE foo (f1 serial, f2 text, f3 int default 42);
+CREATE TABLE foo (f1 serial, f2 text, f3 int default 42);
INSERT INTO foo (f2,f3)
VALUES ('test', DEFAULT), ('More', 11), (upper('more'), 7+9)
@@ -408,3 +408,4 @@ END;
\sf foo_update
DROP FUNCTION foo_update;
+DROP TABLE foo CASCADE;
--
2.43.0
