Re: Autonomous transactions 2023, WIP

> 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