MIME-Version: 1.0
References: <CACsoHGCWgBJ4BKLtC=Q305WBEM+K2fj3BmzvEVYEGRg896QOYg@mail.gmail.com>
In-Reply-To: <CACsoHGCWgBJ4BKLtC=Q305WBEM+K2fj3BmzvEVYEGRg896QOYg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 8 Nov 2024 09:03:30 -0500
Message-ID: <CANzqJaBFddJae4mOV7fBaKfKzL1B1_KJmkcb7xeJz10obrHnRg@mail.gmail.com>
Subject: Re: Why plpython functions increase transaction counter much more
 then plpgsql functions?
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e8ddaa062667378a"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBFddJae4mOV7fBaKfKzL1B1_KJmkcb7xeJz10obrHnRg%40mail.gmail.com>
Precedence: bulk

--000000000000e8ddaa062667378a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Because the plpython function is executing dynamic SQL?

On Fri, Nov 8, 2024 at 2:59=E2=80=AFAM Micha=C5=82 Albrycht <michalalbrycht=
@gmail.com>
wrote:

> I'm trying to understand why plpython function has much bigger impact on
> transaction counter in Postgres than plpgSQL function. Below is example
> which uses 2 functions:
>
> Version with plpgSQL (each part done in separate transactions one after
> another)
>  - check txid_current
>  - SQL query which calls the `f1_plpgsql` function  which calls the
> `insert_row_to_db` function 100 times
>  - check txid_current
>
>  Then we compare txid_currnent values and difference is 2 which means tha=
t
> whole sql with 100 calls to `f1_plpgsql` and `insert_row_to_db` increased
> transaction counter only by 1.
>
> Here is the code:
> ```
> CREATE TABLE insert_rows_table(
>     i BIGINT
> );
>
> CREATE OR REPLACE FUNCTION insert_row_to_db(i BIGINT)
> RETURNS VOID
> AS $$
> BEGIN
>     INSERT INTO insert_rows_table SELECT i;
> END
> $$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALLEL UNSAFE;
>
>
> CREATE OR REPLACE FUNCTION f1_plpgsql(i BIGINT)
>   RETURNS bigint
> AS $$
> BEGIN
>     PERFORM insert_row_to_db(i);
>     RETURN i;
> END
> $$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALLEL UNSAFE;
>
>
> SELECT txid_current();
> SELECT f1_plpgsql(i::BIGINT) FROM generate_series(1,100) as i;
> SELECT txid_current();
> ```
>
> Example output:
>
> txid_current
> 500
>
> f1_plpgsql
> 1
> 2
> ...
> 99
> 100
>
> txid_current
> 502
>
>
> Here is a code reproduction on db-fiddle:
> https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/15135
>
> Now let's replace `f1_plpgsql` with function written in plpython:
>
> ```
> CREATE OR REPLACE FUNCTION f1_plpython(i BIGINT)
>   RETURNS bigint
> AS $$
>     rows =3D plpy.execute("SELECT insert_row_to_db(" + str(i) + ")")
>     return i
> $$ LANGUAGE plpython3u SECURITY DEFINER VOLATILE PARALLEL UNSAFE;
> ```
>
> I get:
>
> txid_current
> 500
>
> f1_plpgsql
> 1
> 2
> ...
> 99
> 100
>
> txid_current
> 602
>
>
> This proves that the plpython function affects the transaction counter
> much more. Does anyone know why? Is there anything I can do about it?
>
> What's interesting it happens only if the function called by plpyhon make=
s
> changes to DB. When I replace `INSERT INTO insert_rows_table SELECT i;`
> with `SELECT i` both plpython and plpgsql functions behave the same.
>  Regards,
>
> Micha=C5=82 Albrycht
>
>


