Re: Add notification on BEGIN ATOMIC SQL functions using temp relations

On 9/21/25 17:37, Jim Jones wrote:
> 
> 
> On 9/21/25 16:59, Tom Lane wrote:
>> There's a larger issue here though: a function such as Jim shows
>> is a normal function, probably stored in the public schema, and
>> by default other sessions will be able to call it.  But it will
>> certainly not work as desired for them, since they can't access
>> the creating session's temp tables.  It would likely bollix
>> a concurrent pg_dump too.  I wonder if we'd be better off to
>> forbid creation of such a function altogether.
> 
> That's indeed a much larger problem. Calling it from a session silently
> delivers a "wrong" result --- I was expecting an error.
> 
> == Session 1 ==
> 
> $ /usr/local/postgres-dev/bin/psql postgres
> psql (19devel)
> Type "help" for help.
> 
> postgres=#
> postgres=# CREATE TEMPORARY TABLE tmp AS SELECT 42 AS val;
> SELECT 1
> postgres=# CREATE FUNCTION f()
> RETURNS int LANGUAGE sql
> BEGIN ATOMIC;
> SELECT val FROM tmp;
> END;
> CREATE FUNCTION
> postgres=# SELECT f();
>  f
> ----
>  42
> (1 row)
> 
> == Session 2 (concurrent) ==
> 
> $ /usr/local/postgres-dev/bin/psql postgres
> psql (19devel)
> Type "help" for help.
> 
> postgres=# SELECT f();
>  f
> ---
> 
> (1 row)
> 
> 
> In that light, forbidding creation of functions that depend on temporary
> objects might be the safer and more consistent approach.
> 
As Tom pointed out, pg_dump produces strange output in this case: it
shows a reference to a temporary table that shouldn’t even be visible:

...

--
-- Name: f(); Type: FUNCTION; Schema: public; Owner: jim
--

CREATE FUNCTION public.f() RETURNS integer
    LANGUAGE sql
    BEGIN ATOMIC
 SELECT tmp.val
    FROM pg_temp_3.tmp;
END;

...

This seems to confirm that allowing such functions leads to more than
just user confusion --- it creates broken dump/restore behaviour.

Given that, I agree forbidding functions from referencing temporary
relations is probably the right fix. If there's consensus, I can rework
my PoC in that direction.


Best regards, Jim