Search Postgresql Archives

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

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

 



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

[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 Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux