Re: Autonomous transactions 2023, WIP - Mailing list pgsql-hackers

 > 1. The solution based on background workers looks too fragile - it 
can be easy to exhaust all background workers, and because this feature 
is proposed mainly for logging, then it is a little bit dangerous, 
because it means loss of possibility of logging.

1. We could add types for background workers. For each type add 
guc-settings, like max workers of each type.
For examaple, for `common` leave `max_worker_processes` setting for 
backward compatibility
enum bgw_type {
   common,
   autonomous,
   etc....
};


 > 2. although the Oracle syntax is interesting, and I proposed PRAGMA 
more times,  it doesn't allow this functionality in other PL

2. Add `AUTONOMOUS` to `BEGIN` instead of `PRAGMA` in `DECLARE`? `BEGIN 
AUTONOMOUS`.
It shows immediately that we are in autonomous session, no need to 
search in subsequent lines for keyword.

```
CREATE FUNCTION foo() RETURNS void AS $$
BEGIN AUTONOMOUS
   INSERT INTO tbl VALUES (1);
   BEGIN AUTONOMOUS
    ....
    END;
END;
$$ LANGUAGE plpgsql;
```

 > CREATE OR REPLACE FUNCTION ...
 > AS $$
 > $$ LANGUAGE plpgsql AUTONOMOUS TRANSACTION;

The downside with the keyword in function declaration, that we will not 
be able to create autonomous subblocks. With `PRAGMA AUTONOMOUS` or 
`BEGIN AUTONOMOUS` it's possible to create them.

```
-- BEGIN AUTONOMOUS

CREATE FUNCTION foo() RETURNS void AS $$
BEGIN
   INSERT INTO tbl VALUES (1);
   BEGIN AUTONOMOUS
     INSERT INTO tbl VALUES (2);
   END;
END;
$$ LANGUAGE plpgsql;


-- or PRAGMA AUTONOMOUS

CREATE FUNCTION foo() RETURNS void AS $$
BEGIN
   INSERT INTO tbl VALUES (1);
   BEGIN
   DECLARE AUTONOMOUS_TRANSACTION;
     INSERT INTO tbl VALUES (2);
   END;
END;
$$ LANGUAGE plpgsql;


START TRANSACTION;
foo();
ROLLBACK;
```

```
Output:
2
```

 > it doesn't allow this functionality in other PL

I didn't work out on other PLs at the current time, but...

## Python

In plpython we could use context managers, like was proposed in Peter's 
patch. ```

with plpy.autonomous() as a:
     a.execute("INSERT INTO tbl VALUES (1) ");

```

## Perl

I don't programm in Perl. But googling shows Perl supports subroutine 
attributes. Maybe add `autonomous` attribute for autonomous execution?

```
sub foo :autonomous {
}
```

https://www.perl.com/article/untangling-subroutine-attributes/


 > Heikki wrote about the possibility to support threads in Postgres.

3. Do you mean this thread?
https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4%40iki.fi
Thanks for info. Will watch it. Unfortunately it takes many years to 
implement threads =(

 > Surely, the first topic should be the method of implementation. Maybe 
I missed it, but there is no agreement of background worker based.
I agree. No consensus at the current time.
Pros of bgworkers are:
1. this entity is already in Postgres.
2. possibility of asynchronous execution of autonomous session in the 
future. Like in pg_background extension. For asynchronous execution we 
need a separate process, bgworkers are this separate process.

Also maybe later create autonomous workers themselves without using 
bgworkers internally: launch of separate process, etc. But I think will 
be many common code with bgworkers.


On 21.12.2023 12:35, Pavel Stehule wrote:
> Hi
>
> although I like the idea related to autonomous transactions, I don't 
> think so this way is the best
>
> 1. The solution based on background workers looks too fragile - it can 
> be easy to exhaust all background workers, and because this feature is 
> proposed mainly for logging, then it is a little bit dangerous, 
> because it means loss of possibility of logging.
>
> 2. although the Oracle syntax is interesting, and I proposed PRAGMA 
> more times,  it doesn't allow this functionality in other PL
>
> I don't propose exactly  firebird syntax 
> https://firebirdsql.org/refdocs/langrefupd25-psql-autonomous-trans.html, 
> but I think this solution is better than ADA's PRAGMAs. I can imagine 
> some special flag for function like
>
> CREATE OR REPLACE FUNCTION ...
> AS $$
> $$ LANGUAGE plpgsql AUTONOMOUS TRANSACTION;
>
> as another possibility.
>
> 3. Heikki wrote about the possibility to support threads in Postgres. 
> One significant part of this project is elimination of global 
> variables. It can be common with autonomous transactions.
>
> Surely, the first topic should be the method of implementation. Maybe 
> I missed it, but there is no agreement of background worker based.
>
> Regards
>
> Pavel
>
>
-- 
Best wishes,
Ivan Kush
Tantor Labs LLC