MIME-Version: 1.0
References: 
 <CAOzEurSgy-gDtwFmEbj5+R9PL0_G3qYB6nnzJtNStyuf87VSVg@mail.gmail.com>
 <CAHGQGwFVdccfhN7PFHU5gZcMNdQuyL8DhZSks2FuuTcZ5V_pGQ@mail.gmail.com>
 <CAGjGUAL-qZ6Na-cH4RY5raF4NwuF47ZVNyP0uMYQcks3w5b7Dw@mail.gmail.com>
In-Reply-To: 
 <CAGjGUAL-qZ6Na-cH4RY5raF4NwuF47ZVNyP0uMYQcks3w5b7Dw@mail.gmail.com>
From: Sami Imseih <samimseih@gmail.com>
Date: Fri, 14 Nov 2025 18:25:36 -0600
Message-ID: 
 <CAA5RZ0s+UUXekbeGcC-H71kW=MfeaUCOV=yEWX94NXViO2-=pA@mail.gmail.com>
Subject: Re: Report oldest xmin source when autovacuum cannot remove tuples
To: wenhui qiu <qiuwenhuifx@gmail.com>
Cc: Shinya Kato <shinya11.kato@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000e3f53a06439727e0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA5RZ0s%2BUUXekbeGcC-H71kW%3DMfeaUCOV%3DyEWX94NXViO2-%3DpA%40mail.gmail.com>
Precedence: bulk

--000000000000e3f53a06439727e0
Content-Type: text/plain; charset="UTF-8"

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)

--000000000000e3f53a06439727e0
Content-Type: text/plain; charset="US-ASCII";
 name="0001-sketch-of-cutoff-reasons.txt"
Content-Disposition: attachment;
	filename="0001-sketch-of-cutoff-reasons.txt"
Content-Transfer-Encoding: base64
Content-ID: <f_mhzjdwad0>
X-Attachment-Id: f_mhzjdwad0

