From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Micha=C5=82_Albrycht?= <michalalbrycht@gmail.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: Why plpython functions increase transaction counter much more then plpgsql functions?
In-reply-to: <CACsoHGCWgBJ4BKLtC=Q305WBEM+K2fj3BmzvEVYEGRg896QOYg@mail.gmail.com>
References: <CACsoHGCWgBJ4BKLtC=Q305WBEM+K2fj3BmzvEVYEGRg896QOYg@mail.gmail.com>
Comments: In-reply-to =?UTF-8?Q?Micha=C5=82_Albrycht?= <michalalbrycht@gmail.com>
	message dated "Fri, 08 Nov 2024 08:58:47 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3628358.1731076788.1@sss.pgh.pa.us>
Date: Fri, 08 Nov 2024 09:39:48 -0500
Message-ID: <3628359.1731076788@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3628359.1731076788%40sss.pgh.pa.us>
Precedence: bulk

=?UTF-8?Q?Micha=C5=82_Albrycht?= <michalalbrycht@gmail.com> writes:
> This proves that the plpython function affects the transaction counter much
> more. Does anyone know why?

A quick look at PLy_spi_execute_query shows that it runs each command
in a subtransaction.  It pretty much has to, because the coding rules
for a Python method don't permit it to just longjmp out of the Python
interpreter, so it has to set up a subtransaction so it can catch any
error.  In this example, each subtransaction will consume an XID.

The plpgsql example is different because it doesn't trap errors,
hence no subtransaction needed, and all the rows will get inserted
under the XID of the outer command's main transaction.  If you were
to wrap the insert_row_to_db call in BEGIN ... EXCEPTION then it'd
consume the same number of XIDs as plpython, for the same reason.

> Is there anything I can do about it?

Batch the DB updates, perhaps?

> What's interesting it happens only if the function called by plpyhon makes
> changes to DB.

Totally unsurprising.  XIDs are acquired only when the current
transaction or subtransaction first needs to change the DB.

			regards, tom lane