[vmikheev@sectorbase.com: RE: [HACKERS] [Fwd: Revisited: Transactions, insert uni que.]]

Ed and I had moved part of this discussion to HACKERS, when it
became clear that something was a bug. Vadim Mikheev (the one
who implemented all this, with MVCC) answered a couple posts.
This is the shorter one, addressing _why_ things are they way
they are. I thought I'd forward it back here for those who didn't
follow Ed and I over to HACKERS.

Ross

----- Forwarded message from "Mikheev, Vadim" <vmikheev(at)sectorbase(dot)com> -----

From: "Mikheev, Vadim" <vmikheev(at)sectorbase(dot)com>
To: pghackers <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: [HACKERS] [Fwd: [GENERAL] Revisited: Transactions, insert uni
que.]
Date: Thu, 27 Apr 2000 02:10:00 -0700

> On Wed, Apr 26, 2000 at 06:15:14PM -0400, Tom Lane wrote:
> > "Ross J. Reedstrom" <reedstrm(at)wallace(dot)ece(dot)rice(dot)edu> writes:
> > > Some concurrent ISOLATION LEVEL SERIALIZABLE transactions are not
> > > truly serializable. Hmm, I wonder if the problem is only with use
> > > of subselects, EXISTS, NOT EXISTS, (and probably IN and NOT IN).

NO! This is results of using MV for CC. With MVCC, serializable xaction
should not assume that it sees _real_ state of database (and this is in
docs... or maybe in 6.5 HISTORY only, sorry -:)). This breaks "theory of
serializability" but gives us "consistent reads without blocking writers".
PostgreSQL provides LOCK/SELECT_FOR_UPDATE if you need in "real"
serializability - so, you have "workarround". Xactions in systems using
LOCKS for CC are always "trully serializable" (on serializable level)
but they have no "workarround" for "consistent reads without blocking
writers".

Vadim
----- End forwarded message -----
--
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005