ERROR: nextval: reached maximum value of sequence "katello_erratum_packages_id_seq" while synchronizing repositories in Red Hat Satellite 6

Solution Verified - Updated

Environment

  • Red Hat Satellite
    • 6.14
    • 6.15
    • 6.16
    • 6.17

Issue

  • Attempt to sync several repositories at once or initiating different sync plans concurrently, results in repository sync failures with the following error:

    ERROR:  nextval: reached maximum value of sequence "katello_erratum_packages_id_seq" (2147483647)

Resolution

  • This issue has been reported to the Red Hat Satellite Engineering team via This content is not included.SAT-34624 bug and is being actively investigated. Refer to the root cause section to understand more about the source of the problems

  • To fix the issue manually on the affected Red Hat Satellite server, proceed with the following steps:

    1. Ensure that a backup or VM snapshot exists for the affected Red Hat Satellite server, before proceeding further.

    2. Clear any paused tasks that might be present.

      # foreman-rake foreman_tasks:cleanup TASK_SEARCH='label ~ *' STATES='paused' VERBOSE=true

    3. Check the max_value and last_value of katello_erratum_packages_id_seq sequence in foreman database. They are expected to be the same at this point.

      # echo "select * from pg_sequences where sequencename='katello_erratum_packages_id_seq';" | su - postgres -c "psql -x foreman"

    4. Fix the issue:

      # echo "ALTER SEQUENCE katello_erratum_packages_id_seq AS bigint;" | su - postgres -c "psql foreman"
# echo "ALTER TABLE katello_erratum_packages ALTER COLUMN id TYPE BIGINT;" | su - postgres -c "psql foreman"

    5. Verify that max_value is now much higher than last_value for katello_erratum_packages_id_seq sequence and its data_type is set as bigint

      # echo "select * from pg_sequences where sequencename='katello_erratum_packages_id_seq';" | su - postgres -c "psql -x foreman"

    6. Restart the services.

      # satellite-maintain service restart
# sleep 20 & hammer ping

  • Once done with all the steps and the satellite instance is back up and running, retry executing the repository sync or sync-plans and observe the progress.

  • Reach out to Red Hat Technical Support in case of any further questions or concerns.

For more KB articles/solutions related to Red Hat Satellite 6.x PostgreSQL-related Issues, please refer to the Consolidated Troubleshooting Article for Red Hat Satellite 6.x PostgreSQL-related Issues

For more KB articles/solutions related to Red Hat Satellite 6.x Repository Issues, please refer to the Red Hat Satellite Consolidated Troubleshooting Article for Red Hat Satellite 6.x Repository Issues.

Root Cause

  • As it can be observed in the Diagnostic Steps section,

    • The id column of katello_erratum_packages table is of integer data type and depends on katello_erratum_packages_id_seq.
    • The katello_erratum_packages_id_seq itself also has the data type set to integer due to which the value of the max_value field is limited to 2147483647 and the last_value field has reached that specific limit.

  • When repository sync happens concurrently and then sync is downloading additional packages and errata from the repositories, the satellite would try to insert more records in katello_erratum_packages table but due to the concern above, it would be impossible to do that and hence the error reached maximum value of sequence.

Diagnostic Steps

  • The repository sync plan execution fails with the following traceback in the /var/log/foreman/production.log. 

    2025-05-30T04:10:03 [I|bac|36314e55] Task {label: Actions::Katello::SyncPlan::Run, id: c594bbf4-eb1b-4700-8a42-a471aff02400, execution_plan_id: 9564a830-4f85-449a-9757-3856e940ccd0} state changed: running 
