On 10/13/25 17:16, Jim Jones wrote:
> PFA a first attempt to address your points.
Oops... wrong files. Sorry.
PFA the correct version.
Jim
From 5e538c3cab1db93ffdff821007b900d1ffd60e39 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Mon, 13 Oct 2025 13:48:08 +0200
Subject: [PATCH v5 1/2] Refactor dependency recording to enable dependency
collection
Add new function collectDependenciesFromExpr() that collects object
dependencies into caller-supplied ObjectAddresses structures without
immediately recording them.
This enables more flexible dependency handling patterns where callers
need to examine, filter, or modify dependencies before recording them.
The existing recordDependencyOnExpr() function is reimplemented using
the new collection function, maintaining full backward compatibility.
This refactoring lays the groundwork for enhanced dependency validation
in SQL functions while preserving all existing functionality.
---
src/backend/catalog/dependency.c | 64 ++++++++++++++++++++++----------
src/include/catalog/dependency.h | 3 ++
2 files changed, 48 insertions(+), 19 deletions(-)
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index 7dded634eb..d6142b1750 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -1553,10 +1553,41 @@ void
recordDependencyOnExpr(const ObjectAddress *depender,
Node *expr, List *rtable,
DependencyType behavior)
+{
+ ObjectAddresses *addrs;
+
+ addrs = new_object_addresses();
+
+ /* Collect all dependencies from the expression */
+ collectDependenciesFromExpr(addrs, expr, rtable);
+
+ /* And record 'em */
+ recordMultipleDependencies(depender,
+ addrs->refs, addrs->numrefs,
+ behavior);
+
+ free_object_addresses(addrs);
+}
+
+/*
+ * collectDependenciesFromExpr - collect expression dependencies
+ *
+ * This function analyzes an expression or query in node-tree form to find all
+ * the objects it refers to (tables, columns, operators, functions, etc.) and
+ * adds them to the provided ObjectAddresses structure. Unlike recordDependencyOnExpr,
+ * this function does not immediately record the dependencies, allowing the caller
+ * to examine, filter, or modify the collected dependencies before recording them.
+ *
+ * This is particularly useful when dependency recording needs to be conditional
+ * or when dependencies from multiple sources need to be merged before recording.
+ */
+void
+collectDependenciesFromExpr(ObjectAddresses *addrs,
+ Node *expr, List *rtable)
{
find_expr_references_context context;
- context.addrs = new_object_addresses();
+ context.addrs = addrs;
/* Set up interpretation for Vars at varlevelsup = 0 */
context.rtables = list_make1(rtable);
@@ -1565,14 +1596,7 @@ recordDependencyOnExpr(const ObjectAddress *depender,
find_expr_references_walker(expr, &context);
/* Remove any duplicates */
- eliminate_duplicate_dependencies(context.addrs);
-
- /* And record 'em */
- recordMultipleDependencies(depender,
- context.addrs->refs, context.addrs->numrefs,
- behavior);
-
- free_object_addresses(context.addrs);
+ eliminate_duplicate_dependencies(addrs);
}
/*
@@ -1599,10 +1623,12 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
DependencyType self_behavior,
bool reverse_self)
{
+ ObjectAddresses *addrs;
find_expr_references_context context;
RangeTblEntry rte = {0};
- context.addrs = new_object_addresses();
+ addrs = new_object_addresses();
+ context.addrs = addrs;
/* We gin up a rather bogus rangetable list to handle Vars */
rte.type = T_RangeTblEntry;
@@ -1617,11 +1643,11 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
find_expr_references_walker(expr, &context);
/* Remove any duplicates */
- eliminate_duplicate_dependencies(context.addrs);
+ eliminate_duplicate_dependencies(addrs);
/* Separate self-dependencies if necessary */
if ((behavior != self_behavior || reverse_self) &&
- context.addrs->numrefs > 0)
+ addrs->numrefs > 0)
{
ObjectAddresses *self_addrs;
ObjectAddress *outobj;
@@ -1630,11 +1656,11 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
self_addrs = new_object_addresses();
- outobj = context.addrs->refs;
+ outobj = addrs->refs;
outrefs = 0;
- for (oldref = 0; oldref < context.addrs->numrefs; oldref++)
+ for (oldref = 0; oldref < addrs->numrefs; oldref++)
{
- ObjectAddress *thisobj = context.addrs->refs + oldref;
+ ObjectAddress *thisobj = addrs->refs + oldref;
if (thisobj->classId == RelationRelationId &&
thisobj->objectId == relId)
@@ -1644,13 +1670,13 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
}
else
{
- /* Keep it in context.addrs */
+ /* Keep it in addrs */
*outobj = *thisobj;
outobj++;
outrefs++;
}
}
- context.addrs->numrefs = outrefs;
+ addrs->numrefs = outrefs;
/* Record the self-dependencies with the appropriate direction */
if (!reverse_self)
@@ -1675,10 +1701,10 @@ recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
/* Record the external dependencies */
recordMultipleDependencies(depender,
- context.addrs->refs, context.addrs->numrefs,
+ addrs->refs, addrs->numrefs,
behavior);
- free_object_addresses(context.addrs);
+ free_object_addresses(addrs);
}
/*
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 0ea7ccf524..f5061605dd 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -114,6 +114,9 @@ extern void recordDependencyOnExpr(const ObjectAddress *depender,
Node *expr, List *rtable,
DependencyType behavior);
+extern void collectDependenciesFromExpr(ObjectAddresses *addrs,
+ Node *expr, List *rtable);
+
extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
Node *expr, Oid relId,
DependencyType behavior,
--
2.43.0
From 8525f08f823bd5aa6ebb6059bb9f660ce7102da6 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Mon, 13 Oct 2025 15:28:22 +0200
Subject: [PATCH v5 2/2] Prevent SQL functions with BEGIN ATOMIC from depending
on temporary objects
SQL functions with BEGIN ATOMIC bodies are intended to be permanently
definable and should not depend on session-specific temporary objects.
This commit implements dependency validation to enforce this restriction.
Key changes:
- Add filter_temp_objects() to detect temporary objects (tables, views, types,
functions, sequences, domains) and raise descriptive errors
- Integrate temp object filtering into ProcedureCreate() for SQL functions
with BEGIN ATOMIC bodies
- Allow temp-to-temp references: functions in temporary schemas can reference
temporary objects since both have the same session lifecycle
- Skip filtering during bootstrap and pg_upgrade to avoid interfering with
system operations
- Preserve existing behavior for regular SQL functions and parameter defaults
The implementation leverages the existing collectDependenciesFromExpr()
infrastructure to collect dependencies before applying temp object validation,
using a collect-then-filter-then-record pattern for SQL function bodies.
---
src/backend/catalog/dependency.c | 82 +++++++++++++++++++
src/backend/catalog/pg_proc.c | 29 ++++++-
src/include/catalog/dependency.h | 2 +
.../regress/expected/create_function_sql.out | 80 ++++++++++++++++++
src/test/regress/expected/returning.out | 52 ++++++------
src/test/regress/sql/create_function_sql.sql | 71 ++++++++++++++++
src/test/regress/sql/returning.sql | 3 +-
7 files changed, 292 insertions(+), 27 deletions(-)
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index d6142b1750..8665e83ca0 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -44,6 +44,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_largeobject.h"
#include "catalog/pg_namespace.h"
+#include "catalog/namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
@@ -2488,6 +2489,87 @@ eliminate_duplicate_dependencies(ObjectAddresses *addrs)
addrs->numrefs = newrefs;
}
+/*
+ * filter_temp_objects - detect and reject temporary objects in an ObjectAddresses array
+ *
+ * This function checks if any dependencies on temporary objects (objects in
+ * temporary namespaces) exist in the given ObjectAddresses array. If temp objects
+ * are found, it raises an error to prevent them from being used in SQL functions
+ * with BEGIN ATOMIC bodies, as such dependencies would be inappropriate for
+ * permanent function definitions.
+ *
+ * Currently checks for temporary tables, views, types, and functions by examining
+ * their containing namespaces. The function raises an error with a descriptive
+ * message if any temporary object dependency is detected.
+ */
+void filter_temp_objects(ObjectAddresses *addrs)
+{
+ int oldref;
+
+ if (addrs->numrefs <= 0)
+ return; /* nothing to do */
+
+ /* Check all dependencies for temp objects */
+ for (oldref = 0; oldref < addrs->numrefs; oldref++)
+ {
+ ObjectAddress *thisobj = addrs->refs + oldref;
+ bool is_temp = false;
+ char *objname = NULL;
+
+ /* Check if this dependency is on a temporary object */
+ if (thisobj->classId == RelationRelationId)
+ {
+ /* For relations, check if they're in a temp namespace */
+ Oid relnamespace = get_rel_namespace(thisobj->objectId);
+ if (OidIsValid(relnamespace) && isAnyTempNamespace(relnamespace))
+ {
+ is_temp = true;
+ objname = get_rel_name(thisobj->objectId);
+ }
+ }
+ else if (thisobj->classId == TypeRelationId)
+ {
+ /* For types, check if they're in a temp namespace */
+ HeapTuple tup;
+ Form_pg_type typform;
+ Oid typnamespace = InvalidOid;
+
+ tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(thisobj->objectId));
+ if (HeapTupleIsValid(tup))
+ {
+ typform = (Form_pg_type)GETSTRUCT(tup);
+ typnamespace = typform->typnamespace;
+ if (OidIsValid(typnamespace) && isAnyTempNamespace(typnamespace))
+ {
+ is_temp = true;
+ objname = NameStr(typform->typname);
+ }
+ ReleaseSysCache(tup);
+ }
+ }
+ else if (thisobj->classId == ProcedureRelationId)
+ {
+ /* For functions, check if they're in a temp namespace */
+ Oid funcnamespace = get_func_namespace(thisobj->objectId);
+ if (OidIsValid(funcnamespace) && isAnyTempNamespace(funcnamespace))
+ {
+ is_temp = true;
+ objname = get_func_name(thisobj->objectId);
+ }
+ }
+
+ /* Raise error if temp object found */
+ if (is_temp)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use temporary object \"%s\" in SQL function with BEGIN ATOMIC",
+ objname ? objname : "unknown"),
+ errdetail("SQL functions with BEGIN ATOMIC cannot depend on temporary objects.")));
+ }
+ }
+}
+
/*
* qsort comparator for ObjectAddress items
*/
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index b89b9ccda0..67cc9851c7 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -24,6 +24,7 @@
#include "catalog/pg_language.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_proc.h"
+#include "catalog/namespace.h"
#include "catalog/pg_transform.h"
#include "catalog/pg_type.h"
#include "executor/functions.h"
@@ -663,7 +664,33 @@ ProcedureCreate(const char *procedureName,
/* dependency on SQL routine body */
if (languageObjectId == SQLlanguageId && prosqlbody)
- recordDependencyOnExpr(&myself, prosqlbody, NIL, DEPENDENCY_NORMAL);
+ {
+ ObjectAddresses *body_addrs;
+
+ /*
+ * For SQL functions with BEGIN ATOMIC, we use a collect-then-filter-then-record
+ * approach to handle temp object dependencies appropriately.
+ */
+ body_addrs = new_object_addresses();
+ collectDependenciesFromExpr(body_addrs, prosqlbody, NIL);
+
+ /*
+ * Check for temp objects that are referenced in the function body.
+ * For SQL functions with BEGIN ATOMIC bodies, we need to prevent
+ * dependencies on temporary objects since such functions should be
+ * permanently definable and not depend on session-specific temp objects.
+ * This will raise an error if any temp objects are found. If the function
+ * itself is being created in a temporary schema, then it's OK for it to
+ * reference temp objects.
+ */
+ if (!IsBootstrapProcessingMode() && !IsBinaryUpgrade &&
+ !isAnyTempNamespace(procNamespace))
+ filter_temp_objects(body_addrs);
+
+ /* Record the filtered dependencies */
+ record_object_address_dependencies(&myself, body_addrs, DEPENDENCY_NORMAL);
+ free_object_addresses(body_addrs);
+ }
/* dependency on parameter default expressions */
if (parameterDefaults)
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index f5061605dd..551ffe921f 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -123,6 +123,8 @@ extern void recordDependencyOnSingleRelExpr(const ObjectAddress *depender,
DependencyType self_behavior,
bool reverse_self);
+extern void filter_temp_objects(ObjectAddresses *addrs);
+
extern ObjectAddresses *new_object_addresses(void);
extern void add_exact_object_address(const ObjectAddress *object,
diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out
index 73c6730d45..7a9ce26ffa 100644
--- a/src/test/regress/expected/create_function_sql.out
+++ b/src/test/regress/expected/create_function_sql.out
@@ -297,6 +297,86 @@ CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
LANGUAGE SQL
RETURN x[1];
ERROR: SQL function with unquoted function body cannot have polymorphic arguments
+CREATE TEMPORARY TABLE temp_table AS SELECT 1 AS val;
+CREATE TEMPORARY VIEW temp_view AS SELECT 42 AS val;
+CREATE TYPE pg_temp.temp_type AS (x int, y text);
+CREATE TEMPORARY SEQUENCE temp_seq;
+CREATE DOMAIN pg_temp.temp_domain AS int CHECK (VALUE > 0);
+CREATE FUNCTION pg_temp.temp_func() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT 42;
+END;
+-- these should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary tables
+CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM temp_table;
+END;
+ERROR: cannot use temporary object "temp_table" in SQL function with BEGIN ATOMIC
+DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects.
+CREATE FUNCTION functest_temp_dep_subquery() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT (SELECT COUNT(*) FROM temp_table);
+END;
+ERROR: cannot use temporary object "temp_table" in SQL function with BEGIN ATOMIC
+DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects.
+CREATE FUNCTION functest_temp_dep_join() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT t1.val FROM temp_table t1
+ JOIN temp_view t2 ON t1.val = t2.val;
+END;
+ERROR: cannot use temporary object "temp_view" in SQL function with BEGIN ATOMIC
+DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects.
+CREATE FUNCTION functest_temp_indirect_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT * FROM pg_class WHERE oid = 'temp_table'::regclass;
+END;
+ERROR: cannot use temporary object "temp_table" in SQL function with BEGIN ATOMIC
+DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects.
+-- this should work: the function is created in a temp schema
+CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM temp_table;
+END;
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary functions
+CREATE FUNCTION functest_temp_func_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT pg_temp.temp_func();
+END;
+ERROR: cannot use temporary object "temp_func" in SQL function with BEGIN ATOMIC
+DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects.
+-- this should work: temp function calling temp function (both in temp schema)
+CREATE FUNCTION pg_temp.functest_temp_to_temp() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT pg_temp.temp_func();
+END;
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary views
+CREATE FUNCTION functest_temp_view() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM temp_view;
+END;
+ERROR: cannot use temporary object "temp_view" in SQL function with BEGIN ATOMIC
+DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects.
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary types
+CREATE FUNCTION functest_temp_type() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT (ROW(1,'test')::pg_temp.temp_type).x;
+END;
+ERROR: cannot use temporary object "temp_type" in SQL function with BEGIN ATOMIC
+DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects.
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary sequences
+CREATE FUNCTION functest_temp_sequence() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT nextval('temp_seq');
+END;
+ERROR: cannot use temporary object "temp_seq" in SQL function with BEGIN ATOMIC
+DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects.
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary domains
+CREATE FUNCTION functest_temp_domain() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT 5::pg_temp.temp_domain;
+END;
+ERROR: cannot use temporary object "temp_domain" in SQL function with BEGIN ATOMIC
+DETAIL: SQL functions with BEGIN ATOMIC cannot depend on temporary objects.
-- check reporting of parse-analysis errors
CREATE FUNCTION functest_S_xx(x date) RETURNS boolean
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..7f0ad8f11b 100644
--- a/src/test/regress/sql/create_function_sql.sql
+++ b/src/test/regress/sql/create_function_sql.sql
@@ -199,6 +199,77 @@ CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement
LANGUAGE SQL
RETURN x[1];
+CREATE TEMPORARY TABLE temp_table AS SELECT 1 AS val;
+CREATE TEMPORARY VIEW temp_view AS SELECT 42 AS val;
+CREATE TYPE pg_temp.temp_type AS (x int, y text);
+CREATE TEMPORARY SEQUENCE temp_seq;
+CREATE DOMAIN pg_temp.temp_domain AS int CHECK (VALUE > 0);
+CREATE FUNCTION pg_temp.temp_func() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT 42;
+END;
+
+-- these should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary tables
+CREATE FUNCTION functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM temp_table;
+END;
+CREATE FUNCTION functest_temp_dep_subquery() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT (SELECT COUNT(*) FROM temp_table);
+END;
+CREATE FUNCTION functest_temp_dep_join() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT t1.val FROM temp_table t1
+ JOIN temp_view t2 ON t1.val = t2.val;
+END;
+CREATE FUNCTION functest_temp_indirect_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT * FROM pg_class WHERE oid = 'temp_table'::regclass;
+END;
+
+-- this should work: the function is created in a temp schema
+CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM temp_table;
+END;
+
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary functions
+CREATE FUNCTION functest_temp_func_dep() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT pg_temp.temp_func();
+END;
+
+-- this should work: temp function calling temp function (both in temp schema)
+CREATE FUNCTION pg_temp.functest_temp_to_temp() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT pg_temp.temp_func();
+END;
+
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary views
+CREATE FUNCTION functest_temp_view() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT val FROM temp_view;
+END;
+
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary types
+CREATE FUNCTION functest_temp_type() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT (ROW(1,'test')::pg_temp.temp_type).x;
+END;
+
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary sequences
+CREATE FUNCTION functest_temp_sequence() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT nextval('temp_seq');
+END;
+
+-- this should fail: BEGIN ATOMIC SQL-functions cannot depend on temporary domains
+CREATE FUNCTION functest_temp_domain() RETURNS int LANGUAGE sql
+BEGIN ATOMIC;
+ SELECT 5::pg_temp.temp_domain;
+END;
+
-- check reporting of parse-analysis errors
CREATE FUNCTION functest_S_xx(x date) RETURNS boolean
LANGUAGE SQL
diff --git a/src/test/regress/sql/returning.sql b/src/test/regress/sql/returning.sql
index cc99cb53f6..8c2bb836ea 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;
\ No newline at end of file
--
2.43.0
