Re: Autonomous transactions 2023, WIP

On 01.01.2024 09:47, Pavel Stehule wrote:
>
>
>     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.

What do you mean by the "dirty"?

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

Maybe. Need to benchmark. Also OLAP like ClickHouse is better for
storing logs.

But in this case (log file -> database) a company needs to write a
custom utility to load log file to the database:

* detect file size,

* load to database

* autorotate file by timeout of filesize

* etc.

Some of our customers use "Autonomous transactions" for logging =)

--
Best wishes,
Ivan Kush
Tantor Labs LLC