Request replica with commit timestamp

[holvianssi]

Is your feature request related to a problem? Please describe.
Assume for example a use case of writing a comment to database, then in an async task read the comment and index it to search index server.

The async task could easily use replica, as it is a read only transaction from the point of view of the database. However, due to replication lag, the async task might not yet see the comment in a replica.

Describe the solution you'd like
Allow telling pgcat that transaction timestamp X must be visible in the server pgcat chooses.

First, the client would fetch the commit timestamp for the write, then include this information for the async task, and the async task would tell pgcat that commit timestamp X must be visible.

The client and pgcat could use PostgreSQL system function pg_last_committed_xact() to obtain the timestamp.

The query syntax could be SET SERVER XACT TIMESTAMP timestamp. When this is issued pgcat would choose a replica which has the timestamp available (last commit timestamp >= provided timestamp). If no such replica exists, then query would be routed to primary.

Describe alternatives you've considered

• Pin connection to primary after write transaction. Downside is that even if the write transaction would be visible already in a replica, primary will be used)
• Not care about the problem. Downside is that sometimes clients will not be able to read the transaction they just wrote, resulting in hard to debug cases. As example, write comment -> async task to index comment -> async task doesn't yet see comment.
• Try to do this in code directly. Leads to code knowing too much about the database setup, plus somewhat easy to get this slightly wrong.
• Retry if transaction not visible. Doesn't easily work for all use cases. For example retry later on is not that easy to implement for HTTP request processing.

Additional context
pg_last_committed_xact(): https://www.postgresql.org/docs/current/functions-info.html