Re: Long running query causing XID limit breach

On Thu, 2024-05-23 at 18:15 +0530, sud wrote:
> On Thu, May 23, 2024 at 1:45 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > If a long running query on the standby influences the primary, that means that
> > you have "hot_standby_feedback" set to "on".  Set it to "off".
>
> Will the setting up of "hot_standby_feedback" value to OFF will cause the
> reader instance to give incorrect query results or unexpected query failure
> which will be potential inconsistency between the writer and reader instance,
> as because those XID's can be removed/cleaned by the writer node even if its
> being read by the reader instance query. And it can have more replication lag.

There will never be incorrect query results.

It can happen that a query on the standby gets canceled if you don't set
"max_standby_streaming_delay" to -1, but that can happen even if
"hot_standby_feedback" is "on".  It just happens less often.

The effect of setting "max_standby_streaming_delay" to -1 will often be a
replication delay if you run a long query.  That's what you have to
accept if you want to execute long-running queries.

You will never be able to have both of the following:
- queries never get canceled
- there is no replication delay