Thread

Re: BUG #19072: New-style SQL language function referencing a temp table behaves oddly

Bernice Southey <bernice.southey@gmail.com> — 2025-10-04T20:02:15Z

On Sat, Oct 4, 2025 at 6:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> PG Bug reporting form <noreply@postgresql.org> writes:
> > When created in psql, a new-style SQL language function referencing a temp
> > table disappears with the session, as if it were a temporary function.
>
> Yeah, this is expected, since the parser will create a pg_depend
> linkage from the function to the temp table.
>
> > When created in pgAdmin, the function survives.
>
> Really?  I don't use pgAdmin, but I don't see how it could affect the
> object dependency rules.  Perhaps it's not closing the originating
> session when you think it is?

Yes you're right. I was thinking of how temp tables worked, and didn't
realise the function was visible across sessions while the creating
session was still open.
>
>
> There is a nearby thread proposing forbidding new-style functions
> from having dependencies on temp objects [1].  Curious to know if
> you think that'd be a good answer.
>
>                         regards, tom lane
>
> [1] https://www.postgresql.org/message-id/flat/19cf6ae1-04cd-422c-a760-d7e75fe6cba9%40uni-muenster.de

Yes, I agree with the latest proposal of ERROR. It certainly would've
saved me much head-scratching. The other BEGIN ATOMIC errors I've
encountered have been very clear and ironically increased my
puzzlement here.
This is a strange hybrid of a temp and permanent function as evidenced
by my confusion of it spanning sessions but with a missing table and
then vanishing. It's just too odd to be useful.

There doesn't seem to be a consensus on what this style of function is
called, making it difficult to find info on them. I only realised they
existed recently when I stumbled across your reference to "new-style"
here [1] and wanted to know what I was missing out on.

Best regards, Bernice

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0dca5d68d