RnJvbSA1MzkxNWJjMWZkMDY3OTBmYzExMmNiMmFjOWU0YjljYWE3NDJjZjkyIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBTYW1pIEltc2VpaCA8c2ltc2VpaEBhbWF6b24uY29tPgpEYXRl
OiBGcmksIDE0IE5vdiAyMDI1IDE4OjE1OjI1IC0wNjAwClN1YmplY3Q6IFtQQVRDSCAxLzFdIHNr
ZXRjaCBvZiBjdXRvZmYgcmVhc29ucwoKLS0tCiBzcmMvYmFja2VuZC9hY2Nlc3MvaGVhcC92YWN1
dW1sYXp5LmMgfCAgNSArKysKIHNyYy9iYWNrZW5kL3N0b3JhZ2UvaXBjL3Byb2NhcnJheS5jICB8
IDYwICsrKysrKysrKysrKysrKysrKysrKysrKysrKysKIHNyYy9pbmNsdWRlL3N0b3JhZ2UvcHJv
Y2FycmF5LmggICAgICB8ICAxICsKIDMgZmlsZXMgY2hhbmdlZCwgNjYgaW5zZXJ0aW9ucygrKQoK
ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL3ZhY3V1bWxhenkuYyBiL3NyYy9i
YWNrZW5kL2FjY2Vzcy9oZWFwL3ZhY3V1bWxhenkuYwppbmRleCBkZWI5YTNkYzBkMS4uZGYxZGY2
YjA3MzMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2FjY2Vzcy9oZWFwL3ZhY3V1bWxhenkuYwor
KysgYi9zcmMvYmFja2VuZC9hY2Nlc3MvaGVhcC92YWN1dW1sYXp5LmMKQEAgLTE1Miw2ICsxNTIs
NyBAQAogI2luY2x1ZGUgInN0b3JhZ2UvYnVmbWdyLmgiCiAjaW5jbHVkZSAic3RvcmFnZS9mcmVl
c3BhY2UuaCIKICNpbmNsdWRlICJzdG9yYWdlL2xtZ3IuaCIKKyNpbmNsdWRlICJzdG9yYWdlL3By
b2NhcnJheS5oIgogI2luY2x1ZGUgInN0b3JhZ2UvcmVhZF9zdHJlYW0uaCIKICNpbmNsdWRlICJ1
dGlscy9sc3lzY2FjaGUuaCIKICNpbmNsdWRlICJ1dGlscy9wZ19ydXNhZ2UuaCIKQEAgLTEwNDcs
NiArMTA0OCwxMCBAQCBoZWFwX3ZhY3V1bV9yZWwoUmVsYXRpb24gcmVsLCBjb25zdCBWYWN1dW1Q
YXJhbXMgcGFyYW1zLAogCQkJYXBwZW5kU3RyaW5nSW5mbygmYnVmLAogCQkJCQkJCSBfKCJyZW1v
dmFibGUgY3V0b2ZmOiAldSwgd2hpY2ggd2FzICVkIFhJRHMgb2xkIHdoZW4gb3BlcmF0aW9uIGVu
ZGVkXG4iKSwKIAkJCQkJCQkgdmFjcmVsLT5jdXRvZmZzLk9sZGVzdFhtaW4sIGRpZmYpOworCisJ
CQlpZiAodmFjcmVsLT5yZWNlbnRseV9kZWFkX3R1cGxlcyA+IDApCisJCQkJQmFja2VuZFhpZEZp
bmRDdXRPZmZSZWFzb24odmFjcmVsLT5jdXRvZmZzLk9sZGVzdFhtaW4pOworCiAJCQlpZiAoZnJv
emVueGlkX3VwZGF0ZWQpCiAJCQl7CiAJCQkJZGlmZiA9IChpbnQzMikgKHZhY3JlbC0+TmV3UmVs
ZnJvemVuWGlkIC0KZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3N0b3JhZ2UvaXBjL3Byb2NhcnJh
eS5jIGIvc3JjL2JhY2tlbmQvc3RvcmFnZS9pcGMvcHJvY2FycmF5LmMKaW5kZXggMjAwZjcyYzZl
MjUuLjQ1ZGZlOGE5YmUyIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9zdG9yYWdlL2lwYy9wcm9j
YXJyYXkuYworKysgYi9zcmMvYmFja2VuZC9zdG9yYWdlL2lwYy9wcm9jYXJyYXkuYwpAQCAtMzI0
NCw2ICszMjQ0LDY2IEBAIEJhY2tlbmRYaWRHZXRQaWQoVHJhbnNhY3Rpb25JZCB4aWQpCiAJcmV0
dXJuIHJlc3VsdDsKIH0KIAordm9pZAorQmFja2VuZFhpZEZpbmRDdXRPZmZSZWFzb24oVHJhbnNh
Y3Rpb25JZCB4aWQpCit7CisJUHJvY0FycmF5U3RydWN0ICphcnJheVAgPSBwcm9jQXJyYXk7CisJ
VHJhbnNhY3Rpb25JZCAqb3RoZXJfeGlkcyA9IFByb2NHbG9iYWwtPnhpZHM7CisJYm9vbAkJZm91
bmRfcmVhc29uID0gZmFsc2U7CisKKwlBc3NlcnQoeGlkICE9IEludmFsaWRUcmFuc2FjdGlvbklk
KTsKKworCUxXTG9ja0FjcXVpcmUoUHJvY0FycmF5TG9jaywgTFdfU0hBUkVEKTsKKworCWVsb2co
Tk9USUNFLCAiPj4+Pj4+IGxvb2tpbmcgdXAgcmVhc29uIGZvciAlZCIsIHhpZCk7CisKKwlmb3Ig
KGludCBpbmRleCA9IDA7IGluZGV4IDwgYXJyYXlQLT5udW1Qcm9jczsgaW5kZXgrKykKKwl7CisJ
CWludAkJCXBncHJvY25vID0gYXJyYXlQLT5wZ3Byb2Nub3NbaW5kZXhdOworCQlQR1BST0MJICAg
KnByb2MgPSAmYWxsUHJvY3NbcGdwcm9jbm9dOworCisJCS8qIENhc2UgMTogeGlkIG1hdGNoZXMg
dGhlIHNlc3Npb24ncyBiYWNrZW5kIFhJRCAqLworCQlpZiAob3RoZXJfeGlkc1tpbmRleF0gPT0g
eGlkKQorCQl7CisJCQlpZiAocHJvYy0+cGlkID09IDApCisJCQkJLyogd2l0aCBhIHByZXBhcmVk
IHRyYW5zYWN0aW9uICovCisJCQkJZWxvZyhOT1RJQ0UsICI+Pj4+Pj4gcHJlcGFyZWQgdHJhbnNh
Y3Rpb24gcHJvYy0+c3RhdHVzRmxhZ3MgJXUiLCBwcm9jLT5zdGF0dXNGbGFncyk7CisJCQllbHNl
CisJCQkJLyogb3IgYSB3cml0ZSB0cmFuc2FjdGlvbiAqLworCQkJCWVsb2coTk9USUNFLCAiPj4+
Pj4+IHhpZDogdHJhbnNhY3Rpb24gQmFja2VuZFhpZEdldFBpZCA9ICVkIHByb2MtPnN0YXR1c0Zs
YWdzICV1IiwgcHJvYy0+cGlkLCBwcm9jLT5zdGF0dXNGbGFncyk7CisKKwkJCWZvdW5kX3JlYXNv
biA9IHRydWU7CisJCQlicmVhazsKKwkJfQorCisJCS8qIENhc2UgMjogeGlkIG1hdGNoZXMgeG1p
biAqLworCQlpZiAocHJvYy0+eG1pbiA9PSB4aWQpCisJCXsKKwkJCS8qIG9yIGFmZmVjdHMgaG9y
aXpvbnMsIHdoaWNoIGlzIGR1ZSB0byBob3Rfc3RhbmRieV9mZWVkYmFjayAqLworCQkJaWYgKHBy
b2MtPnN0YXR1c0ZsYWdzICYgUFJPQ19BRkZFQ1RTX0FMTF9IT1JJWk9OUykKKwkJCXsKKwkJCQll
bG9nKE5PVElDRSwgIj4+Pj4+PiBob3Rfc3RhbmRieV9mZWVkYmFjayA9PSBwaWQgb2Ygd2FscmVj
ZWl2ZXIgJWQgcHJvYy0+c3RhdHVzRmxhZ3MgJXUiLCBwcm9jLT5waWQsIHByb2MtPnN0YXR1c0Zs
YWdzKTsKKwkJCQlmb3VuZF9yZWFzb24gPSB0cnVlOworCQkJCWJyZWFrOworCQkJfQorCisJCQkv
KiBvciBhIHJlYWQtb25seSB0cmFuc2FjdGlvbiAqLworCQkJZWxvZyhOT1RJQ0UsICI+Pj4+Pj4g
eG1pbjogdHJhbnNhY3Rpb24gQmFja2VuZFhpZEdldFBpZCA9ICVkIHByb2MtPnN0YXR1c0ZsYWdz
ID0gJXUiLCBwcm9jLT5waWQsIHByb2MtPnN0YXR1c0ZsYWdzKTsKKwkJCWZvdW5kX3JlYXNvbiA9
IHRydWU7CisJCQlicmVhazsKKwkJfQorCX0KKworCS8qCisJICogd2UgZmFpbGVkIHRvIGZpbmQg
cmVhc29uLCBzbyBpdCdzIGxpa2VseSBhIGxvZ2ljYWwgcmVwbGljYXRpb24gc2xvdCwgb3IKKwkg
KiBzb21lIG90aGVyIHJlYXNvbgorCSAqLworCWlmICghZm91bmRfcmVhc29uKQorCQllbG9nKE5P
VElDRSwgIj4+Pj4+PiBvdGhlciByZWFzb25zLCBpbmNsdWRpbmcgbG9naWNhbCByZXBsaWNhdGlv
biBzbG90Iik7CisKKwlMV0xvY2tSZWxlYXNlKFByb2NBcnJheUxvY2spOworfQorCiAvKgogICog
SXNCYWNrZW5kUGlkIC0tIGlzIGEgZ2l2ZW4gcGlkIGEgcnVubmluZyBiYWNrZW5kCiAgKgpkaWZm
IC0tZ2l0IGEvc3JjL2luY2x1ZGUvc3RvcmFnZS9wcm9jYXJyYXkuaCBiL3NyYy9pbmNsdWRlL3N0
b3JhZ2UvcHJvY2FycmF5LmgKaW5kZXggMmY0YWUwNmMyNzkuLmIwY2RlZWRiODQ4IDEwMDY0NAot
LS0gYS9zcmMvaW5jbHVkZS9zdG9yYWdlL3Byb2NhcnJheS5oCisrKyBiL3NyYy9pbmNsdWRlL3N0
b3JhZ2UvcHJvY2FycmF5LmgKQEAgLTcxLDYgKzcxLDcgQEAgZXh0ZXJuIHZvaWQgUHJvY051bWJl
ckdldFRyYW5zYWN0aW9uSWRzKGludCBwcm9jTnVtYmVyLCBUcmFuc2FjdGlvbklkICp4aWQsCiBl
eHRlcm4gUEdQUk9DICpCYWNrZW5kUGlkR2V0UHJvYyhpbnQgcGlkKTsKIGV4dGVybiBQR1BST0Mg
KkJhY2tlbmRQaWRHZXRQcm9jV2l0aExvY2soaW50IHBpZCk7CiBleHRlcm4gaW50CUJhY2tlbmRY
aWRHZXRQaWQoVHJhbnNhY3Rpb25JZCB4aWQpOworZXh0ZXJuIHZvaWQgQmFja2VuZFhpZEZpbmRD
dXRPZmZSZWFzb24oVHJhbnNhY3Rpb25JZCB4aWQpOwogZXh0ZXJuIGJvb2wgSXNCYWNrZW5kUGlk
KGludCBwaWQpOwogCiBleHRlcm4gVmlydHVhbFRyYW5zYWN0aW9uSWQgKkdldEN1cnJlbnRWaXJ0
dWFsWElEcyhUcmFuc2FjdGlvbklkIGxpbWl0WG1pbiwKLS0gCjIuNTAuMSAoQXBwbGUgR2l0LTE1
NSkKCg==
--000000000000e3f53a06439727e0--