Are snapshots generated for each sub query inside a Postgres READ_COMMITTED transaction?

Question

From what I understand - in a READ_COMMITTED Postgres transaction “the transaction obtains a snapshot whenever an SQL command is executed” source. Does this mean a snapshot will be obtained for each sub query in a nested query? Does the use of CTEs in place of sub queries affect this behavior?

nagamocha · Answer

The snapshot 'created' is per statement regardless of whether that statement contains subqueries or not. From the documentation:

... This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction.

Are snapshots generated for each sub query inside a Postgres READ_COMMITTED transaction?

One Answer

Add your own answers!

Ask a Question