Thread

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

Bernice Southey <bernice.southey@gmail.com> — 2025-11-22T17:05:56Z

On Sat, Nov 22, 2025 at 4:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The big change is that it makes zero sense to me to apply this
> restriction only to new-style SQL functions.  If it's bad for an
> allegedly non-temporary function to disappear at session exit,
> surely it's not less bad just because it's old-style SQL or not
> SQL-language at all....
> So I changed the code to make the check all the time, and was
> rather depressed by how much that broke:
.....if we've depended on this behavior
> in our own tests, I wonder if there aren't plenty of end users
> depending on it too.  We could be in for a lot of push-back.

Yikes. Can I get in early on the push-back? I think what you're saying
is you want to stop old-style SQL functions from using temp tables
that exist outside of them? IIUC this patch is to raise an error if
one tries to create a new-style function that references a temp table,
because it then disappears. I had this happen to me, and it's how I
learned about this patch. Sincere apologies for the intrusion if I'm
misunderstanding and please ignore what follows if so.

My whole system is completely dependent on functions being able to use
temp tables defined outside of them. This is how I encountered the
disappearing function when I tried making one of them new-style. I
copy batched incoming requests into a reusable multi-use session temp
table. I process these batches in functions that reference my temp
table. Temp tables are also an incredibly useful way to pass state
between SQL functions, and I'm sure I'm not the only person who does
this. I would be gutted to lose this. (Unless I'm being dumb and
there's another better way to do all this.) I could live with losing
it in old-style SQL, if it stays in PL/PgSQL. This was also how I
snuck temp tables into old-style SQL functions without syntax errors -
before this became possible in 18. I see this is back to being a
notice, and I really could be completely misunderstanding, but I'm
alarmed enough to jump in.

Thanks,
Bernice