Content-Type: multipart/alternative;
 boundary="------------NAA1Z9snOSb4Imy0syYzgY0v"
Message-ID: <98d3484a-0ce1-4c74-85d4-c053652c5b9d@gmail.com>
Date: Wed, 27 Mar 2024 17:35:03 -0600
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: After 10 -> 15 upgrade getting "cannot commit while a portal is
 pinned" on one python function
Content-Language: en-CA
To: pgsql-general@lists.postgresql.org
References: <6960da61-8033-4067-84d1-d9257ce94870@openvistas.net>
 <81c4556d-b599-4512-b6a2-7255f2a39089@aklaver.com>
 <d553c7d6-8af9-4e93-892b-7dc65129b4a6@openvistas.net>
 <37154728-4700-45b7-9261-72400c80b12b@aklaver.com>
 <133886.1710975858@sss.pgh.pa.us>
 <5ef96169-c11d-48de-b322-b7579bf56cc3@openvistas.net>
 <3446817.1711575877@sss.pgh.pa.us>
 <45d75f81-0be1-4990-97a7-5b4fa0e37c65@openvistas.net>
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: <45d75f81-0be1-4990-97a7-5b4fa0e37c65@openvistas.net>
Archived-At: 
 <https://www.postgresql.org/message-id/98d3484a-0ce1-4c74-85d4-c053652c5b9d%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------NAA1Z9snOSb4Imy0syYzgY0v
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


On 3/27/24 17:05, Jeff Ross wrote:
>
> On 3/27/24 15:44, Tom Lane wrote:
>
>> Perhaps "pinned" in the error message means "open"?
>> No, it means "pinned" ... but I see that plpython pins the portal
>> underlying any PLyCursor object it creates.  Most of our PLs do
>> that too, to prevent a portal from disappearing under them (e.g.
>> if you were to try to close the portal directly from SQL rather
>> than via whatever mechanism the PL wants you to use).
>>
>>> I added a cursor.close() as the last line called in that function and it
>>> works again.
>> It looks to me like PLy_cursor_close does pretty much exactly the same
>> cleanup as PLy_cursor_dealloc, including unpinning and closing the
>> underlying portal.  I'm far from a Python expert, but I suspect that
>> the docs you quote intend to say "cursors are disposed of when Python
>> garbage-collects them", and that the reason your code is failing is
>> that there's still a reference to the PLyCursor somewhere after the
>> plpython function exits, perhaps in a Python global variable.
>>
>> 			regards, tom lane
>>
>>
> Thank you for your reply, as always, Tom!
>
> Debugging at this level might well be over my paygrade ;-)
>
> I just happy that the function works again, and that I was able to 
> share a solution to this apparently rare error with the community.
>
> Jeff
>
My read of Tom's reply suggests you still have work to do to find the 
other "reference" holding on to your cursor.
--------------NAA1Z9snOSb4Imy0syYzgY0v
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <br>
    <br>
    <div class="moz-cite-prefix">On 3/27/24 17:05, Jeff Ross wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:45d75f81-0be1-4990-97a7-5b4fa0e37c65@openvistas.net">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <p>On 3/27/24 15:44, Tom Lane wrote:</p>
      <blockquote type="cite"
        cite="mid:3446817.1711575877@sss.pgh.pa.us"><span
        style="white-space: pre-wrap">Perhaps "pinned" in the error message means "open"?</span>
        <pre class="moz-quote-pre" wrap="">No, it means "pinned" ... but I see that plpython pins the portal
underlying any PLyCursor object it creates.  Most of our PLs do
that too, to prevent a portal from disappearing under them (e.g.
if you were to try to close the portal directly from SQL rather
than via whatever mechanism the PL wants you to use).

</pre>
        <blockquote type="cite">
          <pre class="moz-quote-pre" wrap="">I added a cursor.close() as the last line called in that function and it 
works again.
</pre>
        </blockquote>
        <pre class="moz-quote-pre" wrap="">It looks to me like PLy_cursor_close does pretty much exactly the same
cleanup as PLy_cursor_dealloc, including unpinning and closing the
underlying portal.  I'm far from a Python expert, but I suspect that
the docs you quote intend to say "cursors are disposed of when Python
garbage-collects them", and that the reason your code is failing is
that there's still a reference to the PLyCursor somewhere after the
plpython function exits, perhaps in a Python global variable.

			regards, tom lane


</pre>
      </blockquote>
      <p>Thank you for your reply, as always, Tom!</p>
      <p>Debugging at this level might well be over my paygrade ;-)</p>
      <p>I just happy that the function works again, and that I was able
        to share a solution to this apparently rare error with the
        community.</p>
      <p>Jeff <br>
      </p>
    </blockquote>
    My read of Tom's reply suggests you still have work to do to find
    the other "reference" holding on to your cursor.<br>
  </body>
</html>

--------------NAA1Z9snOSb4Imy0syYzgY0v--