Spock Multi-Master: spock.resolutions Filling with update_update Despite Single-Writer Setup

[Rammurthy5]

Environment

• Self-hosted multi-master setup on EC2
• Logical replication using Spock
• Snowflake-based sequences (using sequence_convert)
• Application writes only to n1

---

Setup Steps

1. Stopped live traffic.

2. Took pg_dump (schema + data) from existing cluster.

3. On n1:

   • Created Spock node n1.
   • Ran sequence_convert for Snowflake-based sequences.
   • Added all tables in my schema to the default replication set.

4. Restored dump to n2.

5. On n2:

   • Created Spock node n2.
   • Ran sequence_convert for Snowflake-based sequences.
   • Added all tables in my schema to the default replication set.

6. Created subscription only on n2 (since the application writes only to n1).

7. Resumed live traffic.

---

Issue

After resuming traffic:

• spock.resolutions keeps accumulating entries.
• Conflict type is consistently: update_update.

I have:

• Repeated the entire process multiple times.
• Performed clean restores.
• Recreated nodes and subscriptions from scratch.

However, the update_update conflicts continue to pile up and do not resolve.

---

Expected Behavior

Since the application writes only to n1, I expect no update_update conflicts after replication to n2.

---

Questions

• What could cause persistent update_update conflicts in this setup?
• Is there any known issue with sequence_convert and Snowflake-style sequences in a multi-master configuration?
• Are there additional steps required to prevent these conflicts when only one node is actively written to?

---

• Spock version 25.5
• PostgreSQL version 16.10
• Subscription creation command
  ./pgedge spock sub-create sub_n2n1 'host= port=5432 user=pgedge dbname='

[rokoshy]

@Rammurthy5 the root cause is similar to the issue i had raised related to absence of originID and how spock 5 changed its resolution tracking behaviour
#349

for your use case you have a way out unlike mine , you can start the subscription from n2 to n1 then restore the content on n1 , ie change the sequence/order first subscription then restore on n1
It will replay in few minutes the entire dump into the n2 and now n2 knows that origination of source is n1 and not self ( n2)

[Rammurthy5]

Hi @rokoshy , sorry i dont follow you. could you elaborate a bit more on what i try to get this fixed pls?

[rokoshy]

Hi @rokoshy , sorry i dont follow you. could you elaborate a bit more on what i try to get this fixed pls?

I meant if you do :
create spock node n1
create spock node n2
Created subscription only on n2 [edit as @danolivo pointed out] which auto sync data from n1
then convert

[danolivo]

However, the update_update conflicts continue to pile up and do not resolve.

You raised a quite interesting issue: should we consider tuples that don't have originId and/or Timestamp to be 'frozen' tuples? I suppose it is a correct assumption. Thanks for picking it up.
BTW, why do you use pg_dump instead of using conventional tools like synchronize_structure/synchronize_data options of the spock.sub_create routine? For example, I use kinda the following command to create a subscription on a fresh and clean node:

SELECT spock.sub_create(
  subscription_name := 'n1_n2',
  provider_dsn := 'port=5432',
  synchronize_structure := true,
  synchronize_data := true
);

It may resolve your problem with conflict resolutions.

Also, I'd propose to try a more advanced tool 'Z0DAN' to add a new node (even under load):
https://docs.pgedge.com/spock-v5/development/modify/zodan/

[Rammurthy5]

@danolivo @rokoshy
i tried fresh n2 added to cluster with synchronize_data and sub created only n2. it did not help.

thanks for sharing about ZODAN. i will have a look.

[danolivo]

@danolivo @rokoshy i tried fresh n2 added to cluster with synchronize_data and sub created only n2. it did not help.

Could you provide an exact reproduction script (e.g., based on pgbench)?
I do exactly the same (if I understand your case correctly), and there are no issues with conflict resolution.

[rokoshy]

@danolivo the issue is that even with synchronize_data internally also does similar b&r after which n2 has no idea that the originID of the rows actually belonged to n1 (and not itself). So any write to n1 , n2 is assumes that it belong to itself and marking it as conflict on n2 my issue also reflects the same : #349

[danolivo]

the issue is that even with synchronize_data internally also does similar ...

@rokoshy , sorry for the mess - I answered @Rammurthy5 . Spock tablesync (triggered by the synchronize_data parameter) adds the proper origin ID and timestamp, so updates from the remote node should be resolved correctly (based on the timestamp value).

If your case differs, please provide a script to ensure we are aligned. I think pgbench could reproduce the issue without exposing your data.

[Rammurthy5]

@danolivo
I dropped the Spock node and subscription on n2.
Stopped live traffic
Truncated the tables and schema on n2
Recreated the Spock node on n2
Recreated the subscription:

SELECT spock.sub_create(
    subscription_name := 'sub_n2n1',
    provider_dsn := 'host=x.x.x.x dbname=db_na user=pgedge',
    replication_sets := ARRAY['default'],
    synchronize_structure := true,
    synchronize_data := true
);

The subscription status moved to replicating and approximately 40 million records were copied within a few minutes. At that point, spock.resolutions was 0.

I then turned live traffic back on, with all reads and writes directed only to n1.

seconds after, spock.resolutions increased from 0 to 4K.

[rokoshy]

@danolivo what @Rammurthy5 explained is exactly similar situation on mine after the above step he has explained :

synchronize_structure := true, + synchronize_data := true is not able to send the right metadata back to subscriber about the origin . There is a bug in 16.10 + spock 5.0.4

[danolivo]

@rokoshy , thanks, that makes sense! I use the main branch where this issue is not detected.