Migrations fail with Postgres 14

[eoldavix]

I am trying to install reportportal from scratch using an external Amazon RDS instance in an empty database with the following version:

PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit

The migrations fail on migration 46 due to: error: migration failed: invalid input syntax for type bigint: "7776000.000000" in line 0: CREATE OR REPLACE FUNCTION update_job_attributes_in_bounds()

I have also checked this issue arises if you change the postgres image in the current docker-compose.yml from postgres:12-alpine to postgres:14-alpine:

reportportal-migrations-1        | wait-for-it.sh: waiting 15 seconds for postgres:5432
reportportal-migrations-1        | wait-for-it.sh: postgres:5432 is available after 0 seconds
reportportal-migrations-1        | 0/u extensions (497.553333ms)
reportportal-migrations-1        | 1/u initialize_schema (3.376374918s)
reportportal-migrations-1        | 2/u initialize_quartz_schema (4.097071294s)
reportportal-migrations-1        | 3/u default_data (4.452005044s)
reportportal-migrations-1        | 4/u size_limitations (4.588283336s)
reportportal-migrations-1        | 5/u test_case_id_type (5.000639669s)
reportportal-migrations-1        | 6/u retries_handling (5.100414752s)
reportportal-migrations-1        | 7/u auth_integration (5.343741502s)
reportportal-migrations-1        | 8/u sender_case_enabled_field (5.425768253s)
reportportal-migrations-1        | 9/u analyzer_params (5.511129753s)
reportportal-migrations-1        | 10/u attachment_size (5.609048795s)
reportportal-migrations-1        | 11/u password_encoding (5.201250836s)
reportportal-migrations-1        | 12/u remove_ticket_duplicates (2.436008876s)
reportportal-migrations-1        | 13/u add_allocated_storage_per_project (1.821044959s)
reportportal-migrations-1        | 14/u test_case_id_size_increase (1.547007959s)
reportportal-migrations-1        | 15/u statistics_decreasing (1.500473167s)
reportportal-migrations-1        | 16/u remove_unused_indexes (1.218856376s)
reportportal-migrations-1        | 17/u status_enum_extension (1.417099792s)
reportportal-migrations-1        | 18/u job_attributes (1.255127126s)
reportportal-migrations-1        | 19/u retries_handling_extension (1.270076834s)
reportportal-migrations-1        | 20/u deep_merge_statistics_handling (1.270825334s)
reportportal-migrations-1        | 21/u deep_merge_retries_fix (1.277714084s)
reportportal-migrations-1        | 22/u deep_merge_nested_steps_fix (1.271136501s)
reportportal-migrations-1        | 23/u rerun_item_statistics_fix (1.240898834s)
reportportal-migrations-1        | 24/u widget_views_cleanup (1.274908001s)
reportportal-migrations-1        | 25/u deep_merge_nested_steps_path_fix (1.271756209s)
reportportal-migrations-1        | 26/u retries_lock_fix (1.269101042s)
reportportal-migrations-1        | 27/u add_project_id_log (1.224727876s)
reportportal-migrations-1        | 28/u create_log_project_idx (1.030932292s)
reportportal-migrations-1        | 29/u create_table_item_project (1.037555543s)
reportportal-migrations-1        | 30/u item_project_fill_part1 (1.071316876s)
reportportal-migrations-1        | 31/u item_project_fill_part2 (1.075505667s)
reportportal-migrations-1        | 32/u create_item_idx (1.071101459s)
reportportal-migrations-1        | 33/u drop_log_message_trgm_idx (1.099963834s)
reportportal-migrations-1        | 34/u fill_project_id_part1 (1.112532167s)
reportportal-migrations-1        | 35/u fill_project_id_part2 (1.049357293s)
reportportal-migrations-1        | 36/u drop_item_project (1.062815792s)
reportportal-migrations-1        | 37/u create_log_message_trgm_idx (1.074203875s)
reportportal-migrations-1        | 38/u add_not_null_log_project_id (1.074487918s)
reportportal-migrations-1        | 39/u attachment_creation_date (1.064267542s)
reportportal-migrations-1        | 40/u attachment_creation_date_fill (1.063272875s)
reportportal-migrations-1        | 41/u attachment_creation_date_not_null (1.028417251s)
reportportal-migrations-1        | 42/u shedlock_table (1.04720725s)
reportportal-migrations-1        | 43/u attachment_for_deletion_table (1.088711501s)
reportportal-migrations-1        | 44/u remove_triggers (1.06143s)
reportportal-migrations-1        | 45/u add_jobs_indexes (1.089748251s)
reportportal-migrations-1        | error: migration failed: invalid input syntax for type bigint: "7776000.000000" in line 0: CREATE OR REPLACE FUNCTION update_job_attributes_in_bounds()
reportportal-migrations-1        |     RETURNS INTEGER
reportportal-migrations-1        |     LANGUAGE plpgsql
reportportal-migrations-1        | AS
reportportal-migrations-1        | $$
reportportal-migrations-1        | DECLARE
reportportal-migrations-1        | prj_id                 BIGINT;
reportportal-migrations-1        |
reportportal-migrations-1        |     launch_job_attr_id     BIGINT;
reportportal-migrations-1        |     log_job_attr_id        BIGINT;
reportportal-migrations-1        |     attachment_job_attr_id BIGINT;
reportportal-migrations-1        |
reportportal-migrations-1        |     launch_job_value       BIGINT;
reportportal-migrations-1        |     log_job_value          BIGINT;
reportportal-migrations-1        |     attachment_job_value   BIGINT;
reportportal-migrations-1        | BEGIN
reportportal-migrations-1        |     launch_job_attr_id := (SELECT id
reportportal-migrations-1        |                            FROM attribute
reportportal-migrations-1        |                            WHERE attribute.name = 'job.keepLaunches');
reportportal-migrations-1        |     log_job_attr_id := (SELECT id
reportportal-migrations-1        |                         FROM attribute
reportportal-migrations-1        |                         WHERE attribute.name = 'job.keepLogs');
reportportal-migrations-1        |     attachment_job_attr_id := (SELECT id
reportportal-migrations-1        |                                FROM attribute
reportportal-migrations-1        |                                WHERE attribute.name = 'job.keepScreenshots');
reportportal-migrations-1        |
reportportal-migrations-1        | FOR prj_id IN (SELECT id FROM project ORDER BY id)
reportportal-migrations-1        |         LOOP
reportportal-migrations-1        |             launch_job_value := (SELECT value::BIGINT FROM project_attribute WHERE attribute_id = launch_job_attr_id AND project_id = prj_id);
reportportal-migrations-1        |             log_job_value := (SELECT value::BIGINT FROM project_attribute WHERE attribute_id = log_job_attr_id AND project_id = prj_id);
reportportal-migrations-1        |             attachment_job_value := (SELECT value::BIGINT FROM project_attribute WHERE attribute_id = attachment_job_attr_id AND project_id = prj_id);
reportportal-migrations-1        |
reportportal-migrations-1        |             IF launch_job_value != 0
reportportal-migrations-1        |             THEN
reportportal-migrations-1        |                 IF log_job_value > launch_job_value OR log_job_value = 0
reportportal-migrations-1        |                 THEN
reportportal-migrations-1        |                     log_job_value := launch_job_value;
reportportal-migrations-1        | UPDATE project_attribute
reportportal-migrations-1        | SET value = log_job_value
reportportal-migrations-1        | WHERE attribute_id = log_job_attr_id
reportportal-migrations-1        |   AND project_id = prj_id;
reportportal-migrations-1        | END IF;
reportportal-migrations-1        | END IF;
reportportal-migrations-1        |
reportportal-migrations-1        |             IF log_job_value != 0
reportportal-migrations-1        |             THEN
reportportal-migrations-1        |                 IF attachment_job_value > log_job_value OR attachment_job_value = 0
reportportal-migrations-1        |                 THEN
reportportal-migrations-1        |                     attachment_job_value := log_job_value;
reportportal-migrations-1        | UPDATE project_attribute
reportportal-migrations-1        | SET value = attachment_job_value
reportportal-migrations-1        | WHERE attribute_id = attachment_job_attr_id
reportportal-migrations-1        |   AND project_id = prj_id;
reportportal-migrations-1        | END IF;
reportportal-migrations-1        | END IF;
reportportal-migrations-1        |
reportportal-migrations-1        | END LOOP;
reportportal-migrations-1        |
reportportal-migrations-1        | RETURN 0;
reportportal-migrations-1        | END;
reportportal-migrations-1        | $$;
reportportal-migrations-1        |
reportportal-migrations-1        | SELECT update_job_attributes_in_bounds(); (details: pq: invalid input syntax for type bigint: "7776000.000000")
reportportal-migrations-1        | wait-for-it.sh: waiting 15 seconds for postgres:5432
reportportal-migrations-1        | wait-for-it.sh: postgres:5432 is available after 0 seconds
reportportal-migrations-1        | error: Dirty database version 46. Fix and force version.
reportportal-migrations-1        | wait-for-it.sh: waiting 15 seconds for postgres:5432
reportportal-migrations-1        | wait-for-it.sh: postgres:5432 is available after 0 seconds
reportportal-migrations-1        | error: Dirty database version 46. Fix and force version.
reportportal-migrations-1        | wait-for-it.sh: waiting 15 seconds for postgres:5432
reportportal-migrations-1        | wait-for-it.sh: postgres:5432 is available after 0 seconds
reportportal-migrations-1        | error: Dirty database version 46. Fix and force version.
reportportal-migrations-1        | wait-for-it.sh: waiting 15 seconds for postgres:5432
reportportal-migrations-1        | wait-for-it.sh: postgres:5432 is available after 0 seconds
reportportal-migrations-1        | error: Dirty database version 46. Fix and force version.
reportportal-migrations-1        | wait-for-it.sh: waiting 15 seconds for postgres:5432
reportportal-migrations-1        | wait-for-it.sh: postgres:5432 is available after 0 seconds
reportportal-migrations-1        | error: Dirty database version 46. Fix and force version.
reportportal-migrations-1        | wait-for-it.sh: waiting 15 seconds for postgres:5432
reportportal-migrations-1        | wait-for-it.sh: postgres:5432 is available after 0 seconds
reportportal-migrations-1        | error: Dirty database version 46. Fix and force version.

As I can see, the created project attributes have been stored as float numbers instead of integers:

Compared with the attributes created in a postgres version 12:

Would you mind checking it, please?

[eoldavix]

As I could see, the issue comes from the function EXTRACT in migration script 18: https://github.com/reportportal/migrations/blob/develop/migrations/18_job_attributes.up.sql

From Postgres 14 Release Notes:

Change EXTRACT() to return type numeric instead of float8 (Peter Eisentraut)
This avoids loss-of-precision issues in some usages. The old behavior can still be obtained by using the old underlying function date_part().
Also, EXTRACT(date) now throws an error for units that are not part of the date data type.

[eoldavix]

I have created PR #257 which fixes this issue. I have checked that the migrations now complete on both Postgres v12 and v14 versions. Please review and merge it if you feel this is a valid fix.