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

ne 21. 9. 2025 v 18:42 odesílatel Jim Jones <jim(dot)jones(at)uni-muenster(dot)de>
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
>
>
>