Return-Path: pg_adm@postgres.berkeley.edu
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA26801; Wed, 21 Apr 93 14:09:57 -0700
Message-Id: <9304212109.AA26801@postgres.Berkeley.EDU>
From: Jeff Meredith <mer@miro.com>
Subject: Re: bug in begin/end? replace?
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
In-Reply-To: Your message of "Wed, 21 Apr 93 12:24:14 PDT."
             <9304211923.AA25728@postgres.Berkeley.EDU> 
Date: Wed, 21 Apr 93 14:06:50 -0700
From: Jeff Meredith <mer@miro.com>

message <9304211923.AA25728@postgres.Berkeley.EDU> read as follows:

> > Here's the bug:
> > Q1					Q2
> > begin\g
> > 					begin\g
> >  retrieve (tt_number.tt_number)\g
> > (gets "2")
> > replace tt_number (tt_number=3)\g
> > end\g
> > 					retrieve (tt_number.tt_number)\g
> > 					(gets "2")
> > 					replace tt_number (tt_number=3)\g
> > 					end\g
> 
> for me, the retrieve in Q2 gets 3.  which is not to say that there
> isn't a bug, but that it's not a consistent one.
> 
> there are some known bugs (known in the sense that it is known that
> there is a bug, the exact nature and fix have not been determined)
> in the handling of dirty buffers.  i have a feeling this might be
> related to them.

this is a problem stemming from the postgres data visibility model.
it does not guarantee serializability.

basically the pdvm says that you cannot see changes that did not
commit before your transaction started.  since Q2 starts before
Q1 commits it cannot see the replaced tuple, but it can see the original.
it replaces this original, putting another tuple in the relation.

it has been argued that the visibility model be changed such that a
transaction can see the changes of any committed transaction regardless
of when it starts.  this would solve the above problem, but it turns out
that it will give you consistency problems.  for example:

	Q1				Q2

					begin\g

					retrieve (pg_class.*)
					where pg_class.relname = "foo" \g
	create foo (a=int4)\g
					retrieve (pg_class.*)
					where pg_class.relname = "foo" \g

					end\g

Q2 gets a different answer for the same query run inside a single xaction.


jeff meredith
mer@miro.com