From: | Ivan Kush <ivan(dot)kush(at)tantorlabs(dot)com> |
---|---|
To: | "Andrey M(dot) Borodin" <x4mmm(at)yandex-team(dot)ru> |
Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Autonomous transactions 2023, WIP |
Date: | 2023-12-24 11:32:48 |
Message-ID: | a8399bf1-1348-4ea2-b028-fd7b37944b53@tantorlabs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> Is anyone else using backgroud connections?
Don't know at the current time. Maybe EnterpriseDB uses bgworkers as
Peter Eisentraut works there currently (LinkedIn says =)) And in 2016
he has proposed a patch with autonomous transactions with bgworkers.
https://www.postgresql.org/message-id/flat/659a2fce-b6ee-06de-05c0-c8ed6a01979e%402ndquadrant.com
> Which syntax is used by other DBMS'?
Main databases use:
1) PRAGMA in block declaration: Oracle, EnterpriseDB, this patch
2) AUTONOMOUS keyword near BEGIN keyword: PostgresPro, SAP HANA
3) AUTONOMOUS keyword in function declaration: IBM DB2
4) сompletely new syntax of autonomous block: Firebird
1 and 2 cases are the same, autonomicity by sub-blocks. Difference only
in syntax, added to existing block definition
3 case autonomicity only by function (as keyword in function declaration)
4 case should we add completely new block definitions?
# Oracle
Uses PRAGMA AUTONOMOUS_TRANSACTION
```
CREATE FUNCTION foo() RETURNS void AS $$
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO tbl VALUES (1);
END;
$$ LANGUAGE plpgsql;
```
https://docs.oracle.com/cd/B13789_01/appdev.101/b10807/13_elems002.htm
# EnterpriseDB
Uses PRAGMA AUTONOMOUS_TRANSACTION; as in Oracle
```
CREATE FUNCTION foo() RETURNS void AS $$
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO tbl VALUES (1);
END;
$$ LANGUAGE plpgsql;
```
# PostgresPro
* plpgsql
Block construction in PL/pgSQL is extended by the optional autonomous
keyword.
```
CREATE FUNCTION foo() RETURNS void AS $$
BEGIN AUTONOMOUS
INSERT INTO tbl VALUES (1);
BEGIN AUTONOMOUS
....
END;
END;
$$ LANGUAGE plpgsql;
```
https://postgrespro.com/docs/enterprise/15/ch16s04
* plpython
autonomous method that can be used in the WITH clause to start an
autonomous transaction
```
with plpy.autonomous() as a:
a.execute("INSERT INTO tbl VALUES (1);")
```
https://postgrespro.com/docs/enterprise/15/ch16s05
# IBM DB2
AUTONOMOUS keyword in function declaration
```
CREATE PROCEDURE foo()
AUTONOMOUS
LANGUAGE SQL
BEGIN
BEGIN AUTONOMOUS TRANSACTION;
INSERT INTO tbl VALUES (1);
END:
END;
$$ LANGUAGE plpgsql;
```
https://github.com/IBM/db2-samples/blob/master/admin_scripts/autonomous_transaction.db2
https://subscription.packtpub.com/book/programming/9781849683968/1/ch01lvl1sec09/using-autonomous-transactions
# SAP HANA
Also AUTONOMOUS_TRANSACTION option for blocks
```
CREATE PROCEDURE foo() LANGUAGE SQLSCRIPT AS
BEGIN
BEGIN AUTONOMOUS TRANSACTION
INSERT INTO tbl VALUES (1);
END;
END;
```
# Firebird
Completely new block definition `IN AUTONOMOUS TRANSACTION DO`
```
CREATE PROCEDURE foo() AS
BEGIN
IN AUTONOMOUS TRANSACTION DO
INSERT INTO tbl VALUES (1);
END;
END;
```
https://firebirdsql.org/refdocs/langrefupd25-psql-autonomous-trans.html
On 21.12.2023 14:26, Andrey M. Borodin wrote:
>
>> On 15 Dec 2023, at 16:28, Ivan Kush <ivan(dot)kush(at)tantorlabs(dot)com> wrote:
>>
>>
>>
>> Hello. I'm working on the support of autonomous transactions in Postgres.
>>
>> # Summary
>> * Add pragma AUTONOMOUS_TRANSACTION in the functions. When function
>> contains this pragma, the it's executed autonomously
>> * Background workers are used to run autonomous sessions.
>> * Synchronous execution between backend and autonomous session
>> * Postgres Client-Server Protocol is used to communicate between them
>> * Pool of autonomous sessions. Pool is created lazily.
>> * Infinite nested calls of autonomous functions are allowed. Limited
>> only by computer resources.
>> * If another 2nd autonomous function is called in the 1st autonomous
>> function, the 2nd is executed at the beginning, and then the 1st
>> continues execution.
> Cool, looks interesting! As far as I know EnterpriseDB, Postgres Pro and OracleDB have this functionality. So, seems like the stuff is in demand.
> How does your version compare to this widely used databases? Is anyone else using backgroud connections? Which syntax is used by other DBMS'?
>
> Looking into the code it seems like an easy way for PL\pgSQL function to have a client connection. I think this might work for other PLs too.
>
> The patch touches translations ( src/backend/po/). I think we typically do not do this in code patches, because this work is better handled by translators.
>
>
> Best regards, Andrey Borodin.
--
Best wishes,
Ivan Kush
Tantor Labs LLC
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Korotkov | 2023-12-24 12:02:45 | Re: Assert failure on 'list_member_ptr(rel->joininfo, restrictinfo)' |
Previous Message | Ivan Kush | 2023-12-24 11:27:47 | Re: Autonomous transactions 2023, WIP |