postgresql blocking behavior for table dependent insert

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

Old context:

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?

Answer:

Thanks for the answer, S1->S4 are all associated with stage A.

Follow up:

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.

Method 1

The result will always be the same: db will have all rows from db_view1 at stage A added twice.

The 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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply