Re: Transaction isolation and UNION queries

Bob Smith <bsmith(at)h-e(dot)com> writes:
> I have a question about transaction isolation that I can't find an
> answer to in the docs. I'm working with a database that has some data
> split over two tables. One table is the ultimate destination for all
> the data, the other is a "pending" table which holds rows during data
> entry. Rows from the pending table are moved to the permanent table
> once data entry is complete. For some purposes I want to see rows from
> both tables, so I do a UNION. My question is, with only read committed
> isolation, could a commit by another transaction make changes appear
> between the separate parts of the UNION query? In other words, could a
> row appear to be missing or duplicated because a transaction that was
> moving the row from pending to permanent committed while the UNION was
> running?

Should be okay as long as you retrieve the data in a single UNION select
--- or even multiple selects, if you put them into a single
serializable-mode transaction. But not multiple select commands in a
read-committed transaction --- in RC mode you will recognize concurrent
commits at each command boundary.

regards, tom lane