Re: Autonomous transactions 2023, WIP

Hi

ne 31. 12. 2023 v 15:15 odesílatel Ivan Kush <ivan(dot)kush(at)tantorlabs(dot)com>
napsal:

>
> 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.
>

almost all these tasks are more or less dirty. It is a serious question if
we want to integrate pg_background to core.

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.
>

I afraid so this solution can be very significantly slower than logging to
postgres log or forwarding to syslog

>
> >
> >
> > > 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
>
>