[Bug]: Upgrade Version From 2.10.1 To 2.14.2 Failing With Error Constraint Foreign Key on bgw_job_stat tables

[adeifeadekunbi]

What type of bug is this?

Other

What subsystems and features are affected?

Background worker, Other

What happened?

I ran below command to upgrade 2.10.1 version to 2.14.2 sudo yum install timescaledb-2-postgresql-14-2.14.2 -y After successfully run of the above command and restart of postgresql, i ran the the below command psql -X -c "ALTER EXTENSION timescaledb UPDATE" database_name; The above update command failed with error

    ERROR:  insert or update on table "bgw_job_stat" violates foreign key constraint 
  "bgw_job_stat_job_id_fkey"
    DETAIL:  Key (job_id)=(-1) is not present in table "bgw_job".

Further investigation shows that bgw_job_stat table has a job_id = -1 while bgw_job table has no id = -1

    select * from _timescaledb_config.bgw_job where id= -1;
    (0 rows)

select * from _timescaledb_internal.bgw_job_stat where job_id = -1;
-[ RECORD 1 ]-----------+-----------------------
job_id                  | -1
last_start              | -infinity
last_finish             | -infinity
next_start              | 2024-04-04 05:30:00+00
last_successful_finish  | -infinity
last_run_success        | t
total_runs              | 0
total_duration          | 00:00:00
total_duration_failures | 00:00:00
total_successes         | 0
total_failures          | 0
total_crashes           | 0
consecutive_failures    | 0
consecutive_crashes     | 0
flags                   | 0

How can this be resolved?

TimescaleDB version affected

2.10.1

PostgreSQL version used

14.6

What operating system did you use?

Centos 7.9

What installation method did you use?

RPM

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

STEP 1: upgrade timescaledb to version 2.14.2
STEP 2: Restart PostgreSQL
STEP 3: psql -X -c "ALTER EXTENSION timescaledb UPDATE" database_name;

[jnidzwetzki]

Hi @adeifeadekunbi,

Thanks for reaching out. The job -1 should not be used by regular jobs. As you checked, this job is also not configured in your system. So, the table _timescaledb_internal.bgw_job_stat seems to be in an inconsistent state. Could you delete the record from _timescaledb_internal.bgw_job_stat and try to execute the upgrade again?

[adeifeadekunbi]

I deleted the record on _timescaledb_internal.bgw_job_stat and the upgrade was successful but is it save to delete such records in production environment?

 \dx timescaledb
                                    List of installed extensions
    Name     | Version | Schema |                            Description
-------------+---------+--------+-------------------------------------------------------------------
 timescaledb | 2.14.2  | public | Enables scalable inserts and complex queries for time-series data
(1 row)

[jnidzwetzki]

Hi @adeifeadekunbi,

I recommend making manual changes in the catalog only when necessary. In this case, an inconsistency needs to be resolved before the upgrade can be performed. The upgrade will also add a constraint to the table to prevent similar inconsistencies in more recent versions.

[adeifeadekunbi]

Ok noted. Thank so much for help and swift response.