Re: MVCC and all that...

On Tue, Sep 9, 2025 at 7:11 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> On Tue, Sep 9, 2025 at 8:41 PM Justin <zzzzz(dot)graf(at)gmail(dot)com> wrote:
>
>>
>> On autonomous transactions we have procedures now that allow transactions
>> inside of transactions that can be committed and rollbacked. that has been
>> around for several years now.
>>
> [snip]
>>
> anonymous functions in PG have several limitation not just input
>> arguments (not sure i see the need for that)
>>
>
> Aren't transience and "ad hockery" the whole point of anonymous
> procedures? Thus, I don't see the point of passing them parameters, either.
>
> (When I *do* need something similar, I build the DO block as a bash
> string variable with environment variables as "parameters", and then
> execute it via psql -c "$sql" More like a template, TBH. It's great for
> purging old data from tables, since I can bypass records who's DELETE
> statements fail due to a FK constraint.
>

IMO, you can't equate procedures to autonomous transactions. The only way
to do AT in a procedure today is with dblink if you are limited to
core/contrib. pg_background can do them, as can my library, pgasync.
<https://github.com/leaselock/pgasync> I would humbly state it's a very
high quality library to deal with them in a general way :).

Autonomous transactions are basically threading at the SQL level. The
classic use case for them is to emit log records while you're processing
some bigger transaction, so that if/when rollback occurs you have some
evidence of processing. There are many, many other use cases for them
however if you are deep into backend programming.

I will say that the stored procedure COMMIT feature made this library
possible as before, it was not possible to have long running processes in
the database at the SQL level.

Personally, rather than having explicit syntax supporting AT (or at least,
in addition to-), I would like to see the dblink library boned up; we ought
to have asynchronous connections, and a multi connection dblink_poll() to
avoid the dblink_is_busy polling loop.

Getting back to firebase, I suppose we ought not to treat the article
author too harshly. There's nothing wrong with advocating for your
solution although a more balanced treatment might have been more compelling.

merlin