Data retrieval failure in batch_info due to difference between the precision of the search key and the precision of the stored key

[kimhanbeom]

Related issue: QA/QC communication repository's issue 115

• Currently, when viewing rounds by cluster in the UI, the data is not displaying correctly.
  
• This issue is caused by the following reason
  • To get the rounds by cluster, the DB is queried in the following order. (roundsByCluster GraphQL API)
    • Get model_id(i32) and batch_ts list(Vec<NaiveDateTime>) from the column_description table in postgres.
    • Get a Vec<ModelBatchInfo> from rocksdb's batch_info cf with model_id, batch_ts as keys.
  • One of the columns in the column_description table, batch_ts, is using the timestamp without time zone type and is stored with a precision down to micro second.
  • The keys used when inserting with batch_info cf are the model_id and the id (time of the event), and in the case of id, a timestamp with a nano second value.
  • That's why we can't find the value by looking up the batch_info cf with batch_ts as the key.

[kimhanbeom]

I believe this issue can be resolved by either adding a new column or changing the type of the existing column (batch_ts). If anyone has a better solution, please feel free to share it by commenting on this issue.

[sehkone]

I appreciate your investigation and suggestions. Based on what you mentioned, I think it would be better to change the current type so as to support nano seconds.

[MW-Kim]

@sehkone @syncpark @kimhanbeom
The batch_ts in the column_description table is of type timestamp without time zone, so it only stores values up to microseconds. The type of batch_ts can be changed to bigint to store values up to nanoseconds. This change requires the migration of existing data.
The migration process is expected to involve the following steps:

• Step1 : Adding a new column

ALTER TABLE column_description 
ADD COLUMN batch_ts_bigint bigint;

• Step2 : Converting and transferring data

UPDATE column_description 
SET batch_ts_bigint = EXTRACT(EPOCH FROM batch_ts) * 1000000000;

• Step3 : Dropping the existing column and renaming

ALTER TABLE column_description 
DROP COLUMN batch_ts;

ALTER TABLE column_description 
RENAME COLUMN batch_ts_bigint TO batch_ts;