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

Поиск
Список
Период
Сортировка
От Marcin Barczynski
Тема Is there a way to execute queries in plpython without increasing txid after each query?
Дата
Msg-id CAOhG4wfup4jDZqBO-wp8RbyPQn60cEDZUjrvZGqDBb6p3GkS1Q@mail.gmail.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Can't seem to mix an inner and outer join in a query and get it to work right.
Следующее
От: Donzell White
Дата:
Сообщение: Logical Replication Issue