public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ivan Kush <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: [email protected]
Subject: Re: Autonomous transactions 2023, WIP
Date: Sun, 31 Dec 2023 17:15:33 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFj8pRCQmfnz6ceEurCjDfRJai_1PErB6Diuj38f4UBF2zOP1g@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAFj8pRCty3SqJyOBCsgJK9abfQr4SJLt-aWxQFg93NYHWb_UsA@mail.gmail.com>
<[email protected]>
<CAFj8pRCQmfnz6ceEurCjDfRJai_1PErB6Diuj38f4UBF2zOP1g@mail.gmail.com>
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
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Autonomous transactions 2023, WIP
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox