Re: Add notification on BEGIN ATOMIC SQL functions using temp relations - Mailing list pgsql-hackers

Hi Tom,

Thanks for the review and thorough feedback.

On 10/8/25 22:35, Tom Lane wrote:
> I think the right way to make this work is to look through the
> array of ObjectAddresses that ProcedureCreate builds to store
> into pg_depend, because that is by definition the authoritative
> info about what the function is dependent on.  There's some
> refactoring pain to be endured to make that happen though.
> Most of the interesting-for-this-purpose dependencies are
> found by recordDependencyOnExpr, which summarily writes them
> out before we'd get a chance to look at them.  I think what we
> want to do is refactor that so that we have a function along
> the lines of "add all the dependencies of this expression to
> a caller-supplied ObjectAddresses struct".  Then merge the
> dependencies found by that function into the list of special
> dependencies that ProcedureCreate has hard-wired logic for, then
> de-duplicate that list, then (if not a temp function) scan the
> list for dependencies on temp objects, and finally (if no error)
> write it out to pg_depend using recordMultipleDependencies.
> This would provide more effective de-duplication of pg_depend
> entries than what ProcedureCreate is doing today, and it would
> give us full coverage not just partial.


PFA a first attempt to address your points.

0001 introduces collectDependenciesFromExpr(), which collects object
dependencies into a caller-supplied ObjectAddresses structure without
recording them immediately. recordDependencyOnExpr() now uses this
helper internally before performing the actual recording.

0002 builds on this infrastructure to collect dependencies before
applying temporary-object validation. It adopts a
"collect–then–filter–then–record" pattern for SQL function bodies in
ProcedureCreate(). After collecting, it calls filter_temp_objects() to
detect any references to temporary objects and raises an ERROR if found,
unless the function itself is being created in a temporary schema.

> 
> I realize that you probably cribbed this logic from
> isQueryUsingTempRelation, but that is looking pretty sad too.
> As a concrete example of what I'm talking about:
> 
> regression=# create temp table mytemp (f1 int);
> CREATE TABLE
> regression=# create view vfoo as select * from pg_class where oid = 'mytemp'::regclass;
> CREATE VIEW
> regression=# \c -
> You are now connected to database "regression" as user "postgres".
> regression=# \d vfoo
> Did not find any relation named "vfoo".


Here a few tests:

postgres=# CREATE TEMPORARY TABLE temp_table AS SELECT 1 AS val;
SELECT 1
postgres=# CREATE TEMPORARY VIEW temp_view AS SELECT 42 AS val;
CREATE VIEW

== temp table dependency ==
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.

== regclass cast ==

postgres=# CREATE FUNCTION functest_temp_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.

== subquery ==
postgres=# 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.

== function created in pg_temp ==
-- this should work: the function is created in a temp schema
postgres=#  CREATE FUNCTION pg_temp.functest_temp_dep() RETURNS int
LANGUAGE sql
BEGIN ATOMIC;
  SELECT val FROM temp_table;
END;
CREATE FUNCTION

== temp view ==
postgres=# 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.

Thoughts?

Best regards, Jim