I am running 3.9.17 with 2 master postgres nodes, and testing conflict resolution. We have configured conflicts to be NEWER_WINS and USE_TIMESTAMP. Ping back is OFF because if I understand correctly I don't need it for this scenario.
I'm testing the case that the nodes become disconnected for a while (anywhere from an hour to a day) and each node modifies the same 2 rows. For row1, node1 edits last. For row2, node2 edits last. When the network outage is fixed, I'd expect 4 outgoing batches to finish sending, 2 conflicts to be resolved, and row1 to have node1's data on each node, and row2 to have node2's data on each node.
What I'm seeing most of the time instead is that node2's data ends up on each node for both row, even though node2 edited row1 first, not last. I say most of the time because sometimes it's the opposite node, other times it works as expected, so I think I've encountered a race condition.
Does all of this make sense? I can list my actual SQL steps out my steps or provide configuration details if that will help.
Queued batches that only involve a single row work as expected, it's when each node has a queued batch that should win the conflict that I see issues.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
For node1 resolving the conflict, it receives a batch from node2 with an earlier timestamp than what is currently in node1. It doesn't need to to ping the resolution of this conflict to node2, because node1 had a pending batch that it can now send to node2, which node2 will happily load since it has the most current timestamp.
I believe this is exactly what's happening when node1 resolves the conflict, but when node2 resolves a conlict for a different row with with opposite-order timestamps, something strange happens and the older data is not overwitten by the newer data.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
For detect_type of USE_TIMESTAMP, you need to specify detect_expression with the column name that contains a timestamp. It currently expects to use a timestamp column on the table you are syncing.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sorry for taking so long to get back to this, since it was intermittent I couldn't devote that much time compared to other tasks.
I tried setting ping back to different values, it didn't seem to affect the behavior much though.
After debugging symmetric ds I noticed the issue is in FormatUtils.java . It uses SimpleDateFormat. The 'S' pattern represents milliseconds. 'SS', and 'SSS' which are used in FormatUtils are invalid, although it doesn't seem to throw and exception. To be clear, S will parse 456 to mean 456ms, and it will parse 23456 to mean 23sec, 456ms.
That parsing makes it incompatible with the 'US' microsecond pattern (see postgres docs ) in PostgreSqlDmlStatement and PostgreSqlDmlStatement.
I was able to modify the jars to us MS instead of US and limit my timestamp column to only 3 decimals. I think it's better to fix the usage of simple date format, but it doesn't seem like it has microsecond support.
Should I be creating an issue on the tracker for this? If it gets fixed, will it be on 3.9 or will I have to update?
Thanks in advance
Last edit: Jack Mueller 2019-08-02
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am running 3.9.17 with 2 master postgres nodes, and testing conflict resolution. We have configured conflicts to be NEWER_WINS and USE_TIMESTAMP. Ping back is OFF because if I understand correctly I don't need it for this scenario.
I'm testing the case that the nodes become disconnected for a while (anywhere from an hour to a day) and each node modifies the same 2 rows. For row1, node1 edits last. For row2, node2 edits last. When the network outage is fixed, I'd expect 4 outgoing batches to finish sending, 2 conflicts to be resolved, and row1 to have node1's data on each node, and row2 to have node2's data on each node.
What I'm seeing most of the time instead is that node2's data ends up on each node for both row, even though node2 edited row1 first, not last. I say most of the time because sometimes it's the opposite node, other times it works as expected, so I think I've encountered a race condition.
Does all of this make sense? I can list my actual SQL steps out my steps or provide configuration details if that will help.
Queued batches that only involve a single row work as expected, it's when each node has a queued batch that should win the conflict that I see issues.
I think you want sym_conflict.ping_back = 'SINGLE_ROW' so it will send the winning row back to the loser.
For node1 resolving the conflict, it receives a batch from node2 with an earlier timestamp than what is currently in node1. It doesn't need to to ping the resolution of this conflict to node2, because node1 had a pending batch that it can now send to node2, which node2 will happily load since it has the most current timestamp.
I believe this is exactly what's happening when node1 resolves the conflict, but when node2 resolves a conlict for a different row with with opposite-order timestamps, something strange happens and the older data is not overwitten by the newer data.
I can still change my configuration to use SINGLE_ROW and post the results later today, since I haven't used that configuration in a while.
For detect_type of USE_TIMESTAMP, you need to specify detect_expression with the column name that contains a timestamp. It currently expects to use a timestamp column on the table you are syncing.
I have that. The column is called conflict_timestamp and I make sure to set it to current_timestamp whenever I do an update or insert.
Sorry for taking so long to get back to this, since it was intermittent I couldn't devote that much time compared to other tasks.
I tried setting ping back to different values, it didn't seem to affect the behavior much though.
After debugging symmetric ds I noticed the issue is in FormatUtils.java . It uses SimpleDateFormat. The 'S' pattern represents milliseconds. 'SS', and 'SSS' which are used in FormatUtils are invalid, although it doesn't seem to throw and exception. To be clear, S will parse 456 to mean 456ms, and it will parse 23456 to mean 23sec, 456ms.
That parsing makes it incompatible with the 'US' microsecond pattern (see postgres docs ) in PostgreSqlDmlStatement and PostgreSqlDmlStatement.
I was able to modify the jars to us MS instead of US and limit my timestamp column to only 3 decimals. I think it's better to fix the usage of simple date format, but it doesn't seem like it has microsecond support.
Should I be creating an issue on the tracker for this? If it gets fixed, will it be on 3.9 or will I have to update?
Thanks in advance
Last edit: Jack Mueller 2019-08-02