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
