Because the plpython function is executing dynamic SQL?
On Fri, Nov 8, 2024 at 2:59 AM Michał Albrycht <michalalbrycht@xxxxxxxxx> 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 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 = 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ł Albrycht
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
