ne 21. 9. 2025 v 18:42 odesílatel Jim Jones <[email protected]> napsal:
> > > 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. > only when the function is not created in pg_temp schema - I think Pavel > > Best regards, Jim > > >
