Concurrent Update of Array (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]?

Answer 1

There is nothing you need to do, because this kind of anomaly cannot occur in PostgreSQL.

When the second transaction gets the lock on the row, it will re-read it and see the value modified by the first transaction.

See the documentation for details.