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