Re: Transaction isolation and UNION queries

Bob Smith <bsmith@h-e.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