MIME-Version: 1.0
From: =?UTF-8?Q?Micha=C5=82_Albrycht?= <michalalbrycht@gmail.com>
Date: Fri, 8 Nov 2024 08:58:47 +0100
Message-ID: <CACsoHGCWgBJ4BKLtC=Q305WBEM+K2fj3BmzvEVYEGRg896QOYg@mail.gmail.com>
Subject: Why plpython functions increase transaction counter much more then
 plpgsql functions?
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000089bc3e0626621ffe"
Archived-At: <https://www.postgresql.org/message-id/CACsoHGCWgBJ4BKLtC%3DQ305WBEM%2BK2fj3BmzvEVYEGRg896QOYg%40mail.gmail.com>
Precedence: bulk

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

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 that
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 makes
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

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

<div dir=3D"ltr">I&#39;m trying to understand why plpython function has muc=
h bigger impact on transaction counter in Postgres than plpgSQL function. B=
elow is example which uses 2 functions:<br><br>Version with plpgSQL (each p=
art done in separate transactions one after another)<br>=C2=A0- check txid_=
current<br>=C2=A0- SQL query which calls the `f1_plpgsql` function =C2=A0wh=
ich calls the `insert_row_to_db` function 100 times<br>=C2=A0- check txid_c=
urrent <br>=C2=A0<br>=C2=A0Then we compare txid_currnent values and differe=
nce is 2 which means that whole sql with 100 calls to `f1_plpgsql` and `ins=
ert_row_to_db` increased transaction counter only by 1.<br>=C2=A0<br>Here i=
s the code:<br>```<br>CREATE TABLE insert_rows_table(<br>=C2=A0 =C2=A0 i BI=
GINT<br>);<br><br>CREATE OR REPLACE FUNCTION insert_row_to_db(i BIGINT)<br>=
RETURNS VOID<br>AS $$<br>BEGIN<br>=C2=A0 =C2=A0 INSERT INTO insert_rows_tab=
le SELECT i;<br>END<br>$$ LANGUAGE plpgsql SECURITY DEFINER VOLATILE PARALL=
EL UNSAFE;<br><br><br>CREATE OR REPLACE FUNCTION f1_plpgsql(i BIGINT)<br>=
=C2=A0 RETURNS bigint<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 SECURIT=
Y DEFINER VOLATILE PARALLEL UNSAFE;<br><br><br>SELECT txid_current();<br>SE=
LECT f1_plpgsql(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-fi=
ddle.com/f/4jyoMCicNSZpjMt4jFYoz5/15135">https://www.db-fiddle.com/f/4jyoMC=
icNSZpjMt4jFYoz5/15135</a><br><br>Now let&#39;s replace `f1_plpgsql` with f=
unction written in plpython:<br><br>```<br>CREATE OR REPLACE FUNCTION f1_pl=
python(i BIGINT)<br>=C2=A0 RETURNS bigint<br>AS $$<br>=C2=A0 =C2=A0 rows =
=3D plpy.execute(&quot;SELECT insert_row_to_db(&quot; + str(i) + &quot;)&qu=
ot;)<br>=C2=A0 =C2=A0 return i<br>$$ LANGUAGE plpython3u SECURITY DEFINER V=
OLATILE 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 cou=
nter 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 plp=
yhon makes changes to DB. When I replace `INSERT INTO insert_rows_table SEL=
ECT i;` with `SELECT i` both plpython and plpgsql functions behave the same=
.<br>=C2=A0Regards,<div><br></div><div>Micha=C5=82 Albrycht<br><div>=C2=A0<=
/div></div></div>

--00000000000089bc3e0626621ffe--