public inbox for [email protected]
help / color / mirror / Atom feedAfter 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function
2+ messages / 2 participants
[nested] [flat]
* After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function
@ 2024-03-21 16:06 Jeff Ross <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Jeff Ross @ 2024-03-21 16:06 UTC (permalink / raw)
To: [email protected]
Greetings!
I posted a version of this to the pgsql-general mailing list yesterday.
Since then I've narrowed down the error to this line:
prod_database_connection.commit()
so I believe this is psycopg2 related.
This is on CentOS 7 and I'm using python3-psycopg2.x86_64 2.8.6-1.rhel7
installed from yum.
Jeff
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.
The source code of the function is here:
https://openvistas.net/copy_orders_to_dev.html
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function
@ 2024-03-21 16:30 Adrian Klaver <[email protected]>
parent: Jeff Ross <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Adrian Klaver @ 2024-03-21 16:30 UTC (permalink / raw)
To: Jeff Ross <[email protected]>; [email protected]
On 3/21/24 09:06, Jeff Ross wrote:
> Greetings!
>
> I posted a version of this to the pgsql-general mailing list yesterday.
> Since then I've narrowed down the error to this line:
>
> prod_database_connection.commit()
>
> so I believe this is psycopg2 related.
Does it work outside of plpython3u?
>
> This is on CentOS 7 and I'm using python3-psycopg2.x86_64 2.8.6-1.rhel7
> installed from yum.
psycopg2 is up to 2.9.9. You are missing ~3 years of fixes.
>
> makes me wonder if this error is specific to plpython3?
Hard to say as the other bug report you linked to seemed to refer to
plpythonu.
> Jeff
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-03-21 16:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-21 16:06 After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function Jeff Ross <[email protected]>
2024-03-21 16:30 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox