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

On 24.12.2023 15:38, Pavel Stehule wrote:
> Can you show some benchmarks? I don't like this system too much but
> maybe it can work enough.
>
> Still I am interested in possible use cases. If it should be used only
> for logging, then we can implement something less generic, but surely
> with better performance and stability. Logging to tables is a little
> bit outdated.
>
> Regards
>
> Pavel

All use cases of pg_background, except asynchronous execution. If later
add asynchronous execution, then all =)

For example, also:

* conversion from Oracle's `PRAGMA AUTONOMOUS` to Postgres.

* possibility to create functions that calls utility statements, like
VACUUM, etc.

I don't have good benchmarks now. Some simple, like many INSERTs. Pool
gives advantage, more tps compared to pg_background with increasing
number of backends.

The main advantage over pg_background is pool of workers. In this patch
separate pool is created for each backend. At the current time I'm
coding one shared pool for all backends.

>
>
>      > 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
>
--
Best wishes,
Ivan Kush
Tantor Labs LLC