Pglogical 2.1.1 CONFLICT: remote UPDATE on relation ... DETAIL: existing local tuple

[flykent]

i'm using version pglogical 2.1.1 to deploy the model 2 node postgres provider -> subscribe

When the provider that runs 1 dml updates an existing row, subscribe appears as an error :

LOG:  CONFLICT: remote UPDATE on relation sync.my_sync (local index pm_productexpiredate_pkey).Resolution: apply_remote.

DETAIL:  existing local tuple {storeid[int4]:2199 productid[bpchar]:8935049510789        dateexpire[date]:2018-04-18 ischeck[bool]:f datecheck[timestamp]:(null) stockquantity[float4]:0 createddate[timestamp]:2018-02-26 08:35:33.283609 customerid[int4]:(null)} xid=1025115589,origin=0,timestamp=2018-02-28 10:48:09.317663+07; remote tuple {storeid[int4]:2199 productid[bpchar]:8935049510789        dateexpire[date]:2018-04-18 ischeck[bool]:t datecheck[timestamp]:2018-03-02 10:06:35.467392 stockquantity[float4]:18 createddate[timestamp]:2018-02-26 08:35:33.283609 customerid[int4]:(null)} in xact origin=1,timestamp=2018-03-02 10:06:35.471171+07,commit_lsn=0/9D278258

