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

From: Marcin Barczynski <mbarczynski(at)starfishstorage(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Is there a way to execute queries in plpython without increasing txid after each query?
Date: 2020-06-30 09:55:05
Message-ID: CAOhG4wfup4jDZqBO-wp8RbyPQn60cEDZUjrvZGqDBb6p3GkS1Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Donzell White 2020-06-30 11:55:32 Logical Replication Issue
Previous Message David G. Johnston 2020-06-30 01:10:24 Re: Can't seem to mix an inner and outer join in a query and get it to work right.