Is there a way to execute queries in plpython without increasing txid after each query?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Is there a way to execute queries in plpython without increasing txid after each query?

Marcin Barczynski
I noticed that each query in plpython3 is executed in its own subtransaction (https://github.com/postgres/postgres/blob/257ee34e733aba0fc371177f1a7fd97f3cb98d74/src/pl/plpython/plpy_spi.c#L226) which increases txid.
For example:


CREATE EXTENSION IF NOT EXISTS plpython3u;

DROP TABLE IF EXISTS demo;
CREATE TABLE demo(key BIGINT, val BIGINT);

CREATE OR REPLACE FUNCTION py_insert(k BIGINT)
    RETURNS VOID AS $$
    plans_by_key = SD.setdefault("plans_by_key", {})
    if not plans_by_key.get(k):
        plans_by_key[k] = plpy.prepare(f"INSERT INTO demo VALUES ({k}, {k})")
    for _ in range(10000):
        plans_by_key[k].execute()
$$ LANGUAGE plpython3u;

SELECT txid_current();
SELECT * FROM py_insert(3);
SELECT txid_current();


CREATE EXTENSION
DROP TABLE
CREATE TABLE
CREATE FUNCTION
 txid_current
--------------
        42541
(1 row)

 py_insert
-----------
 
(1 row)

 txid_current
--------------
        52543
(1 row)


Similar loop in plpgsql doesn't create a subtransaction per each query unless it's in BEGIN ... EXCEPTION ... END block.
High transaction rate is undesirable, because it means more often emergency vacuums to prevent transaction id wraparound.
Is there a way to work around the problem?

--
Best regards,
Marcin Barczyński