postgresql - Concurrent update of an array field in Postgres

Question

I have an array that I can update from multiple workers as follows: UPDATE my_table SET arr = array_append(my_table.arr, element).

The problem pops up when two workers try to update the array at the same time. For instance, both workers see that at the moment of the update, the array is empty and try to append an element as follows: array_append(empty_arr, element_a) and array_append(empty_arr, element_b). In the end, the final content of the array will be: arr[element_a] or arr[element_b] because one of the workers will finish updating the array earlier and its value will be overwritten with the value from the other worker. How can I ensure that both values are written in the array: arr[element_a, element_b]?

question from:https://stackoverflow.com/questions/65882144/concurrent-update-of-an-array-field-in-postgres

Answer 1

Waitting for answers