Is the ANY keyword in SQL considered an atomic operation?

Question

I'm currently working on a project where I need to use the ANY keyword in a SQL query, and I'm curious about its atomicity. Specifically, I'm using it in the context of updating rows in a cockroachdb with Go, similar to the following query:

UPDATE table
SET active = NULL
WHERE id = ANY($1);

I understand that the ANY keyword is used for comparisons, but I'm wondering if this operation is considered atomic by itself or if additional measures (like transactions) are needed to ensure atomicity?

I haven't wrapped it in a transaction for now.

Answer 1

The ANY keyword doesn't affect UPDATE's atomicity gaurantees.

Here is an example in CockroachDB to reaffirm the above statement:

root@localhost:29000/defaultdb> CREATE TABLE atomic_test (id INT, value INT, 
                             -> computed DECIMAL);                           
CREATE TABLE

Time: 12ms total (execution 12ms / network 0ms)

root@localhost:29000/defaultdb> INSERT INTO atomic_test VALUES (1, 100), (2, 
                             -> 200), (3, 0), (4, 400);                      
INSERT 0 4

Time: 13ms total (execution 13ms / network 0ms)

root@localhost:29000/defaultdb> SELECT * FROM atomic_test;                   
  id | value | computed
-----+-------+-----------
   1 |   100 |     NULL
   2 |   200 |     NULL
   3 |     0 |     NULL
   4 |   400 |     NULL
(4 rows)

Time: 2ms total (execution 2ms / network 0ms)

root@localhost:29000/defaultdb> UPDATE atomic_test SET computed = 1 / value  
                             -> WHERE id = ANY(ARRAY[1, 2, 3, 4]);           
ERROR: division by zero
SQLSTATE: 22012
root@localhost:29000/defaultdb> SELECT * FROM atomic_test;                   
  id | value | computed
-----+-------+-----------
   1 |   100 |     NULL
   2 |   200 |     NULL
   3 |     0 |     NULL
   4 |   400 |     NULL
(4 rows)

Time: 2ms total (execution 2ms / network 0ms)

The ANY keyword's role is purely for comparison within queries. As demonstrated above, all changes made by UPDATE either all succeed or all fail - there is no middle state.