Forum OpenACS Q&A: Re: PostgreSQL MVCC transaction model
Also, Tom Lane's slides seem to directly contradict what your actual test above shows, so has PostgreSQL's behavior changed in the last 4 years or so, or did he just have it wrong in his slides?
Tom's slides do contradict the observed behaviour, but on rereading the description of isolation levels this behaviour is a Nonrepeatable Read, and is permitted at the Read Commited isolation level. The docs back to 6.5 (c. 1999) all say that Read Commited is the default isolation level in Postgres, so I suspect the slides were wrong. Also the 6.5 docs are a little ambiguous, but it's possible to interpret their "a query sees only data committed before the query began and never sees either dirty data or concurrent transaction changes committed during query execution" as still matching the current behaviour - note it's "during query execution" not "during the current transaction".
I am not sure what you do if you need to do dml that uses a consistent dataset (other than just lock everything in sight).
This is weird. Under what conditions would you want a consistent data set during an entire transaction. If you are inserting or updating or deleting, these statements rely on the actual current set of data as seen by the transaction, otherwise you might violate some constraint on the data, such as a unique constraint. Obviously there are reasons, but not for default behavior. For anyone who has run an sqlplus session uncommitted for any length of time has figured out, the described behavior is exactly what you expect.
insert into tmp(pk1,val1) select :pk1, :val1 where not exists (select 1 from tmp where pk1 = :pk1)to ensure you don't throw an error if the primary key already exists (this is wrongheaded since with oracle at least, you should just attempt the insert and do an update if you get a pk error but you can't do that on postgres in a transaction).
By default on oracle this can actually generate a primary key violation errors even though it looks on the surface like it is "one statement".