Last updated November 21, 2022

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. For more information about log errors, see Heroku Connect Log Messages and Common Errors.

View Write Error Details

You can find write error details by looking at the record in the Explorer tab. You can also see them in the _trigger_log table of your database by using heroku pg:psql:

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

Permission Denied

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.

For example, 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.

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 _trigger_log table:

-- 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 NEW state.

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_row for retriggering INSERTs
• hc_capture_update_from_row for retriggering UPDATEs

See the following example use cases for resolving write failures for both INSERT and UPDATEs.

INSERT Failures

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[]
)

The 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 _trigger_log‘s values column. It tells the insert-repair function that it’s for the account table.

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 parent__external_id__c.

UPDATE Failures

Correcting a value resolves many UPDATE failures. For example, if the change made to the name column is too long, update it 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[]
)

The 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.