[YSQL] Reset Transaction ID Counter during Postmaster Startup/Shutdown

[karthik-ramanathan-3006]

Jira Link: DB-15592

Description

#26205 explores an issue that can cause PostgreSQL Transaction IDs (XIDs) to get exhausted quickly.
The fix for this issue involves ensuring that at most 1 XID is used per statement/transaction.

However, an issue remains that XIDs can still get exhausted (albeit much more slowly) as YugabyteDB does not perform vacuuming of the XIDs like vanilla PostgreSQL does. This Github issue seeks to reset XIDs on Postmaster startup/shutdown so that there is a graceful way to recover from such exhaustion, without having to require manual intervention. This solution works because XIDs are node local, and are not persisted in the catalog. So, any node can choose to reset its counter without having to coordinate with other nodes in the cluster. The latest XID is instead persisted to a local file pg_control which the postmaster reads upon startup. Performing a reset at startup/shutdown would also make it easier to avoid race conditions in accessing the control file as the postmaster is guaranteed to be the only reader/writer.

Issue Type

kind/enhancement

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.