Re: Is temporary functions feature official/supported? Found some issues with it.

Alexey Bashtanov <bashtanov(at)imap(dot)cc> writes:
> session1:

> l=> begin;
> BEGIN
> l=> create function pg_temp.foo() returns void as $$ begin end; $$
> language plpgsql;
> CREATE FUNCTION
> l=> prepare transaction 'z';
> PREPARE TRANSACTION
> l=> \q

> session2:

> l=> create temp table t();
> ^CCancel request sent
> ... blabla when inserting (0,15) into "pg_namespace_nspname_index" ...
> (sorry, server was running in non-english locale)

Hm. I can reproduce this if I start in a virgin database, but not
otherwise. I think the problem is that the prepared transaction has
created the pg_temp_NN schema for its session, and therefore there
is an uncommitted pg_namespace entry for that schema name, and the
second session is also trying to create that schema (because it has
the same backend ID) so it blocks waiting to see if that index
entry commits.

Another problem is that if the pg_temp_NN schema did already exist
the original session gets hung up on exit, trying to delete the
uncommitted function from its temp schema. (This prevents other
sessions from acquiring the same backend ID, thus masking the
problem from casual inspection.)

None of this is specific to functions, it'd happen for any temp
object.

Maybe we ought to forbid prepared transactions from creating (or
deleting?) any temp objects. I seem to remember that we already
made some restrictions of that sort, but they clearly weren't
sufficient to prevent all problems.

Or we could just say "if it hurts, don't do that". The whole thing
is only a problem if you leave prepared transactions sitting around
for a long time, and that's already a bad idea.

> It isn't the case for without prepared transactions, neither can it be
> achieved with CREATE TEMP TABLE only with no functions involved.

I think your experiments likely didn't account for the different
behavior depending on whether pg_temp_NN exists yet. There's no
reason this would be special to functions.

regards, tom lane