Re: Infinite replication loop with pglogical

On 2017-12-07 14:41:31 +0100, Peter J. Holzer wrote:
> We are testing bidirectional replication with pglogical:
[...]
> We have configured bidirectional replication on several tables. In
> general that seems to work fine. When a row is inserted/updated/
> deleted on one node, it is replicated to the other.
>
> But now I seem to have triggered a replication loop:
[...]
> Subsequent selects randomly return one of three states for this row
> (other rows are stable). The order above is the "logical order", i.e.
> the row was first inserted with the first state, then updated to the
> second, and finally to the third.
>
> I suspect that the updates are bounced between the two nodes eternally
> overwriting each other and never reaching a stable state.
>
> Has anybody seen this? If so, is there a way to reliably avoid this?
> (Maybe with a different pglogical.conflict_resolution setting, but if it
> was a conflict, I should see something in the logs, right?)

This paragraph from
https://github.com/2ndQuadrant/pglogical/blob/master/README.md#subscription-management
looks relevant:

| * forward_origins - array of origin names to forward, currently only
| supported values are empty array meaning don't forward any changes
| that didn't originate on provider node (this is useful for two-way
| replication between the nodes), or "{all}" which means replicate all
| changes no matter what is their origin, default is "{all}"

I didn't change the default, so that means that each change is
replicated back to the originator. I guess that doesn't matter if it
happens before the next change (then the change is essentially a noop
and won't be replicated again), but it does trigger a loop if the row
has changed (because then it is changed again, which means that the
previous change will trigger another replication, and so on.)

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>