Why plpython functions increase transaction counter much more then plpgsql functions?

From: Michał Albrycht <michalalbrycht(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Why plpython functions increase transaction counter much more then plpgsql functions?
Date: 2024-11-08 07:58:47
Message-ID: CACsoHGCWgBJ4BKLtC=Q305WBEM+K2fj3BmzvEVYEGRg896QOYg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Mullineux 2024-11-08 13:39:02 Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16
Previous Message ravi k 2024-11-08 03:07:21 Re: Performance Issue with Hash Partition Query Execution in PostgreSQL 16