As a Metric user I want opendata DB primary key to use bigint instead of integer

[VitaliStupin]

Opendata DB primary key sequence runs out of integer (int4) values in larger X-Road instances. Bigint should be used instead.

Software version:
1.1.1, 1.2.0

Host OS and version:
Ubuntu 20.04

The schema in anonymizer defines bigint as id column type:

X-Road-Metrics/anonymizer_module/etc/field_data.yaml

Line 4 in 64fb79d

type: bigint

But id column type is discarded during schema object creation:

X-Road-Metrics/anonymizer_module/opmon_anonymizer/iio/opendata_writer.py

Line 59 in 64fb79d

if field_name not in ['id']:

Then during creation of table id column type is hardcoded to be SERIAL:

X-Road-Metrics/anonymizer_module/opmon_anonymizer/iio/postgresql_manager.py

Line 118 in 64fb79d

cursor.execute(f'CREATE TABLE {self._table_name} (id SERIAL PRIMARY KEY{column_schema});')

Example error log of anonymizer:

File \"/usr/lib/python3/dist-packages/opmon_anonymizer/iio/postgresql_manager.py\", line 58, in add_data
cursor.execute(query)psycopg2.errors.SequenceGeneratorLimitExceeded: nextval:
reached maximum value of sequence \"logs_id_seq\" (2147483647)

Proposed solution:

Replace id SERIAL PRIMARY KEY with id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY in postgresql_manager.py because SERIAL types are soft-deprecated:
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_serial

Hotfix for existing installations (NB! This is slow operation that locks tables, may take hours to complete and requires at least 50% of free drive space):

ALTER TABLE logs ALTER COLUMN id TYPE BIGINT;
ALTER SEQUENCE logs_id_seq AS BIGINT MAXVALUE 9223372036854775807;

[raits]

Hello @VitaliStupin!

Thank you for reporting this. We will look into it.