Re: Autonomous transactions 2023, WIP

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