> 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
