New deployment - k8s migrations job stuck at "46/update_job_attributes_in_bounds"

[joshisumit]

Installing reportportal through helm chart version 5.8.0, however its failing at migration number 46 with below error:

error: migration failed: invalid input syntax for type bigint: "7776000.000000" in line 0: CREATE OR REPLACE FUNCTION update_job_attributes_in_bounds()

• Tried this solution of executing from migration job 18 and setting schema_migration to 45 didn't help.
  [v5] db-scrripts migration fail from 5.3.5 to 5.5 reportportal#1511 (comment)
• RDS postgres Server version 14.7

Complete logs from reportportal/migrations:5.8.0

9/u analyzer_params (1.143361068s)
10/u attachment_size (1.175525432s)
11/u password_encoding (1.114227086s)
12/u remove_ticket_duplicates (522.663799ms)
13/u add_allocated_storage_per_project (379.700988ms)
14/u test_case_id_size_increase (367.856924ms)
15/u statistics_decreasing (354.718664ms)
16/u remove_unused_indexes (311.564418ms)
17/u status_enum_extension (342.797121ms)
18/u job_attributes (330.871786ms)
19/u retries_handling_extension (326.087918ms)
20/u deep_merge_statistics_handling (330.744182ms)
21/u deep_merge_retries_fix (329.711705ms)
22/u deep_merge_nested_steps_fix (330.360563ms)
23/u rerun_item_statistics_fix (329.194707ms)
24/u widget_views_cleanup (329.418043ms)
25/u deep_merge_nested_steps_path_fix (331.535074ms)
26/u retries_lock_fix (334.215555ms)
27/u add_project_id_log (332.330028ms)
28/u create_log_project_idx (305.759724ms)
29/u create_table_item_project (308.886168ms)
30/u item_project_fill_part1 (312.603135ms)
31/u item_project_fill_part2 (315.398854ms)
32/u create_item_idx (341.233996ms)
33/u drop_log_message_trgm_idx (342.157895ms)
34/u fill_project_id_part1 (346.855066ms)
35/u fill_project_id_part2 (342.970527ms)
36/u drop_item_project (339.832702ms)
37/u create_log_message_trgm_idx (335.66447ms)
38/u add_not_null_log_project_id (335.46782ms)
39/u attachment_creation_date (335.465249ms)
40/u attachment_creation_date_fill (330.811425ms)
41/u attachment_creation_date_not_null (334.479702ms)
42/u shedlock_table (336.707924ms)
43/u attachment_for_deletion_table (314.953562ms)
44/u remove_triggers (338.470678ms)
45/u add_jobs_indexes (354.939352ms)
error: migration failed: invalid input syntax for type bigint: "7776000.000000" in line 0: CREATE OR REPLACE FUNCTION update_job_attributes_in_bounds()
    RETURNS INTEGER
    LANGUAGE plpgsql
AS
$$
DECLARE
prj_id                 BIGINT;

    launch_job_attr_id     BIGINT;
    log_job_attr_id        BIGINT;
    attachment_job_attr_id BIGINT;

    launch_job_value       BIGINT;
    log_job_value          BIGINT;
    attachment_job_value   BIGINT;
BEGIN
    launch_job_attr_id := (SELECT id
                           FROM attribute
                           WHERE attribute.name = 'job.keepLaunches');
    log_job_attr_id := (SELECT id
                        FROM attribute
                        WHERE attribute.name = 'job.keepLogs');
    attachment_job_attr_id := (SELECT id
                               FROM attribute
                               WHERE attribute.name = 'job.keepScreenshots');

FOR prj_id IN (SELECT id FROM project ORDER BY id)
        LOOP
            launch_job_value := (SELECT value::BIGINT FROM project_attribute WHERE attribute_id = launch_job_attr_id AND project_id = prj_id);
            log_job_value := (SELECT value::BIGINT FROM project_attribute WHERE attribute_id = log_job_attr_id AND project_id = prj_id);
            attachment_job_value := (SELECT value::BIGINT FROM project_attribute WHERE attribute_id = attachment_job_attr_id AND project_id = prj_id);

            IF launch_job_value != 0
            THEN
                IF log_job_value > launch_job_value OR log_job_value = 0
                THEN
                    log_job_value := launch_job_value;
UPDATE project_attribute
SET value = log_job_value
WHERE attribute_id = log_job_attr_id
  AND project_id = prj_id;
END IF;
END IF;

            IF log_job_value != 0
            THEN
                IF attachment_job_value > log_job_value OR attachment_job_value = 0
                THEN
                    attachment_job_value := log_job_value;
UPDATE project_attribute
SET value = attachment_job_value
WHERE attribute_id = attachment_job_attr_id
  AND project_id = prj_id;
END IF;
END IF;

END LOOP;

RETURN 0;
END;
$$;

SELECT update_job_attributes_in_bounds(); (details: pq: invalid input syntax for type bigint: "7776000.000000")

Any pointers is appreciated.

[BeWut]

@joshisumit We had the same issue and this was related to the used postgres version. After downgrading to version 13 the migration job finished successfully.

The version that would be installed by the referenced dependency would be postgres version 11. Unfortunately we couldn't find any documentation that clarified the supported postgres version.

[dracut5]

I am going to update PostgreSQL version (RDS Aurora for Postgres) and haven't found a list of supported versions either.

I can confirm that the latest RP release, 23.1, can work with RDS Aurora for Postgres12.

Also, there are one mention that it might work with RDS Aurora for Postgres13 - https://reportportal.io/docs/installation-steps/DeployWithAWSECSFargate/#postresql-database.

It would be great to have such documentation for supported DBs and their versions.