All we need is an easy explanation of the problem, so here it is.
This question follows up the previous simpler question: postgresql blocking behavior
I would like to understand the blocking behavior of postgresql. Consider I have an insert statement starting at 1:00 (stage A) completed at 2:00 (stage B).
However, I have a few complex select statements:
S1, starting at 0:30, completed at 1:30 S2, starting at 0:30, completed at 2:30 S3, starting at 1:30, completed at 2:30 S4, starting at 1:30, completed at 1:45,
What would be the associated end result I shall get? From one test I had myself, I realize
S1, S4 -> stage A.
How about S2 and S3?
Thanks for the answer, S1->S4 are all associated with stage A.
A more complex follow up question.
Imagine I have a view based on the db,
Create view db_view1 as select a+random() as a from db
The insert (I1) statement is
insert into db select * from db_view1
Again, I1 starting at 1:00 (stage A) completed at 2:00 (stage B). However, another client implement the same insert. 4 scenario below:
S5, apply another I1 starting at 0:30, completed at 1:30 S6, apply another I1 starting at 0:30, completed at 2:30 S7, apply another I1 starting at 1:30, completed at 2:30 S8, apply another I1 starting at 1:30, completed at 1:45,
For S5-S8, what happen in each of the scenario?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
The result will always be the same:
db will have all rows from
db_view1 at stage A added twice.
SELECT that is part of the
INSERT will see stage A, just like the query in the other question.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