Re: Max connections reached without max connections reached

On Wed, Dec 1, 2021 at 8:54 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

> On 12/1/21 7:08 PM, Michael Lewis wrote:
>
> On Wed, Dec 1, 2021 at 3:52 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
>> Also be on the look-out for begin trans; begin trans; etc I read
>> Kumar's report as nested transactions. If your gang is doing a transaction
>> per row, they need a kick in the rear. Anyone not closing those needs a
>> pink slip.
>>
>
> I have seen the second attempt at calling begin seem to be ignored with
> "WARNING: there is already a transaction in progress" similar to calling
> rollback without a transaction open. Are you stating or suggesting that
> calling begin a second time actually is more similar to
> creating a savepoint? That would be rather unexpected from the messaging,
> at least for me.
>
> No. Hard to know what the devs are up to here (what stack they're using,
> etc), but I'm suggesting one transaction is getting started and not closed.
> Warnings have been know to be ignored. I admit I am not familiar with
> Kumar's term of "sub-transaction" and how those might be created but it
> sounds to me like a transaction is begun early in the processing and more
> work happens unaware of the transaction. And possible not committed.
>

Okay cool. Subtransactions are interesting. They are used when a savepoint
is issued, and with exception blocks in PL/pgSQL functions. They allow the
system to keep track of which "sub" process made changes to rows. So just
like the main transaction gets a transaction ID and rows get marked with
xmin or xmax as needed for MVCC, the subtransactions get their own
transaction ID and the objects they modify get updated to set xmin and xmax
with their transaction ID. There are some interesting performance
bottlenecks when many subtransactions get involved, but I am still learning
about that.

Disclaimer- I am no kind of PG contributor and very much still learning,
the above explanation is my best understanding but I welcome any
corrections or clarifications.