Re: Skipping logical replication transactions on subscriber side

On Wed, Jan 26, 2022 at 12:14 AM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>
> On Tue, Jan 25, 2022 at 8:09 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>>
>> On Tue, Jan 25, 2022 at 11:58 PM David G. Johnston
>> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>> >
>> > On Tue, Jan 25, 2022 at 7:47 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>> >>
>> >> Yeah, I think it's a good idea to clear the subskipxid after the first
>> >> transaction regardless of whether the worker skipped it.
>> >>
>> >
>> > So basically instead of stopping the worker with an error you suggest having the worker continue applying changes (after resetting subskipxid, and - arguably - the ?_error_* fields). Log the transaction xid mis-match as a warning in the log file as opposed to an error.
>>
>> Agreed, I think it's better to log a warning than to raise an error.
>> In the case where the user specified the wrong XID, the worker should
>> fail again due to the same error.
>>
>
> If it remains possible for the system to accept a wrongly specified XID I would agree that this behavior is preferable. At least when the user wonders why the skip didn't work and they are seeing the same error again they will have a log entry warning telling them their XID choice was incorrect.

Yes.

> I would prefer that the system not accept a wrongly specified XID and the user be told directly and sooner that their XID choice was incorrect.

Given that we cannot use rely on the pg_stat_subscription_workers view
for this purpose, we would need either a new sub-system that tracks
each logical replication status so the system can set the error XID to
subskipxid, or to wait for shared-memory based stats collector. While
agreeing that ideally, we need such a sub-system I'm concerned that
everyone will agree to add complexity for this feature. That having
been said, if there is a significant need for it, we can implement it
as an improvement.

Regards,

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