Understanding conflicts on publications and subscriptions

Reading this document:
/docs/16/logical-replication-conflicts.html

There is talk of the "disable_on_error" option when creating a subscription.

The conflicts this applies to, I am assuming are only conflicts caused on
the side of the subscription?

As an attempt to apply new data doesn't work, because of modifications made
since the initial copy, is that correct?

I'm a bit confused by errors on the side of the publisher. Reading this
document: /docs/16/sql-createpublication.html

It states:

> The tables added to a publication that publishes UPDATE and/or DELETE operations
must have REPLICA IDENTITY defined. Otherwise those operations will be
disallowed on those tables.

This is not related to the subscription option "disable_on_error", I take
it?

Because it sure would be nice if there was a way to do a similar thing for
the subscription, disabling it on error.

Am I getting this right? "disable_on_error" is only on subscription, and
errors on the publishers related to replica identity are not tied to that?

Thanks for your time.

Just to add a thought:

If the subscriber gets a bit of logic to say "Something went wrong, so I'm
automatically stopping what I'm doing", it sounds logical to give the
publisher the same ability.

On Tue, Jul 30, 2024 at 3:47 PM Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:

> Reading this document:
> /docs/16/logical-replication-conflicts.html
>
> There is talk of the "disable_on_error" option when creating a
> subscription.
>
> The conflicts this applies to, I am assuming are only conflicts caused on
> the side of the subscription?
>
> As an attempt to apply new data doesn't work, because of modifications
> made since the initial copy, is that correct?
>
>
> I'm a bit confused by errors on the side of the publisher. Reading this
> document: /docs/16/sql-createpublication.html
>
> It states:
>
> > The tables added to a publication that publishes UPDATE and/or DELETE operations
> must have REPLICA IDENTITY defined. Otherwise those operations will be
> disallowed on those tables.
>
> This is not related to the subscription option "disable_on_error", I take
> it?
>
> Because it sure would be nice if there was a way to do a similar thing for
> the subscription, disabling it on error.
>
> Am I getting this right? "disable_on_error" is only on subscription, and
> errors on the publishers related to replica identity are not tied to that?
>
> Thanks for your time.
>

On Tuesday, July 30, 2024, Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:
>
> If the subscriber gets a bit of logic to say "Something went wrong, so I'm
> automatically stopping what I'm doing", it sounds logical to give the
> publisher the same ability.
>

The wording for that option is:
Specifies whether the subscription should be automatically disabled if any
errors are detected by subscription workers during data replication from
the publisher.

A subscription worker has no clue what the publisher is doing. It operates
on the “when I see data I act on it” model.

As for whether the publisher should have this clause - the errors in
question are logical, data-oriented, errors, which the publisher is
incapable of having.

I believe what you are effectively requesting is that instead of
disallowing updates and deletes on the added table that lacks replica
identity you wish for the addition itself to fail. That would have made a
better default behavior with an option to override when the current
behavior is desired. But it seems too late to change this decision now.

David J.

David,

By "addition" do you mean "adding the table to the publication"? I suppose
that's an option, though I was more thinking about disabling the
publication if an error occurs, similarly to how a subscription is disabled
if "disable_on_error" is set to true, and an error occurs there.

However, thinking about that is fantasizing, at this point.

My main worry is understanding the behavior as it is. And if my
understanding is correct: if a table doesn't have a replica identity, any
UPDATE or DELETE statement that happens on the publisher, for that table,
will be refused.

Is that correct?

Regards,
Koen

On Tue, Jul 30, 2024 at 4:04 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tuesday, July 30, 2024, Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:
>>
>> If the subscriber gets a bit of logic to say "Something went wrong, so
>> I'm automatically stopping what I'm doing", it sounds logical to give the
>> publisher the same ability.
>>
>
> The wording for that option is:
> Specifies whether the subscription should be automatically disabled if
> any errors are detected by subscription workers during data replication
> from the publisher.
>
> A subscription worker has no clue what the publisher is doing. It
> operates on the “when I see data I act on it” model.
>
> As for whether the publisher should have this clause - the errors in
> question are logical, data-oriented, errors, which the publisher is
> incapable of having.
>
> I believe what you are effectively requesting is that instead of
> disallowing updates and deletes on the added table that lacks replica
> identity you wish for the addition itself to fail. That would have made a
> better default behavior with an option to override when the current
> behavior is desired. But it seems too late to change this decision now.
>
> David J.
>

On Tue, Jul 30, 2024 at 7:16 AM Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:

> And if my understanding is correct: if a table doesn't have a replica
> identity, any UPDATE or DELETE statement that happens on the publisher, for
> that table, will be refused.
>
>
That is how I read the sentence "Otherwise those operations will be
disallowed on those tables."

Upon adding said table to a publication, future attempts to run updates and
deletes will result in failures in the transactions performing said DML.

Feel free to experiment that the behavior indeed matches the wording in the
documentation.

David J.

It indeed seems to be that.

My initial thought of " will be disallowed on those tables" was "on the
subscriber side". After all, why have a publication be of any effect if
there's nobody subscribing to it.

But it appears the publication influences behavior, regardless of there
being a subscriber, which feels counter-intuitive to me.

Thanks for stepping me through it.

On Tue, Jul 30, 2024 at 4:34 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Tue, Jul 30, 2024 at 7:16 AM Koen De Groote <kdg(dot)dev(at)gmail(dot)com> wrote:
>
>> And if my understanding is correct: if a table doesn't have a replica
>> identity, any UPDATE or DELETE statement that happens on the publisher, for
>> that table, will be refused.
>>
>>
> That is how I read the sentence "Otherwise those operations will be
> disallowed on those tables."
>
> Upon adding said table to a publication, future attempts to run updates
> and deletes will result in failures in the transactions performing said DML.
>
> Feel free to experiment that the behavior indeed matches the wording in
> the documentation.
>
> David J.
>
>