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

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.

Best regards, Jim