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

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.

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