-1

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?

Edit:

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?

1

By virtue of PostgreSQL's multi-versioning implementation, no transaction will be blocked. Each of these queries will see the state of the database like it was before the insert started.

PostgreSQL takes a snapshot of the database at the start of the query, and it sees the data as they were when the query started. Also, you never see modifications from a transaction that was not yet completed when the query started.

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.