Why can't I make changes to my database that's synced with Salesforce?

Issue

When I attempt to insert, update, or delete records from my Heroku Postgres database, I'm getting an error that looks similar to SQL Error [22003]: ERROR: integer out of range. The error also mentions a PL/pgSQL function whose name ends with _logger().

Or, alternatively:

"nextval: reached maximum value of sequence "_trigger_log_id_seq" (2147483647)".

This is happening consistently for all of my tables that are synced using Heroku Connect, and it started happening suddenly.

Resolution

The likely cause is that the id field of the _trigger_log table that Heroku Connect uses to capture database changes has reached the maximum integer value of 2,147,483,647. You can verify this with the following SQL query, assuming that your Heroku Connect schema is called salesforce:

SELECT last_value FROM salesforce._trigger_log_id_seq;

If the last_value is 2,147,483,647 or greater, you would need to reset the sequence for the id column.

First, you need to ensure that the application and Heroku Connect are NOT making changes to the Connect-related tables.

1. Please stop making changes to the mapped database tables and wait for the _trigger_log table to contain no entries in the NEW, PENDING, or BULKSENT states. You can view a breakdown of your trigger log states with this SQL query: SELECT state, COUNT(*) FROM salesforce._trigger_log GROUP BY state;
2. And then pause the connection.

Once this is done, You would need to move the data from the _trigger_log to the _trigger_log_archive table.

Note: The queries below assume the Heroku Connect tables are using the default salesforce schema. If this is not the case, you'll need to use the right schema in the queries.

3. Move the data from the _trigger_log to the _trigger_log_archive table:

INSERT INTO salesforce._trigger_log_archive (created_at, updated_at, state, txid, table_name, action, record_id, values, old, sfid, sf_result, processed_at, processed_tx, sf_message, id)
  SELECT created_at, updated_at, state, txid, table_name, action, record_id, values, old, sfid, sf_result, processed_at, processed_tx, sf_message, id
  FROM salesforce._trigger_log;

4. Run the following SQL command to reset the id sequence to 1:

ALTER SEQUENCE salesforce._trigger_log_id_seq RESTART WITH 1

5. Once this is done, You can resume the connection and verify that you are able to insert, update, or delete from their database without errors.

Integer overflow for the id column is a rare occurrence, and will only happen after the trigger log records over two billion changes. If it's conceivable that your connection will be this busy, we recommend that you set up an alerting feature that warns you when the id field gets close to two billion. Then you can proactively reset the id sequence as described above before you encounter database write errors.

If you need further help, please reach out to the Support Team for clarifications about this.