Re: Report oldest xmin source when autovacuum cannot remove tuples

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)