..
2025-05-30T04:10:07 [I|bac|36314e55] Task {label: Actions::Katello::Repository::Sync, id: c5be306d-5eae-4b2a-9cc0-a91a7a20cc13, execution_plan_id: 083c509f-45e6-4b5c-9279-06b7113fd219} state changed: running 
..
2025-05-30T04:11:06 [I|aud|36314e55] Katello::Repository (364) update event on publication_href /pulp/api/v3/publications/rpm/rpm/01971b4f-79b9-7946-bc05-a2f4f93a46dc/, /pulp/api/v3/publications/rpm/rpm/01972075-b2f7-73f5-b949-ec59575c004d/
2025-05-30T04:11:06 [I|aud|36314e55] Katello::Repository (364) update event on last_contents_changed 2025-05-29 04:11:20 -0500, 2025-05-30 04:11:06 -0500
2025-05-30T04:11:06 [I|aud|36314e55] Katello::Repository (364) update event on version_href /pulp/api/v3/repositories/rpm/rpm/018ff440-4cf0-7f63-85a8-df450db769d0/versions/115/, /pulp/api/v3/repositories/rpm/rpm/018ff440-4cf0-7f63-85a8-df450db769d0/versions/116/
..
2025-05-30T04:11:18 [I|app|36314e55] CONTENT_INDEX type=Katello::Rpm success=true duration=22159.28
2025-05-30T04:11:19 [I|app|36314e55] CONTENT_INDEX type=Katello::ModuleStream success=true duration=1010.92
2025-05-30T04:11:20 [I|app|36314e55] CONTENT_INDEX type=Katello::Rpm success=true duration=19924.05
2025-05-30T04:11:20 [I|app|36314e55] CONTENT_INDEX type=Katello::ModuleStream success=true duration=139.36
2025-05-30T04:11:33 [I|app|36314e55] CONTENT_INDEX type=Katello::Erratum success=false duration=13777.04
2025-05-30T04:11:33 [E|bac|36314e55] PG::SequenceGeneratorLimitExceeded: ERROR:  nextval: reached maximum value of sequence "katello_erratum_packages_id_seq" (2147483647)
 36314e55 |  (ActiveRecord::StatementInvalid)
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `exec_params'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql_adapter.rb:672:in `block (2 levels) in exec_no_cache'
 36314e55 | /usr/share/gems/gems/activesupport-6.1.7.7/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
 36314e55 | /usr/share/gems/gems/activesupport-6.1.7.7/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
 36314e55 | /usr/share/gems/gems/activesupport-6.1.7.7/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql_adapter.rb:671:in `block in exec_no_cache'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract_adapter.rb:696:in `block (2 levels) in log'
 36314e55 | /usr/share/gems/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 levels) in synchronize'
 36314e55 | /usr/share/gems/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
 36314e55 | /usr/share/gems/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
 36314e55 | /usr/share/gems/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interrupt'
 36314e55 | /usr/share/gems/gems/activesupport-6.1.7.7/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract_adapter.rb:695:in `block in log'
 36314e55 | /usr/share/gems/gems/activesupport-6.1.7.7/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract_adapter.rb:687:in `log'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql_adapter.rb:670:in `exec_no_cache'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql_adapter.rb:649:in `execute_and_clear'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/postgresql/database_statements.rb:55:in `exec_query'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/database_statements.rb:154:in `exec_insert_all'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/connection_adapters/abstract/query_cache.rb:22:in `exec_insert_all'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/insert_all.rb:35:in `execute'
 36314e55 | /usr/share/gems/gems/activerecord-6.1.7.7/lib/active_record/persistence.rb:124:in `insert_all'
 36314e55 | /usr/share/gems/gems/katello-4.11.0.20/app/services/katello/pulp3/erratum.rb:64:in `insert_child_associations'
..

  • Inspecting the sequence and table in concern, the following can be found in the foreman database. 

        # echo "select * from pg_sequences where sequencename='katello_erratum_packages_id_seq';" | su - postgres -c "psql -x foreman"
    -[ RECORD 1 ]-+--------------------------------
    schemaname    | public
    sequencename  | katello_erratum_packages_id_seq
    sequenceowner | foreman
    data_type     | integer
    start_value   | 1
    min_value     | 1
    max_value     | 2147483647    ------------------> Not large enough value 
    increment_by  | 1
    cycle         | f
    cache_size    | 1
    last_value    | 2147483647    ------------------> same as max_value which means it had reached its limit 

    # echo "\d+ katello_erratum_packages;" | su - postgres -c "psql -x foreman" | grep seq -B1
    ------------+------------------------+-----------+----------+------------------------------------------------------+----------+--------------+-------------
     id         | integer                |           | not null | nextval('katello_erratum_packages_id_seq'::regclass) | plain    |              |
    • It's not expected data_type for the id field or the sequence to be integer as the table can hold a lot of records and should have a large enough max_value (and that can be achieved from bigint data type).
SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.