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.
- Please stop making changes to the mapped database tables and wait for the
_trigger_log
table to contain no entries in theNEW
,PENDING
, orBULKSENT
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;
- 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.
- 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;
- Run the following SQL command to reset the
id
sequence 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.