Read Uncommitted

Currently, PostgreSQL implements the SQL Standard transaction isolation
level of Read Uncommitted as being equivalent to Read Committed. That is
a valid decision, though I wish to propose an alternative.

At the moment, a long running SQL Statement can prevent xmin moving
forward, which can result in VACUUM and HOT not being able to remove row
versions effectively. My proposal is that a Read Uncommitted transaction
would not prevent row removal, which then offers no guarantee that the
"correct" answer would be returned. Which is *exactly* what that
transaction isolation level was designed for.

In many cases, an application designer may be able to tell that a
particular query will always return the correct answer. For example, we
may query against data which is known not to change, even though other
data in the same database cluster may be subject to frequent change.
e.g. queries against large insert-only tables.

By allowing the user to select a non-default isolation level we would
allow long running queries to have significantly less disruption on
other operations. Read Uncommitted would imply Read Only status, so
various data change operations would be rejected. No errors would
result, just that some data would be missing from the answer, iff data
had been removed during execution. It might be possible to get a table
does not exist error because non-transactional pg_class updates take
place; transactional pg_class updates would be prevented by
relation-level locking.

I would *not* allow this as an option or default for pg_dump, since this
would almost certainly result in error. No footguns allowed in so
critical a utility.

The implementation is trivial, namely that the calculation of global
xmin would ignore Read Uncommitted transactions.

Read Uncommitted would not be the default, so novice users would be in
no danger. Bear in mind that Read Uncommitted is commonly used in other
database systems, so this feature is already understood and even
expected by many database experts from other backgrounds. Many
PostgreSQL experts would also be able to take advantage of this feature.
Since its part of the SQL Standard this "dirty read" isn't likely to
take anybody by surprise that reads any of (PostgreSQL manual, SQL
Standard, other RDBMS manual).

Comments?

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support