Last updated November 29, 2022

Heroku Connect is a system designed to be eventually, but not immediately consistent system with Salesforce. With read-write mappings, there’s a chance of Postgres being temporarily out of sync when you make multiple updates to the same field. This behavior is expected and manifests in different ways. This article describes these behaviors and how to prevent them.

“Flip-Flop” Values

A field seems to “flip-flop” as the Postgres value updates. It’s set to a previous value, then a few seconds or minutes later, updates back to its new value:

1. Postgres updates the field in a table. Heroku Connect triggers create an entry in the _trigger_log.
2. Postgres updates the field again. A new entry inserts into the _trigger_log.
3. Connect processes the first _trigger_log entry, updating Salesforce to the value of the Postgres update after step 1.
4. The value from Salesforce syncs to Postgres, setting the field to the value from step 1.
5. The second _trigger_log entry processes, updating the field in Salesforce to the value from step 2.
6. The updated Salesforce value syncs back to Postgres.

Temporarily Reviving Deleted Records

In this scenario:

1. A record is deleted in Postgres.
2. The same record gets updated in Salesforce before the delete from Postgres processes.
3. The Salesforce update causes the record in Postgres to temporarily revive.
4. The sync for the delete occurs, and the record gets deleted in both SFDC and Postgres.

Re-inserted Records

When Using the Merged Writes Algorithm

Even when using unique identifiers, it’s possible to have a deleted record remain if you delete records in Postgres shortly after insertion. This issue can occur when:

1. A record gets created in Postgres.
2. The record is deleted from Postgres, before it’s synced to Salesforce.
3. The insert from step 1 gets processed in the first batch of merged writes, and inserts into Salesforce.
4. The delete from step 2 gets ignored by Heroku Connect because the record didn’t have an sfid at the time of deletion.
5. A poll from Salesforce occurs and the record gets inserted from Salesforce back into Postgres.

While this situation is rare, it’s not recommended to delete records shortly after inserting them to Postgres. Wait until the record is created in Salesforce before deleting it to ensure that the record in Postgres has the sfid value populated. Using Ordered Writes can prevent this issue, however, it can still occur in rare cases.

When Using the Ordered Writes Algorithm

In this scenario:

1. A record gets created in Postgres.
2. Heroku Connect syncs the record to Salesforce.
3. The Heroku Connect service begins to restart before it receives an acknowledgement from Salesforce that it successfully inserted. The trigger log entry for the insert remains as PENDING.
4. The same record gets deleted from Salesforce before Heroku Connect restarts.
5. Heroku Connect restarts and retries the PENDING insert trigger log entry. It detects the record doesn’t exist in Salesforce and reinserts the record.

While this situation is rare, it’s not recommended to delete records in Salesforce shortly after syncing via Heroku Connect. Wait at least 10 minutes before deleting.

Counters Increment or Decrement Incorrectly

Fields used as counters can have their values increment or decrement incorrectly. When the field updates to change the value before the final value is inserted, the final new value is different than expected.

For example:

1. Set view_count = 1 in Heroku Postgres, which inserts an entry into the _trigger_log to capture the change for Heroku Connect to process.
2. Update view_count in Heroku Postgres to view_count + 1, which insert _trigger_log entry #2. The view_count is now 2.
3. Connect processes _trigger_log #1 to write the view_count = 1 update to Salesforce.
4. The value in Salesforce syncs back to Postgres. The view_count in Postgres is 1.
5. Update view_count in Heroku Postgres again to view_count + 1, which inserts _trigger_log entry #3. In this case, view_count never increments to 3 because the increment behavior isn’t idempotent. The final value is 2.

Lost Updates

Given a scenario where you have a record with these starting fields and values: {A: "name", B: true}:

1. Update A in Heroku Postgres to "new name". A _trigger_log entry captures {A: "new name"}. The Heroku Postgres record is now {A: "new name", B: true}.
2. Update B in Heroku Postgres to false. A second _trigger_log entry captures {B: false}. The Postgres record is {A: "new name", B: false}.
3. Connect processes the first _trigger_log entry. The update syncs back to Postgres from Salesforce and the Postgres record is {A: "new name", B: true}. B is incorrect and has “flip-flopped”. If nothing else updates the record, it would eventually update to the correct value.
4. Update B in Heroku Postgres to true. This is a no-op change because B is currently incorrectly true due to step 3. No-op changes don’t result in a _trigger_log entry.
5. Connect processes the second _trigger_log to update B to false in Salesforce.
6. The Salesforce value syncs to Postgres. The final Heroku Postgres record has {A: "new name", B: false}. The update to set B to true is lost.

Avoid or Work Around Rapid Multiple Updates Sync issues

• Accelerated Polling makes it more likely that a temporary state gets pushed into Postgres. Disabling Accelerated Polling on a mapping can make these scenarios less likely.
• Long polling intervals can likewise make these issues less likely to occur.
• If you know that you must perform rapid updates on a record, make those updates in a staging table. Move the record to the mapped table when it’s no longer changing quickly.