Re: Autonomous transactions 2023, WIP

> 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.kush@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
likethe stuff is in demand.
 
> How does your version compare to this widely used databases? Is anyone else using backgroud connections? Which syntax
isused 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
workfor other PLs too.
 
>
> The patch touches translations ( src/backend/po/). I think we typically do not do this in code patches, because this
workis better handled by translators.
 
>
>
> Best regards, Andrey Borodin.

-- 
Best wishes,
Ivan Kush
Tantor Labs LLC