Hello everyone
I believe I've run into a bug in the behaviour of SKIP LOCKED, where I have a program that implements a queue with concurrent workers SELECTing work from some shared tables.
The code in question does a LEFT JOIN across two tables with a FOR UPDATE on the left table and a SKIP LOCKED clause, and then UPDATEs or INSERTs rows into the table on right side of the JOIN in a way that leads to subsequent executions of the same query to no longer match those rows. However, when run concurrently I'm seeing the same row be selected by multiple workers - which shouldn't be possible based on my understanding of the relevant semantics of these operations. Perhaps I'm just holding it wrong, but I would have expected the FOR UPDATE lock on the left table to be sufficient to avoid overlapping results.
I wasn't sure how much detail from that reproducing case to repeat in this email, so I've only gone with an outline of the observed and expected behaviour - but I can try and add more detail to this thread if desired
Cheers
Glen