The purpose is to concat new data onto existing values of c:
UPDATE tbl
SET c = c || new_data.c
FROM ( [...] ) AS new_data
WHERE
tbl.id = new_data.id
It appears to have a race condition:
t0: Query A starts subquery
t1: Query A starts self-join
t2. Query A starts UPDATE with data from self-join and subquery
t3. Query B starts subquery
t4. Query B starts self-join (note: data from t1!)
[...]
tN. Query A finishes UPDATE
tN+1. Query B finishes UPDATE, missing any new_data from Query A
My assumption is that t1 and t4 (the self-joins) use SELECT but not
SELECT FOR UPDATE. If they did, I think the race condition would go
away.
Did I analyze that right?
Thanks!
--
Seamus Abshere, SCEA
https://github.com/seamusabsherehttps://linkedin.com/in/seamusabsherehttps://www.faraday.io