Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux