Horton

Thread

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

    PG Bug reporting form <noreply@postgresql.org> — 2025-10-04T16:51:33Z

    The following bug has been logged on the website:

Bug reference:      19072
Logged by:          Bernice Southey
Email address:      bernice.southey@gmail.com
PostgreSQL version: 18.0
Operating system:   Linux Mint 22.2
Description:        

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.
When created in pgAdmin, the function survives. It always succeeds even if
the temp table doesn't exist, and if it does, its row are ignored.

It makes sense that new-style SQL language functions don't support temp
tables, but the current non-support is somewhat confusing.
I couldn't find any references to this, my apologies if it's already known.

VERSIONS:
Server:  PostgreSQL 18.0 (Ubuntu 18.0-1.pgdg24.04+3) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
psql:    18.0 (Ubuntu 18.0-1.pgdg24.04+3)
pgAdmin: 9.8
Reproducible on a PostgreSQL 17 server.

STEPS TO REPRODUCE
---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 1: In psql create a temporary table and a new-style SQL function
referencing the table.
---------------------------------------------------------------------------------------------------------------------------------------------------
:~$ psql -d postgresql://postgres:@localhost/test

test=# CREATE TEMP TABLE test(i) AS VALUES(1); CREATE FUNCTION vanish()
RETURNS INT RETURN (SELECT i FROM test); SELECT vanish();
SELECT 1
CREATE FUNCTION
 vanish
--------
      1
(1 row)

---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 2: Start a new session and select the function. The function does not
exist.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# \q
:~$ psql -d postgresql://postgres:@localhost/test

test=# SELECT vanish();
ERROR:  function vanish() does not exist
LINE 1: SELECT vanish();
               ^
HINT:  No function matches the given name and argument types. You might need
to add explicit type casts.

---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 3: In pgAmin create a temp table and a new-style SQL function
referencing the table. (as per step 1)
---------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TEMP TABLE test(i) AS VALUES(1); CREATE FUNCTION vanish() RETURNS INT
RETURN (SELECT i FROM test); SELECT vanish();

---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 4: In psql select the function. It should error because the table
doesn't exist but it returns no rows.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# SELECT vanish();
 vanish
--------
       
(1 row)

---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 5: Create the temp table with rows and call the function. It returns no
rows.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# CREATE TEMP TABLE test AS VALUES(2); SELECT vanish();
SELECT 1
 vanish
--------
       
(1 row)

---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 6: For completeness create the temp table and function with quoted SQL
language syntax.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# CREATE TEMP TABLE test2(i) AS VALUES(3); CREATE FUNCTION vanish2()
RETURNS INT LANGUAGE SQL AS $$SELECT i FROM test2$$; SELECT vanish2();
SELECT 1
CREATE FUNCTION
 vanish2
---------
       3
(1 row)

---------------------------------------------------------------------------------------------------------------------------------------------------
STEP 7: Start a new session and select the function. As expected the
function exists and the table does not.
---------------------------------------------------------------------------------------------------------------------------------------------------
test=# \q
:~$ psql -d postgresql://postgres:@localhost/test

test=# SELECT vanish2();
ERROR:  relation "test2" does not exist
LINE 1: SELECT i FROM test2
                      ^
QUERY:  SELECT i FROM test2
CONTEXT:  SQL function "vanish2" during inlining
test=#

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-04T17:26:00Z

    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?

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

  3. 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