New feature: prepared_xacts

[ylacancellera]

Having xmin age from prepared_xacts could help
I'm thinking of

select *, (now()-prepared)::INTERVAL as duration, age(transaction) as trx_age from pg_prepared_xacts order by age(transaction) desc;

[jobinau]

Thank you, @ylacancellera, for this feedback.
This is something I also thought about previously. Unfortunately, I didn't see many cases of people using Prepared transactions / Distributed transactions / Two-phase commit in actual use cases.
Is it for something like Citus? Please share your thoughts.
I would appreciate your thoughts and feedback.

[ylacancellera]

This is just because it's one of the potential reason that could explain a wraparound/vacuum issue: it can hoard row versions
So having xmin associated to prepared txs can give a more complete overview when dealing with vacuum issues

I agree, i did not see many cases either, but having an abandoned prepared tx could be useful in the rare cases it happens, it's too easy to miss

[jobinau]

aah, that is an interesting requirement. that makes sense.
Basically, we need to rule out the possibility of some distributed transactions holding any xmin reference. Right ?
If yes, we need not list all prepared transactions, instead, report if there is one, in the "Findings" section.

[ylacancellera]

Actually yes, having the oldest xmin age and when it was prepared in findings seems more than enough