Re: BUG #16961: Could not access status of transaction

Hi Daniil,

On Mon, Aug 4, 2025 at 4:08 AM Daniil Davydov <3danissimo(at)gmail(dot)com> wrote:

> Hi,
>
> On Fri, Aug 1, 2025 at 10:41 AM Alexandra Wang
> <alexandra(dot)wang(dot)oss(at)gmail(dot)com> wrote:
> >
> > I'm not sure what's the best solution here. When ComputeXidHorizons()
> > determines the cutoff xid, it doesn't check asyncQueueControl for the
> > sender's xids stored in each AsyncQueueEntry. It make sense that the
> > async queue itself isn't expected to survive a database restart, but
> > should the sender's xids it stores be considered?
> >
> > What are your thoughts?
> >
>
> Finding the minimum xid in async queue may consume a lot of time, so I
> don't think
> that we should teach ComputeXidHorizons to look into async queue :
> 1) This is pretty "hot" function
> 2) We don't need minimal xid from async queue each time
> ComputeXidHorizons is called
>
> IMO, the best solution is to create something like the
> "AsyncQueueMinXid" function and
> call it in the beginning of vac_update_datfrozenxid. Thus,
> newFrozenXid will be capped
> by minimal sender's xid and autovacuum could not advance it too far.
>
> If we want to find out the minimum xid in a reasonable amount of time, we
> need
> something like a tree structure for xids from async queue (which will
> be maintained
> together with SLRU structures).
>
> I still don't have a solution for it. Maybe we should write to the
> pgsql-hackers
> mailing list with this problem?
>
> BTW, I'll attach an interim solution of this problem to this letter
> (explicitly advance
> queue tail before advancing datfrozenxid).
>
> --
> Best regards,
> Daniil Davydov
>

Thank you for sharing your interim solution! Inspired by your solution, I
found
that there is an existing built-in function
"pg_notification_queue_usage()"[1]

pg_notification_queue_usage () → double precision
> Returns the fraction (0–1) of the asynchronous notification queue's maximum
> size that is currently occupied by notifications that are waiting to be
> processed. See LISTEN and NOTIFY for more information.

This function calls asyncQueueAdvanceTail(). I think it's a similar
workaround
to your patch, but without code change.

What do you think?

[1] https://www.postgresql.org/docs/current/functions-info.html

Best,
Alex