2

I have a PostgreSQL query that looks like this:

DELETE FROM x WHERE x.id NOT IN (SELECT x_id FROM y WHERE ...);

Is it possible for the DELETE to see rows that weren't previously committed and visible to the SELECT subquery?

My intuition is that since the subquery is part of the overall statement, no data changes should be visible to the DELETE that weren't already visible to the SELECT. Is this correct?

3

Is it possible for the DELETE to see rows that weren't previously committed and visible to the SELECT subquery?

No, that's not possible.

Postgres always operates at "read committed" isolation level (or higher) and every statement sees a consistent snapshot of the database from the time the statement started. And in this case the statement defining the snapshot is the DELETE statement. The select from the sub-query sees the same snapshot

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.