> 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;
```
https://www.enterprisedb.com/docs/epas/latest/application_programming/epas_compat_spl/06_transaction_control/03_pragma_autonomous_transaction/
# 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;
```
https://help.sap.com/docs/SAP_HANA_PLATFORM/de2486ee947e43e684d39702027f8a94/4ad70daee8b64b90ab162565ed6f73ef.html
# 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.k...@tantorlabs.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
