Hi ne 31. 12. 2023 v 15:15 odesÃlatel Ivan Kush <ivan.k...@tantorlabs.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 > >