Re: Skipping logical replication transactions on subscriber side

On Tue, Jul 6, 2021 at 7:13 PM Alexey Lesovsky <lesovsky(at)gmail(dot)com> wrote:
>
> On Tue, Jul 6, 2021 at 10:58 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>
>> > Also, I'd like to suggest thinking twice about the view name (and function used in view DDL) - "pg_stat_logical_replication_error" contains very common "logical replication" words, but the view contains errors related to subscriptions only. In the future there could be other kinds of errors related to logical replication, but not related to subscriptions - what will you do?
>>
>>
>> Is pg_stat_subscription_errors or
>> pg_stat_logical_replication_apply_errors better?
>
>
> It seems to me 'pg_stat_subscription_conflicts' proposed by Amit Kapila is the most suitable, because it directly says about conflicts occurring on the subscription side. The name 'pg_stat_subscription_errors' is also good, especially in case of further extension if some kind of similar errors will be tracked.

I personally prefer pg_stat_subscription_errors since
pg_stat_subscription_conflicts could be used for conflict resolution
features in the future. This stats view I'm proposing is meant to
focus on errors that happened during applying logical changes. So
using the term 'errors' seems to make sense to me.

>
>>
>> > 3. Add a counter field with total number of errors - it helps to calculate errors rates and aggregations (sum), and don't lose information about errors between view checks.
>>
>> Do you mean to increment the error count if the error (command, xid,
>> and relid) is the same as the previous one? or to have the total
>> number of errors per subscription? And what can we infer from the
>> error rates and aggregations?
>
>
> To be honest, I hurried up when I wrote the first email, and read only about stats view. Later, I read the starting email about the patch and rethought this note.
>
> As I understand, when the conflict occurs, replication stops (until conflict is resolved), an error appears in the stats view. Now, no new errors can occur in the blocked subscription. Hence, there are impossible situations when many errors (like spikes) have occurred and a user didn't see that. If I am correct in my assumption, there is no need for counters. They are necessary only when errors might occur too frequently (like pg_stat_database.deadlocks). But if this is possible, I would prefer the total number of errors per subscription, as also proposed by Amit.

Yeah, the total number of errors seems better.

>
> Under "error rates and aggregations" I also mean in the context of when a high number of errors occured in a short period of time. If a user can read the "total errors" counter and keep this metric in his monitoring system, he will be able to calculate rates over time using functions in the monitoring system. This is extremely useful.

Thanks for your explanation. Agreed. But the rate depends on
wal_retrieve_retry_interval so is not likely to be high in practice.

> I also would like to clarify, when conflict is resolved - the error record is cleared or kept in the view? If it is cleared, the error counter is required (because we don't want to lose all history of errors). If it is kept - the flag telling about the error is resolved is needed (or set xid to NULL). I mean when the user is watching the view, he should be able to identify if the error has already been resolved or not.

With the current patch, once the conflict is resolved by skipping the
transaction in question, its entry on the stats view is cleared. As
you suggested, if we have the total error counts in that view, it
would be good to keep the count and clear other fields such as xid,
last_failure, and command etc.

Regards,

--
Masahiko Sawada
EDB: https://www.enterprisedb.com/