--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000e8ddaa062667378a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Because the plpython function is executing dynamic SQ=
L?</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr=
">On Fri, Nov 8, 2024 at 2:59=E2=80=AFAM Micha=C5=82 Albrycht &lt;<a href=
=3D"mailto:michalalbrycht@gmail.com">michalalbrycht@gmail.com</a>&gt; wrote=
:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"lt=
r">I&#39;m trying to understand why plpython function has much bigger impac=
t on transaction counter in Postgres than plpgSQL function. Below is exampl=
e which uses 2 functions:<br><br>Version with plpgSQL (each part done in se=
parate transactions one after another)<br>=C2=A0- check txid_current<br>=C2=
=A0- SQL query which calls the `f1_plpgsql` function =C2=A0which calls the =
`insert_row_to_db` function 100 times<br>=C2=A0- check txid_current <br>=C2=
=A0<br>=C2=A0Then we compare txid_currnent values and difference is 2 which=
 means that whole sql with 100 calls to `f1_plpgsql` and `insert_row_to_db`=
 increased transaction counter only by 1.<br>=C2=A0<br>Here is the code:<br=
>```<br>CREATE TABLE insert_rows_table(<br>=C2=A0 =C2=A0 i BIGINT<br>);<br>=
<br>CREATE OR REPLACE FUNCTION insert_row_to_db(i BIGINT)<br>RETURNS VOID<b=
r>AS $$<br>BEGIN<br>=C2=A0 =C2=A0 INSERT INTO insert_rows_table SELECT i;<b=
r>END<br>$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALLEL UNSAFE;<br>=
<br><br>CREATE OR REPLACE FUNCTION f1_plpgsql(i BIGINT)<br>=C2=A0 RETURNS b=
igint<br>AS $$<br>BEGIN<br>=C2=A0 =C2=A0 PERFORM insert_row_to_db(i);<br>=
=C2=A0 =C2=A0 RETURN i;<br>END<br>$$ LANGUAGE plpgsql SECURITY DEFINER VOLA=
TILE PARALLEL UNSAFE;<br><br><br>SELECT txid_current();<br>SELECT f1_plpgsq=
l(i::BIGINT) FROM generate_series(1,100) as i;<br>SELECT txid_current();<br=
>```<br><br>Example output:<br><br>txid_current<br>500<br><br>f1_plpgsql<br=
>1<br>2<br>...<br>99<br>100<br><br>txid_current<br>502<br><br><br>Here is a=
 code reproduction on db-fiddle: <a href=3D"https://www.db-fiddle.com/f/4jy=
oMCicNSZpjMt4jFYoz5/15135" target=3D"_blank">https://www.db-fiddle.com/f/4j=
yoMCicNSZpjMt4jFYoz5/15135</a><br><br>Now let&#39;s replace `f1_plpgsql` wi=
th function written in plpython:<br><br>```<br>CREATE OR REPLACE FUNCTION f=
1_plpython(i BIGINT)<br>=C2=A0 RETURNS bigint<br>AS $$<br>=C2=A0 =C2=A0 row=
s =3D plpy.execute(&quot;SELECT insert_row_to_db(&quot; + str(i) + &quot;)&=
quot;)<br>=C2=A0 =C2=A0 return i<br>$$ LANGUAGE plpython3u SECURITY DEFINER=
 VOLATILE PARALLEL UNSAFE;<br>```<br><br>I get:<br><br>txid_current<br>500<=
br><br>f1_plpgsql<br>1<br>2<br>...<br>99<br>100<br><br>txid_current<br>602<=
br><br><br>This proves that the plpython function affects the transaction c=
ounter much more. Does anyone know why? Is there anything I can do about it=
?<br><br>What&#39;s interesting it happens only if the function called by p=
lpyhon makes changes to DB. When I replace `INSERT INTO insert_rows_table S=
ELECT i;` with `SELECT i` both plpython and plpgsql functions behave the sa=
me.<br>=C2=A0Regards,<div><br></div><div>Micha=C5=82 Albrycht<br><div>=C2=
=A0</div></div></div>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>

--000000000000e8ddaa062667378a--