#3777 [BUG] AutoIncrement sequence number data type is not consistent and not cycle Issue created by honnix Describe the bug This is probably due to change in gorm over time when handling

uint

and

int64

(I have no proof though, but https://github.com/flyteorg/flyteadmin/blob/master/pkg/repositories/models/resource.go#L17 might indicate that because

resources_id_seq

is

bigint

), but we have seen different sequence types in our database, some of them being

integer

while some of them being

bigint

. For example the newer sequence

description_entities_id_seq

is

bigint

while older things like

node_execution_events_id_seq

is

integer

that is obviously too short. Also the sequences are not cycle which means they will not wrap around when reaching max value. This is somewhat problematic especially for those

integer

typed sequences. We have scheduled data retention job to clean up database so old entries are deleted, but since the sequence number is not reusable, we can hit max value easily when it is an

integer

. This gives a quick overview of what we have in database (note that some of the types have been manually altered:

executions_id_seq

,

node_execution_events_id_seq

)

sequencename               | data_type | min_value |      max_value      | cycle 
-----------------------------------------+-----------+-----------+---------------------+-------
 projects_id_seq                         | integer   |         1 |          2147483647 | f
 description_entities_id_seq             | bigint    |         1 | 9223372036854775807 | f
 tasks_id_seq                            | integer   |         1 |          2147483647 | f
 signals_id_seq                          | bigint    |         1 | 9223372036854775807 | f
 workflows_id_seq                        | integer   |         1 |          2147483647 | f
 launch_plans_id_seq                     | integer   |         1 |          2147483647 | f
 executions_id_seq                       | bigint    |         1 | 9223372036854775807 | f
 execution_events_id_seq                 | integer   |         1 |          2147483647 | f
 node_executions_id_seq                  | integer   |         1 |          2147483647 | f
 node_execution_events_id_seq            | bigint    |         1 | 9223372036854775807 | f
 task_executions_id_seq                  | integer   |         1 |          2147483647 | f
 named_entity_metadata_id_seq            | integer   |         1 |          2147483647 | f
 named_entity_metadata_resource_type_seq | integer   |         1 |          2147483647 | f
 resources_id_seq                        | bigint    |         1 | 9223372036854775807 | f
 schedulable_entities_id_seq             | integer   |         1 |          2147483647 | f
 schedule_entities_snapshots_id_seq      | integer   |         1 |          2147483647 | f

Expected behavior All sequences are typed as

bigint

, and it would be nice to make them cycle (although a quick search didn't find how gorm could support that). Additional context to reproduce No response Screenshots No response Are you sure this issue hasn't been raised already? ☑︎ Yes Have you read the Code of Conduct? ☑︎ Yes flyteorg/flyte