Thread: BUG #17837: The potential risks associated with executing "commit" in a procedure.

The following bug has been logged on the website:

Bug reference:      17837
Logged by:          Andre Lin
Email address:      857348270@qq.com
PostgreSQL version: 12.14
Operating system:   Linux x86_64 GNU/Linux
Description:

Recently, I have noticed that executing "commit" in a procedure calls
PreCommit_Portals in the kernel, which sets the resowner of the top level
portal to null. However, subsequent statements still use this portal, and
its resowner remains null. Is there any risk associated with this or could
it lead to unexpected behavior? If this is expected, how should I interpret
(or ... understand) portal->resowner? I would greatly appreciate your
guidance on this matter.

For example:

create or replace procedure p () 
as
$$
    declare
    vsql varchar := 'aa';
    begin
        for i in 1..10
        loop
            raise notice '%',i;
        end loop;
    commit;
        for i in 1..10
        loop
        raise notice '%',i;
        end loop;
    commit;
end;
$$
language plpgsql;

PG Bug reporting form <noreply@postgresql.org> writes:
> Recently, I have noticed that executing "commit" in a procedure calls
> PreCommit_Portals in the kernel, which sets the resowner of the top level
> portal to null. However, subsequent statements still use this portal, and
> its resowner remains null. Is there any risk associated with this or could
> it lead to unexpected behavior?

I do not see any reason to believe there's a bug here.  After the
COMMIT, CurrentResourceOwner won't be pointing at the portal's
resowner anymore, but at a resowner belonging to the newly-started
transaction (cf AtStart_ResourceOwner).  The portal's resowner would
only have been used for resources that went away at commit, so it's
not needed anymore.  Nor is there any code that would try to
re-install that resowner as active.

            regards, tom lane