Horton

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

Alexandra Wang <alexandra.wang.oss@gmail.com>

View thread
From: Alexandra Wang <alexandra.wang.oss@gmail.com>
To: Daniil Davydov <3danissimo@gmail.com>
Cc: noah@leadboat.com, Tom Lane <tgl@sss.pgh.pa.us>, Stepan Yankevych <Stepan_Yankevych@epam.com>, Stepan Yankevych <stepya@ukr.net>, "sergii.zhuravlev@smartnet.ua" <sergii.zhuravlev@smartnet.ua>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>, Shruthi Gowda <gowdashru@gmail.com>, Matheus Alcantara <matheusssilv97@gmail.com>
Date: 2025-08-05T23:30:12Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix bug where we truncated CLOG that was still needed by LISTEN/NOTIFY

Hi Daniil,

On Mon, Aug 4, 2025 at 4:08 AM Daniil Davydov <3danissimo@gmail.com> wrote:

> Hi,
>
> On Fri, Aug 1, 2025 at 10:41 AM Alexandra Wang
> <alexandra.wang.oss@gmail.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