Heroku Connect Write Errors
Last updated August 16, 2021
Writes to Salesforce can fail for a number of reasons, including planned maintenance windows. In cases where Salesforce is unavailable, Connect queues writes until it’s available again. In all other cases, resolving errors requires user intervention.
View Write Error Details
SELECT table_name, record_id, action, sf_message FROM salesforce._trigger_log WHERE state = 'FAILED';
When errors occur that require user intervention, Heroku Connect sends notifications to all users that have chosen to receive them. See Errors with Associated Notifications for more info.
Common Write Errors
A common error that indicates the integration user doesn’t have write permission to the object or field in Salesforce. Grant the correct permissions in Salesforce and reprocess the write updates.
duplicate value found
Heroku Connect tried to push a record to Salesforce, but Salesforce wasn’t able to save it.
duplicate value found: Contact_External_ID__c duplicates value on record with id: a006... This error indicates the
Contact_External_ID__c value already exists and must be unique.
A race condition in Heroku Connect can cause this error in some cases. Prevent this error by always adding an External ID when inserting records into the database.
If you have an External ID set, this error is likely due to Connect reprocessing an already committed record. A successful commit can fail to report the success back to Connect, so you can ignore this error in this case.
entity is deleted
Heroku Connect attempted to sync a record to Salesforce, but Salesforce reports that the record has been deleted on their side.
owner is inactive, cannot reparent record
Heroku Connect attempted to sync a record with a relational field pointing to another object, but its parent object is no longer active.
Id not specified in an update call
This error happens a subsequent update is made to a record after a failed insert while using the Ordered Writes algorithm.
Use one of the methods in the When Using the Ordered Writes Algorithm section to resolve the initial insert error and resubmit any subsequent updates.
The Salesforce API documentation includes a full list of error codes and their explanations.
Retry After Resolving Write Errors
After investigating the cause of the error, have Connect reprocess the update. How you retry depends on the write algorithm you’re using.
Retries When Using the Merged Writes Algorithm
Signal Heroku Connect to reprocess updates by resetting the
state field in the trigger log to
NEW. Alternatively, if an INSERT failed, UPDATE a column in the record that failed to write. This change creates a trigger log entry that sends the change to Salesforce as an INSERT.
For example, to retry all failed entries for a mapping named
Contact use the following query:
UPDATE salesforce._trigger_log SET state = 'NEW' WHERE state = 'FAILED' AND table_name = 'contact';
This example uses the default Postgres schema name of
salesforce. Change the name if you’re using a different schema. The
table_name field in the
_trigger_log table doesn’t include the schema name.
Trigger log entries older than 24 hours move to a
_trigger_log_archive table. To resubmit archived entries, you must copy them back to the
-- Set the records you want to retry to a state of 'REQUEUE' UPDATE salesforce._trigger_log_archive SET state='REQUEUE' WHERE state='NEW'; -- or some other criteria like state='FAILED' and table_name = 'account' and sf_message like '%my error message%' -- Copy them to the trigger log table INSERT INTO salesforce._trigger_log (created_at, updated_at, state, txid, table_name, action, record_id, values, old, sfid) SELECT now(), now(), 'NEW', txid, table_name, action, record_id, values, old, sfid FROM salesforce._trigger_log_archive where state='REQUEUE'; -- Update the records still in the archive UPDATE salesforce._trigger_log_archive SET state='REQUEUED' WHERE state='REQUEUE';
It can take up to 20 minutes before Connect finds changes reset to the
Retries When Using the Ordered Writes Algorithm
Sometimes INSERTs or UPDATEs valid in Postgresql aren’t valid when sent to Salesforce. Salesforce can enforce additional rules around referential integrity or data format. Changes can also fail because of trigger-based validations or locking issues.
Review the status of the changes sent to Salesforce regularly by inspecting failures in the trigger log table:
SELECT created_at, table_name, record_id, sfid, sf_message FROM salesforce._trigger_log WHERE state='FAILED';
Updating a value on a record can be enough to correct simpler errors. More complicated changes can require the use of one of the repair functions available in your database.
Heroku Connect provides two PL/PGSQL signatures for retriggering a change on a failed write to Salesforce:
hc_capture_insert_from_rowfor retriggering INSERTs
hc_capture_update_from_rowfor retriggering UPDATEs
See the following example use cases for resolving write failures for both INSERT and UPDATEs.
For INSERT failures, use the insert repair function, which has the following PL/PGSQL signature:
hc_capture_insert_from_row( source_row hstore, table_name varchar, excluded_cols text = ARRAY::text )
excluded_cols parameter is optional. If it’s not set, the function doesn’t exclude any fields.
For example, you can resubmit the record with:
SELECT salesforce.hc_capture_insert_from_row(hstore(salesforce.account.*), 'account') FROM salesforce.account WHERE id = 1234;
This statement converts the entire row to an hstore value to store in your
values column. It tells the insert-repair function that it’s for the
Note You only need the name of the table, not the fully qualified name that includes the schema name.
You can omit parts of the record using the
excluded_cols parameter. For example:
SELECT salesforce.hc_capture_insert_from_row( hstore(salesforce.account.*), 'account', ARRAY['parent__external_id__c']) FROM salesforce.account where id = 1234;
This statement captures all of the columns and values, but excludes the column named
Correcting a value resolves many UPDATE failures. For example, if the change made to the
name column iss too long, update tit with a smaller value and ignore the original failed write:
UPDATE salesforce.account set name='Smaller Name' where id = 1234;
If correcting the value alone can’t resolve the error, use the update repair function. It has the following PL/PGSQL signature:
hc_capture_update_from_row( source_row hstore, table_name varchar, columns_to_include text = ARRAY::text )
columns_to_include parameter is optional. If it’s not set, the function includes all fields.
In some cases, you must requeue an update change. In the example shown in the INSERT failures section, an insert repair excluded
parent__external_id__c. If you want to write an update to then set that value, use the following statement:
SELECT salesforce.hc_capture_update_from_row( hstore(salesforce.account.*), 'account', ARRAY['parent__external_id__c']) FROM salesforce.account where id = 1234;
This statement selects every column and value from the table by default to convert to an hstore value.
You can also filter which parts of a record to update. Use the
columns_to_include parameter to only update certain columns.