"satellite-maintain content prepare" fails with `PG::NumericValueOutOfRange: ERROR: integer out of range` error

Solution Verified - Updated

Environment

Red Hat Satellite 6.9

Issue

When executing "reimport_all=true satellite-maintain content prepare"

ForemanTasks::TaskError: Task ef140a11-4068-4593-be24-b4c25fa99ced: ActiveRecord::RangeError: PG::NumericValueOutOfRange: ERROR:  integer out of range

Resolution

The easiest way forward, is to restart the sequence using the max value already set on the table. Based on table data, that value is still not exhausting the integer range.

[root@rhnsat ~]# su - postgres -c "psql -d foreman -c 'select MAX(id) from katello_repository_errata;'"
    max
 2124433709

Between the current max (2124433709) and the possible max acceptable value (2147483647) there are still 23049938 numbers, which is quite a lot.

  1. Check the current max(ID) on the table katello_repository_errata:
echo "select max(id) from katello_repository_errata"|su - postgres -c 'psql foreman'
  1. Restart the sequence using the max(id) + 1. Assuming you still got 2124433709 as result from the max, use 2124433710 to restart:
echo "alter sequence katello_repository_errata_id_seq restart with 2124433710" |su - postgres -c 'psql foreman'
  1. Restart all services to ensure the sequence doesn't keep a cache entry (by default, the next value is kept on cache and restart it won't clean the cache):
foreman-maintain service restart
  1. Try again the syncs that were failing with the error PG::NumericValueOutOfRange: ERROR: integer out of range.

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

Root Cause

We shouldn't have a sequence using bigint to control an ID field that is integer.

When inserting new data, the ID is calculated by the sequence and then the it tries do insert that value as ID. But it's out of range and then fails.

The data_type for this same sequence got defined to "bigint" after an update to 6.9.10. Somehow the same thing happened here.

Diagnostic Steps

The table katello_repository_errata is defined like this:

# \d katello_repository_errata
                                            Table "public.katello_repository_errata"
       Column       |            Type             | Collation | Nullable |                        Default                        
--------------------+-----------------------------+-----------+----------+-------------------------------------------------------
 id                 | integer                     |           | not null | nextval('katello_repository_errata_id_seq'::regclass)
 erratum_id         | integer                     |           | not null | 
 repository_id      | integer                     |           |          | 
 created_at         | timestamp without time zone |           |          | 
 updated_at         | timestamp without time zone |           |          | 
 erratum_pulp3_href | character varying           |           |          | 
Indexes:
    "katello_repository_errata_pkey" PRIMARY KEY, btree (id)
    "index_katello_repository_errata_on_erratum_id_and_repo_id" UNIQUE, btree (erratum_id, repository_id)
    "index_katello_repository_errata_on_erratum_pulp3_href" btree (erratum_pulp3_href)
    "index_katello_repository_errata_on_repository_id" btree (repository_id)
Foreign-key constraints:
    "katello_repository_errata_errata_id_fk" FOREIGN KEY (erratum_id) REFERENCES katello_errata(id)
    "katello_repository_errata_repo_id_fk" FOREIGN KEY (repository_id) REFERENCES katello_repositories(id)

Note the that the column "id" is integer and the default is calculated using the sequence "katello_repository_errata_id_seq". An integer column accept values in the range of 1 and 2147483647.

Now, looking at the sequence katello_repository_errata_id_seq:

$ grep -e last_value -e katello_repository_errata_id_seq 0050-pg_sequences.txt 
 schemaname |                         sequencename                          | sequenceowner | data_type | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
 public     | katello_repository_errata_id_seq                              | foreman       | bigint    |           1 |         1 | 9223372036854775807 |            1 | f     |          1 | 2147484128

Note the type "bigint". A bigint accept values between 1 and 9223372036854775807. Also note the "last_value" defined on the sequence being 2147484128, which is outside of the valid range for an integer. And this is our problem.

SBR
Product(s)
Components

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.