Heroku Connect FAQ and Best Practices
Last updated 05 January 2017
Table of Contents
- Which Salesforce objects are supported?
- What does restarting the connection do?
- What does reloading a mapping do?
- Why can’t the SOAP API be used for all Salesforce interactions?
- Why were rows still synchronized after I paused my connection?
- Why aren’t all of my Events or Tasks being synchronized?
- Why is there a difference between the Salesforce and database row counts for my mapping?
- Why are some objects grayed out on the ‘Create mapping’ page?
- What happens to my data if I delete my application?
- How do I change the recipients of notifications?
- Can Heroku Connect sync images from Salesforce?
- How can I get my Salesforce assignment rules to run for Leads and Cases?
- I inserted or updated a record in my database. Why didn’t my change appear in Salesforce?
- Can I retry records that failed to write to Salesforce?
- How should I import large quantities of data into read/write mappings?
- What kind of sync throughput performance can I expect?
- How can I preview new features?
- Does Heroku Connect have an API?
- How can I ensure my own custom Postgres triggers work well with Connect?
Which Salesforce objects are supported?
The Supported Salesforce Standard Objects page lists the standard objects that Heroku Connect supports, along with noted limitations, for a specific version of Salesforce.
What does restarting the connection do?
Restarting a connection is a non-destructive action that allows Heroku Connect to recover from unexpected errors. Processes are restarted and will resume synchronization from the time of the last successful poll.
To restart a connection navigate to the Settings tab, Manage Connection and click the Restart button.
What does reloading a mapping do?
Reloading a mapping is a destructive action that cannot be undone.
Reloading a mapping truncates the mapped table in your Postgres database, removing all existing data, and then reloads all records from Salesforce. The process used to reload the data is the same as that used when a mapping is initially populated and will make use of the SOAP or Bulk API depending on the number of records to be reloaded.
For mappings with a large number of records you should consider putting your application into maintenance mode while the process completes.
If a mapping has entered a
SYSTEM_ERROR state you should try to recover by restarting the connection rather than reloading.
Why can’t the SOAP API be used for all Salesforce interactions?
As SOAP API calls do not count towards your daily usage limits it may seem like a good idea for Heroku Connect to simply use the SOAP API for all interactions with your Salesforce organization.
Unfortunately the SOAP API is not optimized for use with large data volumes: the main differences being that it lacks the asynchronous background processing capabilities of the Bulk API and therefore requires more processing overhead and server round-trips in order to transfer data.
Although the Bulk API is the best choice for processing large data volumes you can take steps to reduce its usage: see Reducing Bulk API calls for more information.
Why were rows still synchronized after I paused my connection?
When you pause synchronization Heroku Connect will finish processing any active synchronization tasks. Any further changes made either in your database or in Salesforce after you pause will not be synchronized until you resume your connection.
Why aren’t all of my Events or Tasks being synchronized?
Event and Task objects can be archived by Salesforce resulting in the
IsArchived flag being set. Unfortunately the Salesforce Bulk API does not allow archived Events and Tasks to be retrieved, which in turn means Heroku Connect cannot synchronize them.
Why is there a difference between the Salesforce and database row counts for my mapping?
Row counts can differ between Salesforce and your database for a number of reasons, including:
- Errors reading or writing data: for example due to validation errors in Salesforce or constraints in your database.
- Records are no longer visible to Heroku Connect: for example archived Events and Tasks.
- The database row count will sometimes be an estimate: to improve performance for mapped tables with a high number of records (in the order of several million) Heroku Connect will not perform a full row count after every sync operation but will instead use an estimation method that can be calculated very quickly.
Why are some objects grayed out on the ‘Create mapping’ page?
The object may not be supported by Heroku Connect (due to Salesforce API limitations), or the object has recently been removed and the removal operation is still underway.
What happens to my data if I delete my application?
Heroku Connect is an application-specific add-on (it cannot be shared): this means that when you delete the application, the add-on and the associated data from the database will also be deleted. If the Heroku Postgres database is attached to other applications then the database itself will not be deleted, but the mapped tables used by Connect will be removed.
How do I change the recipients of notifications?
In the Heroku Connect dashboard navigate to the Settings tab, Manage Connection and check or uncheck the Notifications Enabled box next to the relevant users in the Access list. For more information see Notifications.
Can Heroku Connect sync images from Salesforce?
Heroku Connect does not currently support images or other types of binary data. An alternative approach is to store images in Amazon S3 and sync a simple text field containing a URI for the image between Salesforce and your database.
How can I get my Salesforce assignment rules to run for Leads and Cases?
By default, when Heroku Connect writes data to Salesforce, assignment rules aren’t run. If you’d like to enable your default assignment rules for Leads and Cases, please enter a support ticket. If you have multiple connections, be sure to mention them so that we can enable the setting for each one.
I inserted or updated a record in my database. Why didn’t my change appear in Salesforce?
The most common error that occurs when pushing updates from your database to Salesforce is insufficient write permissions for the target Salesforce org.
You can find more details by looking at the record in the Explorer or the
_trigger_log table in your database. To find errors in the
_trigger_log, you can run this SQL from
SELECT table_name, record_id, action, sf_message from salesforce._trigger_log where state = 'FAILED';
Can I retry records that failed to write to Salesforce?
If an error occurs when synchronizing data to Salesforce the relevant entries in the
_trigger_log system table will be set to a
Once you have investigated the cause of the error it is possible to force Heroku Connect to reprocess these updates by resetting the
state field to
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';
The above example uses the default Postgres schema name of
salesforce - you will need to change this if you are using a different schema. The
table_name field in the
_trigger_log table does not include the schema name.
Trigger log entries older than 24 hours are moved to a
_trigger_log_archive table. If you need to resubmit entries that have been moved to the archive, you can 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';
Heroku Connect periodically runs a 'sweeper’ task to find old
_trigger_log entries with a
NEW state and reprocesses them: this process currently runs every 20 minutes.
An alternative approach for records that have never been successfully written to Salesforce is to query for records with no Salesforce ID and a
FAILED state, for example:
SELECT * FROM salesforce.contact WHERE sfid IS NULL AND _hc_lastop='FAILED';
Updating a field in the mapped table will generate a new entry in the
_trigger_log to insert the record into Salesforce.
How should I import large quantities of data into read/write mappings?
The best option is to import the data directly into Salesforce using a tool such as Data Loader. Connect will sync the imported data to your database using the Bulk API, which is the most efficient way to transfer large volumes of data.
This is a better option than loading the data into your database and allowing Connect to sync it to Salesforce. Connect has to write data to Salesforce in much smaller batches using a slower API. It also has to update each record with a SFID after it has been successfully written to Salesforce. The performance impact is minimal during normal usage, but it’s noticeable when dealing with large volumes of data.
What kind of sync throughput performance can I expect?
Sync performance can vary widely from one connection to another. The best way to determine performance is to test with a production Salesforce org and a production database plan. Specifically, a few factors can affect overall performance:
- Object size can have a big impact on sync performance. Mapping many columns on a particular object causes Salesforce to sync those objects in smaller batches, increasing sync time. To improve performance, try removing columns from your mapping that you don’t need in your database.
- High volumes of changes on either side (Salesforce or database) can also slow synchronization. Connect has to synchronize the entire contents of an object, even if only a small amount of data has changed. For example, if you have a single frequently toggled field on a very large object, try moving that field to a different table to reduce the volume of data that Connect needs to sync.
- Using an ORM that 'touches’ or saves nonexistent changes to records can also cause slowdowns. This will cause a large number of records in your trigger log with a
IGNORED, which slows down the sync process.
- Overall database load can contribute to slower synchronization. Make sure to select a database plan that supports your production traffic alongside Connect’s synchronization queries.
How can I preview new features?
If you want to be able to preview new features before they are GA please contact the team at firstname.lastname@example.org.
Does Heroku Connect have an API?
How can I ensure my own custom Postgres triggers work well with Connect?
Heroku Connect uses Postgres triggers to observe changes in your Connect tables and write your changes to Salesforce. If you implement your own custom triggers on Connect tables, Connect may miss your updates, or you may create additional load.
In its own triggers, Connect makes use of a session variable in Postgres called
xmlbinary to determine whether or not to send updates to Salesforce. This is how Connect prevents its own updates from being sent back to Salesforce and creating an infinite loop. It is possible to set this value in your custom trigger to temporarily enable or disable sending updates to Salesforce. Please proceed at your own risk, and know that Support may not be able to provide help with custom triggers.
A value of
base64 means that row updates will be detected by the Connect triggers and sent to Salesforce. A value of
hex means that updates to database rows will be ignored by the Connect triggers.
Here’s an example for adding a value to an
externalid__c field in a
Contact mapping whenever a new record is inserted into the
contact table. This trigger ensures that even if Connect is writing the row to your database as part of a sync operation, the
externalid__c update will be detected, and the value will sync back to Salesforce:
CREATE OR REPLACE FUNCTION salesforce.contact_external_id_proc() RETURNS TRIGGER AS $$ DECLARE oldxmlbinary varchar; BEGIN -- Save old value oldxmlbinary := get_xmlbinary(); -- Change value base64 to ensure writing to _trigger_log is enabled SET SESSION xmlbinary TO 'base64'; -- This is where you start your custom trigger code. -- Update the external ID UPDATE salesforce.contact SET externalid__c = NEW.id WHERE id = NEW.id; -- This is where you end your custom trigger code. -- Reset the value EXECUTE 'SET SESSION xmlbinary TO ' || oldxmlbinary; RETURN NEW; END; $$ LANGUAGE plpgsql; DROP TRIGGER IF EXISTS contact_after_trigger ON salesforce.contact; CREATE TRIGGER contact_after_trigger AFTER INSERT OR UPDATE ON salesforce.contact FOR EACH ROW WHEN (NEW.externalid__c IS NULL) EXECUTE PROCEDURE salesforce.contact_external_id_proc();
If you’re changing records that are being synced to Salesforce, we strongly recommend using
AFTER INSERT OR UPDATE triggers instead of
BEFORE triggers when possible. When combined with the
xmlbinary flag described above, this ensures that Connect can process its own changes, and the changes you apply in the trigger can be accurately captured in the trigger log.
Multiple triggers (or triggers that execute a lot of code for each change) can have an adverse affect on database performance. If you are updating a large number of records, consider using a statement-level trigger that only has to execute once when possible.