Forum OpenACS Q&A: Re: PostgreSQL MVCC transaction model

Collapse
Posted by Andrew Piskorski on
Russell, the quote I gave above is from section 12.2.1 of the docs. It sounds to me like whoever wrote that section of the docs was themselves confused about the actual behavior, as first they say one thing and then they directly contradict themselves later in the same paragraph.

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?

Collapse
Posted by russ m on
Yes, I know... Let's just say that at 2:47 I would have appreciated forums having an "edit post to look like less of a dumbass" function.

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".

Collapse
Posted by Jeff Davis on
Interestingly enough, the default on oracle is that statements not transactions see a consistent data set (i.e. any select will see the data as it existed at the start of the statement, not the start of the transaction). You can get transaction level consistency with "set transaction read only", but naturally you can only do selects, not dml.

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).

Collapse
Posted by Tom Jackson on

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.

Collapse
Posted by Jeff Davis on
A really simple (and mostly wrongheaded) example is something like:
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".