Message-ID: <76bb1c7c-4c3d-4e10-a8ee-0d41a251b689@tantorlabs.com>
Date: Sun, 24 Dec 2023 14:27:47 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Autonomous transactions 2023, WIP
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-hackers@postgresql.org
References: <59cb5ad2-f814-464f-9f16-1f74575f11a8@tantorlabs.com>
 <f7470d5a-3cf1-4919-8404-5c4d91341a9f@tantorlabs.com>
 <CAFj8pRCty3SqJyOBCsgJK9abfQr4SJLt-aWxQFg93NYHWb_UsA@mail.gmail.com>
Content-Language: en-US
From: Ivan Kush <ivan.kush@tantorlabs.com>
Organization: Tantor Labs LLC
In-Reply-To: <CAFj8pRCty3SqJyOBCsgJK9abfQr4SJLt-aWxQFg93NYHWb_UsA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/76bb1c7c-4c3d-4e10-a8ee-0d41a251b689%40tantorlabs.com>
Precedence: bulk

 > 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