CODEWITHSUNDEEP
sqldatabasepostgresqlsnapshot-isolation
Utkarsh Munjal
Utkarsh Munjal

Reputation: 11

Why must the exporting transaction remain active when importing a snapshot in PostgreSQL?

I'm working with PostgreSQL and using pg_export_snapshot() to create a snapshot in one transaction, which I then import into another transaction using SET TRANSACTION SNAPSHOT. I noticed that PostgreSQL requires the original (exporting) transaction to stay active while the snapshot is being used by the importing transaction.

Could someone explain why the exporting transaction must remain open for the snapshot to be valid in the importing transaction? Specifically, what is the technical reason behind this requirement?

Any insights into how PostgreSQL handles snapshot consistency in this context would be helpful. Thank you!

Upvotes: 0

Views: 26

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

If a snapshot exists, PostgreSQL needs to know about that so that VACUUM won't clean up any data that would be visible in that snapshot.

For that, PostgreSQL uses the information you can see as backend_xmin in the pg_stat_activity view. If you were to close the transaction, that information would become invalid, and VACUUM might clean up data before you import the snapshot somewhere else.

Sure, it would be possible to have something in shared memory that tracks the xmin horizon of exported snapshots, but (apart from not holding any locks) that would have the same effect as you holding open the transaction. Moreover, you'd have to somehow expire snapshots that never get imported anywhere. So that does not seem like a good idea.

Upvotes: 0

Related Questions