Re: Read Uncommitted

On 18.12.2019 13:01, Simon Riggs wrote:
> I present a patch to allow READ UNCOMMITTED that is simple, useful and
> efficient.  This was previously thought to have no useful definition
> within PostgreSQL, though I have identified a use case for diagnostics
> and recovery that merits adding a short patch to implement it.
>
> My docs for this are copied here:
>
>     In <productname>PostgreSQL</productname>'s
> <acronym>MVCC</acronym>./configure
> --prefix=/home/knizhnik/postgresql/dist --enable-debug
> --enable-cassert CFLAGS=-O0
>
>     architecture, readers are not blocked by writers, so in general
>     you should have no need for this transaction isolation level.
>
>     In general, read uncommitted will return inconsistent results and
>     wrong answers. If you look at the changes made by a transaction
>     while it continues to make changes then you may get partial results
>     from queries, or you may miss index entries that haven't yet been
>     written. However, if you are reading transactions that are paused
>     at the end of their execution for whatever reason then you can
>     see a consistent result.
>
>     The main use case for this transaction isolation level is for
>     investigating or recovering data. Examples of this would be when
>     inspecting the writes made by a locked or hanging transaction, when
>     you are running queries on a standby node that is currently paused,
>     such as when a standby node has halted at a recovery target with
>     <literal>recovery_target_inclusive = false</literal> or when you
>     need to inspect changes made by an in-doubt prepared transaction to
>     decide whether to commit or abort that transaction.
>
>     In <productname>PostgreSQL</productname> read uncommitted mode gives
>     a consistent snapshot of the currently running transactions at the
>     time the snapshot was taken. Transactions starting after that time
>     will not be visible, even though they are not yet committed.
>
> This is a new and surprising thought, so please review the attached patch.
>
> Please notice that almost all of the infrastructure already exists to
> support this, so this patch does very little. It avoids additional
> locking on main execution paths and as far as I am aware, does not
> break anything.
>
> --
> Simon Riggshttp://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/>
> PostgreSQL Solutions for the Enterprise

As far as I understand with "read uncommitted" policy we can see two
versions of the same tuple if it was updated by two transactions both of
which were started before us and committed during table traversal by
transaction with "read uncommitted" policy. Certainly "read uncommitted"
means that we are ready to get inconsistent results, but is it really
acceptable to multiple versions of the same tuple?

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company