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:
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.
only when the function is not created in pg_temp schema - I think
