Re: Autonomous transactions 2023, WIP

Hi

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

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

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

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