Hi Sami > Thinking about point 3 above, I began to wonder if this > whole thing can be simplified with inspiration. Looking at the > existing BackendXidGetPid(), I think it can.
> Based on BackendXidGetPid(), I tried a new routine called > BackendXidFindCutOffReason() which can take in the cutoff xmin, > passed in by vacuum and can walk though the proc array and > determine the reason. We don't need to touch ComputeXidHorizons() > to make this work, it seems to me. This comes with an additional > walk though the procarray holding a shared lock, but I don't think > this will be an issue. > Attached is a rough sketch of BackendXidFindCutOffReason() > For now, I just added NOTICE messages which will log with > VACUUM (verbose) for testing. I like your idea , I think we also could consider introducing a GUC parameter in the future, which would terminate sessions blocking vacuum operations when the table's age reaches vacuum_failsafe_age. Thanks On Sat, Nov 15, 2025 at 8:25 AM Sami Imseih <[email protected]> wrote: > Thanks for starting this thread! This is a very useful > feature that users will find beneficial to easily narrow > down the reason the xmin horizon is being held back, > and take action. > > Adding this information to the vacuum logging is useful, but > I can see this information being exposed in a view as well in > the future. > > I have a few comments: > > A few minor ones: > > 1/ pid should be declared as "pid_t" > > 2/ last value of an enum should be have a traling comma > +typedef enum OldestXminSource > +{ > + OLDESTXMIN_SOURCE_ACTIVE_TRANSACTION, > + OLDESTXMIN_SOURCE_HOT_STANDBY_FEEDBACK, > + OLDESTXMIN_SOURCE_PREPARED_TRANSACTION, > + OLDESTXMIN_SOURCE_REPLICATION_SLOT, > + OLDESTXMIN_SOURCE_OTHER > +} OldestXminSource; > > More importantly: > > 3/ As mentioned earlier in the thread, the "idle-in-transaction" > transactions is not being reported correctly, particularly for write > tansactions. I think that is an important missing case. The reason > for this is the cutoff xmin is not being looked up against the current > list of xid's, so we are not blaming the correct pid. > > 4/ > Thinking about point 3 above, I began to wonder if this > whole thing can be simplified with inspiration. Looking at the > existing BackendXidGetPid(), I think it can. > > Based on BackendXidGetPid(), I tried a new routine called > BackendXidFindCutOffReason() which can take in the cutoff xmin, > passed in by vacuum and can walk though the proc array and > determine the reason. We don't need to touch ComputeXidHorizons() > to make this work, it seems to me. This comes with an additional > walk though the procarray holding a shared lock, but I don't think > this will be an issue. > > Attached is a rough sketch of BackendXidFindCutOffReason() > For now, I just added NOTICE messages which will log with > VACUUM (verbose) for testing. > > This takes what you are doing in v1 inside ComputeXidHorizons() > into a new routine. I think this is a cleaner approach. > > 5/ Also, I think we should also include tests for serializable > transactions > > > What do you think? > > -- > > Sami Imseih > Amazon Web Services (AWS) >