CONTEXT:  apply UPDATE from remote relation sync.my_sync in commit before 3515/9D278258, xid 1033489516 commited at 2018-03-02 10:06:35.471171+07 (action #2) from node replorigin 1

Let me know what's going on

Thanks All

[elernonelma]

Do you had any news about that? Got the same issue with the 2.2.0 release.

[ringerc]

Haven't had time to look yet, there's a great deal going on and priorities are spread a bit thin. A test case SQL script would be handy.

[elernonelma]

Thx for the reply. I have exactly the same issue but I have no idea where it comes from... can't really reproduce it mounting a new database...

[derkan]

Same problem here.

2019-10-01 18:47:36.044 +03 [517] LOG:  CONFLICT: remote UPDATE on relation public.instances (local index instances_pkey). Resolution: apply_remote.

2019-10-01 18:47:36.044 +03 [517] DETAIL:  existing local tuple {instance[varchar]:1 checked_on[timestamp]:2019-10-01 18:47:35.937921 is_stopped[bool]:f} xid=273175094,origin=0,timestamp=2019-10-01 18:47:35.942601+03; remote tuple {instance[varchar]:1 checked_on[timestamp]:2019-10-01 18:47:36.037363 is_stopped[bool]:f} in xact origin=1,timestamp=2019-10-01 18:47:36.043864+03,commit_lsn=0/27E73208

2019-10-01 18:47:36.044 +03 [517] CONTEXT:  apply UPDATE from remote relation public.instances in commit before 1C/27E73208, xid 113631981 committed at 2019-10-01 18:47:36.043864+03 (action #2) from node replorigin 1

Version Info:

Installed Packages
Name        : postgresql11-pglogical
Arch        : x86_64
Version     : 2.2.2
Release     : 1.el7

[paulbandler]

Was this issue ever resolved - as I'm experiencing something very similar. I've narrowed it down to only occurring when using certain key value prefixes of the tables in question - strange but apparently true...

[lucasbaronio]

Hi @paulbandler, what did you mean with "using certain key value prefixes of the tables in question"?

Is there any news about this issue? Same problem.

LOG: CONFLICT: remote UPDATE on relation public.usuario (local index usuario_pkey). Resolution: apply_remote.

DETAIL: existing local tuple {.............} xid=144018,origin=3,timestamp=2021-08-04 12:44:33.853697+00; remote tuple {.............} in xact origin=2,timestamp=2021-08-04 14:51:22.676057+00,commit_lsn=0/A69FD8F0

CONTEXT: apply UPDATE from remote relation public.usuario in commit before 1B2/A69FD8F0, xid 498907199 committed at 2021-08-04 14:51:22.676057+00 (action #2) from node replorigin 2

Version Info:

Provider:

• PostgreSQL v9.6.19
• Pglogical v 2.3.4

Suscribe:

• PostgreSQL v12.7
• Pglogical v 2.3.2

[mjuvette]

Hello guys, I am having the same issues but I do not know what is going on. I have a one provider--> subscriber node but I see this error in the logs on the subscriber logs. When I do a count on a table I see the count on subscriber smaller than provider.please anyone has an idea?
2022-05-16 08:53:53.118 EDT,,"postgres",26305,,627d4d0e.66c1,12142757,"",2022-05-12 14:08:14 EDT,7/74036942,0,LOG,23000,"CONFLICT: remote UPDATE on relation xx.table_name replica identity index pk_rx(tuple not found). Resolution: skip.","remote tuple {rxid[int8]:83592384

[WalissonPires]

Hello guys, I am having the same issues but I do not know what is going on. I have a one provider--> subscriber node but I see this error in the logs on the subscriber logs. When I do a count on a table I see the count on subscriber smaller than provider.please anyone has an idea? 2022-05-16 08:53:53.118 EDT,,"postgres",26305,,627d4d0e.66c1,12142757,"",2022-05-12 14:08:14 EDT,7/74036942,0,LOG,23000,"CONFLICT: remote UPDATE on relation xx.table_name replica identity index pk_rx(tuple not found). Resolution: skip.","remote tuple {rxid[int8]:83592384

I have the same problem. Update a record at the provider that doesn't exist at the subscriber (for some reason). Is there any way to configure pglogical to download missing records?

[gkachru]

Have a similar issue on multiple tables. All have primary keys.

Interestingly, everything was working fine for a week, however we had to increase CPU cores on a system and after the reboot this started happening and the replication stopped.

Clocks on both systems are synchronized.

pglogical apply 1889269:4258465055_error:  CONFLICT: remote UPDATE on relation public.my_table_name. Resolution: apply_remote.
pglogical apply 1889269:4258465055_detail:  existing local tuple ...

Setup on both provider and subscriber:

Postgresql: 14.6
Pglogical: 2.4.2
track_commit_timestamp = true
conflict_resolution = 'last_update_wins'

Have changed to the following setting and replication is working again:

conflict_resolution = 'error'
use_spi = true

I don't know why using SPI works -- but it does. Tried switching back to earlier settings and it works for some time and then gives a similar error and replication stops.

However, after turning on use_spi we started to see continuous warnings in the log (Snapshot still active). Have logged a separate issue for this. #404

[luss]

I have similar experience with that "CONFLICT" message. I believe on the latest version of pg_logical you get that "error" every time an UPDATE is applied on a subscribing server, even though there is no conflict (in my way of thinkn ing)_.

…

On Wed, Nov 30, 2022 at 3:05 AM Gautam Kachru ***@***.***> wrote: Have a similar issue on multiple tables. All have primary keys. Interestingly, everything was working fine for a week, however we had to increase CPU cores on a system and after the reboot this started happening. Clocks on both systems are synchronized. pglogical apply 1889269:4258465055_error: CONFLICT: remote UPDATE on relation public.my_table_name. Resolution: apply_remote. pglogical apply 1889269:4258465055_detail: existing local tuple ... Setup on both provider and subscriber: Postgresql: 14.6 Pglogical: 2.4.2 track_commit_timestamp = true conflict_resolution = 'last_update_wins' Have changed to the following setting and replication is working again: conflict_resolution = 'error' use_spi = true However, after the above changes we started to see continuous warnings in the log (Snapshot still active). Have logged a separate issue for this. #404 <#404> — Reply to this email directly, view it on GitHub <#150 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMWOHUMFXCOJ6HCBOVICQ3WK4DFBANCNFSM4ETFRFFA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[dusatvoj]

Still relevant in 2.4.4

oid   |  extname  | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
---------+-----------+----------+--------------+----------------+------------+-----------+--------------
 4728676 | pglogical |       10 |      4728675 | f              | 2.4.4      |           |

---

2024-08-05 03:31:29 CEST [1188692]: db=replica,user=[unknown],app=pglogical apply 4211453:121248605,client= ERROR:  unknown column name worklogstate
2024-08-05 03:31:29 CEST [1188692]: db=replica,user=[unknown],app=pglogical apply 4211453:121248605,client= CONTEXT:  apply UPDATE in commit before 1559/F50A5020, xid 24273469 committed at 2024-08-05 03:31:25.66357+02 (action #2) from node replorigin 1
2024-08-05 03:31:29 CEST [1188692]: db=replica,user=[unknown],app=pglogical apply 4211453:121248605,client= LOG:  apply worker [1188692] at slot 3 generation 1 exiting with error
2024-08-05 03:31:29 CEST [1164297]: db=,user=,app=,client= LOG:  background worker "pglogical apply 4211453:121248605" (PID 1188692) exited with exit code 1

[dusatvoj]

Also crashed with

conflict_resolution = 'error'
use_spi = true

---

Now I'm stuck in the state:

• Deploy new version of application which causes some update of column
• Replication fails with this error and stops working.
• I have to rebuild the whole replication 🔥