[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: [ADMIN] Transaction isolation and UNION queries
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2003-02-28 4:24:11
[Download RAW message or body]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
[prev in list] [next in list] [prev in thread] [next in thread]