pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19072: New-style SQL language function referencing a temp table behaves oddly
3+ messages / 3 participants
[nested] [flat]
* BUG #19072: New-style SQL language function referencing a temp table behaves oddly
@ 2025-10-04 16:51 PG Bug reporting form <noreply@postgresql.org>
2025-10-04 17:26 ` Re: BUG #19072: New-style SQL language function referencing a temp table behaves oddly Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2025-10-04 16:51 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: bernice.southey@gmail.com
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=#
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19072: New-style SQL language function referencing a temp table behaves oddly
2025-10-04 16:51 BUG #19072: New-style SQL language function referencing a temp table behaves oddly PG Bug reporting form <noreply@postgresql.org>
@ 2025-10-04 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-04 20:02 ` Re: BUG #19072: New-style SQL language function referencing a temp table behaves oddly Bernice Southey <bernice.southey@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2025-10-04 17:26 UTC (permalink / raw)
To: bernice.southey@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19072: New-style SQL language function referencing a temp table behaves oddly
2025-10-04 16:51 BUG #19072: New-style SQL language function referencing a temp table behaves oddly PG Bug reporting form <noreply@postgresql.org>
2025-10-04 17:26 ` Re: BUG #19072: New-style SQL language function referencing a temp table behaves oddly Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-10-04 20:02 ` Bernice Southey <bernice.southey@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Bernice Southey @ 2025-10-04 20:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-10-04 20:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-04 16:51 BUG #19072: New-style SQL language function referencing a temp table behaves oddly PG Bug reporting form <noreply@postgresql.org>
2025-10-04 17:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-10-04 20:02 ` Bernice Southey <bernice.southey@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox