Re: Max connections reached without max connections reached

On Fri, Dec 3, 2021 at 9:02 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Dilip Kumar <dilipbalaut(at)gmail(dot)com> writes:
> > On Thu, Dec 2, 2021 at 9:35 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> >> I think there is no such view or anything which tells about which
> >> backend or transaction has more than 64 sub transaction. But if we
> >> are ready to modify the code then we can LOG that information in
> >> GetNewTransactionId(), when first time we are marking it overflown.
>
> > I have prepared a small patch to log this information.
>
> Putting an elog call into GetNewTransactionId seems like a completely
> horrid idea from a performance standpoint. Especially if you put it
> inside the XidGenLock hold, where it can block the entire system not just
> the one process. But even without that, this seems like a performance
> penalty with basically no real-world benefit. People who have issues
> like this are not going to want to trawl the postmaster log for such
> messages.

Agreed with both points. What about we add, subxid count and overflow
status in LocalPgBackendStatus and through that, we can show in
pg_stat_activity. That way we don't have to report it ever and
whenever the user is running pg_stat_activity they can fetch it
directly from "proc->subxidStatus", along with fetching the proc.xid
and proc.xmin. Does this make sense?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com