Skip to main content
13 events
when toggle format what by license comment
Jun 3, 2022 at 14:46 comment added Gili @ErwinBrandstetter Sorry, you're right. I'll post a follow-up question there.
Jun 3, 2022 at 14:22 comment added Erwin Brandstetter @Gili: Isn't that what my answer here suggests? dba.stackexchange.com/a/213625/3684
Jun 3, 2022 at 14:12 comment added Gili @ErwinBrandstetter Reviewing your other post, what happens if we were to add a retry loop around INSERT ... ON CONFLICT ... DO NOTHING ... SELECT existingRow? I assume this would protect one against inserts that are not visible yet. It would be less efficient than the single statement you proposed, but would it be safe?
Sep 21, 2018 at 17:08 comment added Erwin Brandstetter You have quoted a sentence from my related answer out of context, which applies applies to the CTE case. I have no time for a test case right now. If you won't believe me, feel free. I have provided plenty of information.
Sep 21, 2018 at 16:56 comment added CL. @ErwinBrandstetter This question does not have any CTE. And please provide the sequence of commands what would lead to a race condition.
Sep 21, 2018 at 14:27 comment added Erwin Brandstetter Note the difference between a nested SELECT in a CTE and a separate SELECT statement. Your separate SELECT operates on a new snapshot. But there is a time frame between INSERT and SELECT that allows race conditions.
Sep 21, 2018 at 14:19 comment added CL. @ErwinBrandstetter In my test, the sentence "The SELECT sees the same snapshot from the start of the query and also cannot return the yet invisible row." appears to be wrong. [In two connections: C1: BEGIN; C2: BEGIN; C1: INSERT; C2: tries same INSERT, blocks; C1: COMMIT; C2: unblocks; C2: SELECT sees the new row.] Again, please provide an example that proves the existence of the problem.
Sep 21, 2018 at 13:46 comment added Erwin Brandstetter I discussed possible issues in detail here: stackoverflow.com/a/42217872/939860
Sep 21, 2018 at 13:39 comment added CL. @ErwinBrandstetter I tested this. Please provide a sequence of commands that shows the problem.
Sep 21, 2018 at 12:59 comment added Erwin Brandstetter While this is simple and should work in most cases, it can still find no row (though the row is there) or find a row in the SELECT, which a concurrent transaction already deleted (yet uncommitted). Under heavy concurrent load you might have to do more (loop), like the other answers illustrate.
Jul 20, 2018 at 7:29 vote accept Elliot Blackburn
Jul 20, 2018 at 7:27 comment added Elliot Blackburn Thanks for this, a simple solution but one we overlooked as it would potentially end up being done in many places. That said, it doesn't mean it's bad, just means we've got some work to do!
Jul 19, 2018 at 8:59 history answered CL. CC BY-SA 4.0