On 04/11/2025 21:41, Tom Lane wrote: > 0001 is mostly what I had in mind, except that I do not think > collectDependenciesFromExpr should perform > eliminate_duplicate_dependencies; it should be explicitly documented > that the caller should do that before recording the dependencies. > This approach will avoid duplicate work when collecting dependencies > from multiple sources.
Done. eliminate_duplicate_dependencies() has been removed from collectDependenciesFromExpr(). The function's comment now explicitly documents that callers are responsible for calling eliminate_duplicate_dependencies() before recording. In recordDependencyOnExpr(), eliminate_duplicate_dependencies() is now called right before recordMultipleDependencies(). > It seems like a lot of the changes in recordDependencyOnSingleRelExpr, > maybe all of them, were unnecessary --- why'd you find it useful to > add the "addrs" variable instead of continuing to use context.addrs? Yes, you're right. These changes were unnecessary leftovers from an earlier draft. I've reverted recordDependencyOnSingleRelExpr() to use context.addrs. > I'm not terribly happy with 0002. In particular, I don't like > filter_temp_objects having an explicit list of which object types > might be temp. But I don't think we need to do that, because > the objectaddress.c infrastructure already knows all about > which objects belong to schemas. I think you can just use > get_object_namespace(), and if it returns something that satisfies > OidIsValid(namespace) && isAnyTempNamespace(namespace), > then complain. (Also, use getObjectDescription() to build a > description of what you're complaining about, rather than > hard-coding that knowledge.) Done. filter_temp_objects() now uses get_object_namespace() from the objectaddress.c infrastructure to identify which objects belong to schemas, then checks if those schemas are temporary. The error message now uses getObjectDescription() to provide clear descriptions of the problematic objects. > The bigger issue is that it's not only the prosqlbody that > we ought to be applying this check to. For example, we should > similarly refuse cases where a temporary type is used as an > argument or result type. So I think the way that ProcedureCreate > needs to work is to collect up *all* of the dependencies that > it is creating into an ObjectAddresses list, and then de-dup > that (once), check it for temp references, and finally record it. The implementation now collects all function dependencies into a single ObjectAddresses structure and then checks for temporary objects. If no temporary object was found, it records the dependencies once. For SQL functions with BEGIN ATOMIC bodies, filter_temp_objects() is called on the complete set of dependencies before recording, ensuring that temporary objects are rejected whether they appear in the function signature or body. The dependencies are then deduplicated and recorded once via record_object_address_dependencies(). create_function_sql.sql now contain tests for temporary objects in function parameters, DEFAULT parameters, and RETURN data types. PFA v6 with these changes. Thanks for the thorough review. Best, Jim
From 3af74aa118e81dc5b9a84d670fb973026d532227 Mon Sep 17 00:00:00 2001 From: Jim Jones <[email protected]> Date: Wed, 5 Nov 2025 09:55:31 +0100 Subject: [PATCH v6 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 caller is responsible for calling eliminate_duplicate_dependencies() on the collected dependencies before recording them. This design avoids redundant deduplication work when collecting dependencies from multiple sources. 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 | 54 +++++++++++++++++++++++++------- src/include/catalog/dependency.h | 3 ++ 2 files changed, 46 insertions(+), 11 deletions(-) diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index 7dded634eb..bd5d705936 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -1554,25 +1554,57 @@ recordDependencyOnExpr(const ObjectAddress *depender, Node *expr, List *rtable, DependencyType behavior) { - find_expr_references_context context; + ObjectAddresses *addrs; - context.addrs = new_object_addresses(); + addrs = new_object_addresses(); - /* Set up interpretation for Vars at varlevelsup = 0 */ - context.rtables = list_make1(rtable); + /* Collect all dependencies from the expression */ + collectDependenciesFromExpr(addrs, expr, rtable); - /* Scan the expression tree for referenceable objects */ - find_expr_references_walker(expr, &context); - - /* Remove any duplicates */ - eliminate_duplicate_dependencies(context.addrs); + /* Remove duplicates */ + eliminate_duplicate_dependencies(addrs); /* And record 'em */ recordMultipleDependencies(depender, - context.addrs->refs, context.addrs->numrefs, + addrs->refs, addrs->numrefs, behavior); - free_object_addresses(context.addrs); + 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. + * + * Note: the caller is responsible for calling + * eliminate_duplicate_dependencies() on the ObjectAddresses structure + * before recording the dependencies, if duplicate elimination is + * desired. This design allows collecting dependencies from multiple + * sources without redundant deduplication work. + */ +void +collectDependenciesFromExpr(ObjectAddresses *addrs, + Node *expr, List *rtable) +{ + find_expr_references_context context; + + context.addrs = addrs; + + /* Set up interpretation for Vars at varlevelsup = 0 */ + context.rtables = list_make1(rtable); + + /* Scan the expression tree for referenceable objects */ + find_expr_references_walker(expr, &context); } /* 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 6d3cadd472fed5157d46b4ccd72f3d4bb4ecbf00 Mon Sep 17 00:00:00 2001 From: Jim Jones <[email protected]> Date: Wed, 5 Nov 2025 13:43:33 +0100 Subject: [PATCH v6 2/2] Disallow temp objects in SQL BEGIN ATOMIC functions SQL functions with BEGIN ATOMIC bodies are intended to be permanently definable and should not depend on session-specific temporary objects. This implements dependency validation to enforce this restriction. Key changes: - Add filter_temp_objects() to detect temporary objects in any schema using get_object_namespace() from the objectaddress.c infrastructure - Check ALL function dependencies (return types, parameter types, default parameter values, and function body) for temporary object references - Integrate temp object filtering into ProcedureCreate() for SQL functions with BEGIN ATOMIC bodies using a collect-then-filter-then-record pattern - Allow temp-to-temp references: functions in temporary schemas can reference temporary objects since both have the same session lifecycle The implementation collects all function dependencies (including parameter types, return types, default expressions, and SQL body) into a single ObjectAddresses structure, filters out temporary object references, then records the dependencies once. This ensures that not only the function body but also the function signature cannot reference temporary objects. --- src/backend/catalog/dependency.c | 43 +++++++ src/backend/catalog/pg_proc.c | 45 ++++++- src/include/catalog/dependency.h | 2 + .../regress/expected/create_function_sql.out | 118 ++++++++++++++++++ src/test/regress/expected/returning.out | 52 ++++---- src/test/regress/sql/create_function_sql.sql | 105 ++++++++++++++++ src/test/regress/sql/returning.sql | 3 +- 7 files changed, 336 insertions(+), 32 deletions(-) diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index bd5d705936..3162bc0ad7 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" @@ -2494,6 +2495,48 @@ eliminate_duplicate_dependencies(ObjectAddresses *addrs) addrs->numrefs = newrefs; } +/* + * filter_temp_objects - reject temporary object references + * + * Scan an ObjectAddresses array for references to temporary objects + * (objects in temporary namespaces) and raise an error if any are found. + * This is used to prevent SQL functions with BEGIN ATOMIC bodies from + * depending on temporary objects, as such dependencies would be + * inappropriate for permanent function definitions. + * + * Uses get_object_namespace() to identify which objects belong to + * schemas, then checks if those schemas are temporary. + */ +void +filter_temp_objects(ObjectAddresses *addrs) +{ + int i; + + for (i = 0; i < addrs->numrefs; i++) + { + ObjectAddress *thisobj = addrs->refs + i; + Oid objnamespace; + + /* + * Use get_object_namespace() to see if this object belongs to a + * schema. If not, we can skip it. + */ + objnamespace = get_object_namespace(thisobj); + + /* + * If the object is in a temporary namespace, complain. + */ + if (OidIsValid(objnamespace) && isAnyTempNamespace(objnamespace)) + { + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use temporary %s in SQL function with BEGIN ATOMIC", + getObjectDescription(thisobj, false)), + 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..9b23201e83 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" @@ -658,15 +659,47 @@ ProcedureCreate(const char *procedureName, add_exact_object_address(&referenced, addrs); } - record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL); - free_object_addresses(addrs); - /* dependency on SQL routine body */ if (languageObjectId == SQLlanguageId && prosqlbody) - recordDependencyOnExpr(&myself, prosqlbody, NIL, DEPENDENCY_NORMAL); + { + collectDependenciesFromExpr(addrs, prosqlbody, NIL); + + /* Also collect dependencies from parameter defaults */ + if (parameterDefaults) + collectDependenciesFromExpr(addrs, (Node *) parameterDefaults, NIL); - /* dependency on parameter default expressions */ - if (parameterDefaults) + /* + * Check for temp objects before recording dependencies, but only + * for SQL functions with BEGIN ATOMIC bodies. We check for temp + * objects here so that the check applies to all dependencies, not + * just those from the SQL body. For example, a function with a + * temp table type as an argument or return type should be rejected, + * not just one that references a temp table in its body. + * + * We skip the check if the function is being created in a temp + * schema (in which case it's fine for it to depend on temp objects), + * or if we're in bootstrap or binary upgrade mode (where we need to + * restore whatever was in the dump without complaints). + */ + if (!IsBootstrapProcessingMode() && !IsBinaryUpgrade && + !isAnyTempNamespace(procNamespace)) + filter_temp_objects(addrs); + } + + /* + * Now record all dependencies at once. This will also remove any + * duplicates. + */ + record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL); + free_object_addresses(addrs); + + /* + * Dependency on parameter default expressions, but only if we didn't + * already handle them above. For SQL functions with BEGIN ATOMIC bodies, + * parameter defaults are included in the temp object check and recorded + * above. + */ + if (parameterDefaults && (languageObjectId != SQLlanguageId || !prosqlbody)) recordDependencyOnExpr(&myself, (Node *) parameterDefaults, NIL, DEPENDENCY_NORMAL); 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..ef05243f92 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -297,6 +297,124 @@ 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 table 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 table 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 table temp_table 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 table 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 +-- (wrapped in DO block because error message includes session-specific temp schema name) +DO $$ +BEGIN + EXECUTE 'CREATE FUNCTION functest_temp_func_dep() RETURNS int LANGUAGE sql BEGIN ATOMIC SELECT pg_temp.temp_func(); END'; +EXCEPTION + WHEN feature_not_supported THEN + RAISE NOTICE 'caught expected error'; +END $$; +NOTICE: caught expected error +-- 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 view 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 column x of composite type 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 sequence 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 type temp_domain 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 contain parameters with temporary types +CREATE FUNCTION functest_temp_parameter(foo temp_type) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; +ERROR: cannot use temporary type 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 return temporary types +CREATE FUNCTION functest_temp_return() RETURNS temp_type LANGUAGE sql +BEGIN ATOMIC; + SELECT NULL; +END; +ERROR: cannot use temporary type 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 contain array of temporary type as parameter +CREATE FUNCTION functest_temp_array_param(foo temp_type[]) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; +ERROR: cannot use temporary type 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 contain default parameter value referencing temporary object +CREATE FUNCTION functest_temp_default(seq_val int DEFAULT nextval('temp_seq')) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT seq_val; +END; +ERROR: cannot use temporary sequence 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 return SETOF temporary type +CREATE FUNCTION functest_temp_setof_return() RETURNS SETOF temp_type LANGUAGE sql +BEGIN ATOMIC; + SELECT NULL; +END; +ERROR: cannot use temporary type temp_type 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..bf2533b7fc 100644 --- a/src/test/regress/sql/create_function_sql.sql +++ b/src/test/regress/sql/create_function_sql.sql @@ -199,6 +199,111 @@ 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 +-- (wrapped in DO block because error message includes session-specific temp schema name) +DO $$ +BEGIN + EXECUTE 'CREATE FUNCTION functest_temp_func_dep() RETURNS int LANGUAGE sql BEGIN ATOMIC SELECT pg_temp.temp_func(); END'; +EXCEPTION + WHEN feature_not_supported THEN + RAISE NOTICE 'caught expected error'; +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; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot contain parameters with temporary types +CREATE FUNCTION functest_temp_parameter(foo temp_type) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot return temporary types +CREATE FUNCTION functest_temp_return() RETURNS temp_type LANGUAGE sql +BEGIN ATOMIC; + SELECT NULL; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot contain array of temporary type as parameter +CREATE FUNCTION functest_temp_array_param(foo temp_type[]) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT 42; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot contain default parameter value referencing temporary object +CREATE FUNCTION functest_temp_default(seq_val int DEFAULT nextval('temp_seq')) RETURNS int LANGUAGE sql +BEGIN ATOMIC; + SELECT seq_val; +END; + +-- this should fail: BEGIN ATOMIC SQL-functions cannot return SETOF temporary type +CREATE FUNCTION functest_temp_setof_return() RETURNS SETOF temp_type LANGUAGE sql +BEGIN ATOMIC; + SELECT NULL; +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
