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 : ERROR: integer out of range. The error also mentions a
PL/pgSQL function whose name ends with
"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.
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
SELECT last_value FROM salesforce._trigger_log_id_seq;
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.
- Please stop making changes to the mapped database tables and wait for the
_trigger_logtable to contain no entries in the
BULKSENTstates. You can view a breakdown of your trigger log states with this SQL query:
SELECT state, COUNT(*) FROM salesforce._trigger_log GROUP BY state;
- And then pause the connection.
Once this is done, You would need to move the data from the
_trigger_log to the
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.
- Move the data from the
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;
- Run the following SQL command to reset the
idsequence to 1:
ALTER SEQUENCE salesforce._trigger_log_id_seq RESTART WITH 1
- 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.