After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

Jeff Ross <jross@xxxxxxxxxxxxxx> · Wed, 20 Mar 2024 16:18:58 -0600



    Greetings!
    I built a trigger fired process that copies an "order" from our
      production database to our dev database.  An order, in this case,
      is an initial row from a table and all of the rows in all of the
      tables in that database/schema that are needed to satisfy all of
      the foreign key constraints for the original insert.  Through a
      web page, one of our folks can select a schema and an order id to
      copy.  That information is then inserted into a table.  A trigger
      attached to that table takes care of copying the necessary rows
      using a function that uses both plython3u and psycopg2.  I can
      supply the source code if that will help.

    
    On postgresql 10 using plpython2, this function worked great.
    After migration to 15 (now 15.5) and a switch to plpython3 (no
      code change needed inside the function) logging inside the
      function tells me that everything completes except the exit.
    I then get this error:

      
    NOTICE:  update
        cargotel_common.copy_orders_to_dev set copy_completed = 't',
        copy_completed_timestamp = clock_timestamp() where id = 21

        ERROR:  cannot commit while a portal is pinned

      
    What the heck?

    
    I did find this error inside the source code.  This is from 15.6
      source:
    jross@workstation:~/postgresql-15.6$
        grep -R -C20 "cannot commit while a portal is pinned" *

        src/backend/utils/mmgr/portalmem.c- */

        src/backend/utils/mmgr/portalmem.c-bool

        src/backend/utils/mmgr/portalmem.c-PreCommit_Portals(bool
        isPrepare)

        src/backend/utils/mmgr/portalmem.c-{

        src/backend/utils/mmgr/portalmem.c-    bool        result =
        false;

        src/backend/utils/mmgr/portalmem.c-    HASH_SEQ_STATUS status;

        src/backend/utils/mmgr/portalmem.c-    PortalHashEnt *hentry;

        src/backend/utils/mmgr/portalmem.c-

        src/backend/utils/mmgr/portalmem.c-   
        hash_seq_init(&status, PortalHashTable);

        src/backend/utils/mmgr/portalmem.c-

        src/backend/utils/mmgr/portalmem.c-    while ((hentry =
        (PortalHashEnt *) hash_seq_search(&status)) != NULL)

        src/backend/utils/mmgr/portalmem.c-    {

        src/backend/utils/mmgr/portalmem.c-        Portal        portal
        = hentry->portal;

        src/backend/utils/mmgr/portalmem.c-

        src/backend/utils/mmgr/portalmem.c-        /*

        src/backend/utils/mmgr/portalmem.c-         * There should be no
        pinned portals anymore. Complain if someone

        src/backend/utils/mmgr/portalmem.c-         * leaked one.
        Auto-held portals are allowed; we assume that whoever

        src/backend/utils/mmgr/portalmem.c-         * pinned them is
        managing them.

        src/backend/utils/mmgr/portalmem.c-         */

        src/backend/utils/mmgr/portalmem.c-        if
        (portal->portalPinned && !portal->autoHeld)

        src/backend/utils/mmgr/portalmem.c:            elog(ERROR,
        "cannot commit while a portal is pinned");

        src/backend/utils/mmgr/portalmem.c-

        src/backend/utils/mmgr/portalmem.c-        /*

        src/backend/utils/mmgr/portalmem.c-         * Do not touch
        active portals --- this can only happen in the case of

        src/backend/utils/mmgr/portalmem.c-         * a
        multi-transaction utility command, such as VACUUM, or a commit
        in

        src/backend/utils/mmgr/portalmem.c-         * a procedure.

        src/backend/utils/mmgr/portalmem.c-         *

        src/backend/utils/mmgr/portalmem.c-         * Note however that
        any resource owner attached to such a portal is

        src/backend/utils/mmgr/portalmem.c-         * still going to go
        away, so don't leave a dangling pointer.  Also

        src/backend/utils/mmgr/portalmem.c-         * unregister any
        snapshots held by the portal, mainly to avoid

        src/backend/utils/mmgr/portalmem.c-         * snapshot leak
        warnings from ResourceOwnerRelease().

        src/backend/utils/mmgr/portalmem.c-         */

        src/backend/utils/mmgr/portalmem.c-        if (portal->status
        == PORTAL_ACTIVE)

        src/backend/utils/mmgr/portalmem.c-        {

        src/backend/utils/mmgr/portalmem.c-            if
        (portal->holdSnapshot)

        src/backend/utils/mmgr/portalmem.c-            {

        src/backend/utils/mmgr/portalmem.c-                if
        (portal->resowner)

        src/backend/utils/mmgr/portalmem.c-                   
        UnregisterSnapshotFromOwner(portal->holdSnapshot,

        src/backend/utils/mmgr/portalmem.c-                           
                            portal->resowner);

        src/backend/utils/mmgr/portalmem.c-               
        portal->holdSnapshot = NULL;

        src/backend/utils/mmgr/portalmem.c-            }

      
    Do I have any idea of how to fix this after reading this bit of
      code?  No.
    This error has been reported here before on December 2, 2022 and
      in other places as well.
    https://www.postgresql.org/message-id/1061909348.200334.1669970706749%40mail.yahoo.com
    No responses though to this message though.
    In the code this:
    src/backend/utils/mmgr/portalmem.c-   
            /*

        src/backend/utils/mmgr/portalmem.c-         * There should be no
        pinned portals anymore. Complain if someone

        src/backend/utils/mmgr/portalmem.c-         * leaked one.
        Auto-held portals are allowed; we assume that whoever

        src/backend/utils/mmgr/portalmem.c-         * pinned them is
        managing them.

        src/backend/utils/mmgr/portalmem.c-         */
    makes me wonder if this error is specific to plpython3?
    I can think of a way to maybe workaround this but this seems to
      be something that needs to be handled in a better way than
      slapping on a hacky bandaid.

    
    Thanks for reading and for any suggestions, including
      clue-by-fours :-)

    
    Jeff