1

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?

2
  • 1
    No, it's a single snapshot for the whole (top-level) query. CTEs don't change that, it's still a single query.
    – user1822
    Jul 30, 2020 at 4:57
  • Great, thanks! Want to make that an answer?
    – Jordan
    Jul 30, 2020 at 5:18

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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