Re: Autonomous transactions 2023, WIP - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Autonomous transactions 2023, WIP
Date
Msg-id CAFj8pRAsuHS8FkDdVDCueMkMat=6N=i1Ga2sJGOY6hD-h-3C8A@mail.gmail.com
Whole thread Raw
In response to Re: Autonomous transactions 2023, WIP  (Ivan Kush <ivan.kush@tantorlabs.com>)
List pgsql-hackers


po 1. 1. 2024 v 12:15 odesílatel Ivan Kush <ivan.kush@tantorlabs.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

pgsql-hackers by date:

Previous
From: Ashutosh Bapat
Date:
Subject: Re: Proposal to include --exclude-extension Flag in pg_dump
Next
From: Ayush Vatsa
Date:
Subject: Re: Proposal to include --exclude-extension Flag in pg_dump