Re: Autonomous transactions 2023, WIP

po 1. 1. 2024 v 12:15 odesílatel Ivan Kush <ivan(dot)kush(at)tantorlabs(dot)com>
napsal:

>
> 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 think so these task should be implemented in stored procedures

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

I understand the motivation. But it was designed 20 years ago, and I don't
see a reason why we need to implement the same "bad" patterns, mainly when
the proposed implementation is not fully robust or can have performance
issues.

>
> --
> Best wishes,
> Ivan Kush
> Tantor Labs LLC
>
>