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