Subject: bug in begin/end? replace?

From: Nat Howard <[email-masked]>
Date: Wed, 21 Apr 93 14:35:56 -0400

I'm trying to get postgres to give out unique, sequential,  ticket
identifiers -- but I've run into a problem.

I sent out a previous note asking if the following would work.

Given a class:

---------------
| tt_number   |
---------------
| 2           |
---------------

If I have two queriers (questors?)) do the following, in the following
order, one gets a deadlock and backout, the other completes -- this
is pretty much what I want:

Q1					Q2

begin\g

					begin\g

retrieve(tt_number.tt_number)\g
(gets 2)

					retrieve(tt_number.tt_number)\g
					(gets 2)

replace tt_number(tt_number=3)\g

					 replace tt_number(tt_number=3)\g

end\g

					end\g

		( a long pause occurs for both at this point and then...)

Q1 gets:

NOTICE:Apr 21 11:56:23:Timeout -- possible deadlock
WARN:Apr 21 11:56:23:WaitOnLock: error on wakeup - Aborting this transaction

and Q2 gets:

REPLACE
Go 
* 
Query sent to backend is "end"
END
Go 

This is fine -- better, as my last message said, than I hoped.

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

At the end of this, both Q1 and Q2 both think they have a "unique" 
number -- "2".  What is the state of tt_number?  That's the best part:

* retrieve (tt_number.tt_number)\g

Query sent to backend is "retrieve (tt_number.tt_number)"
---------------
| tt_number   |
---------------
| 3           |
---------------
| 3           |
---------------

In other words, some sort of "append" appears to have happened.

On top of that -- the OID's of the two instances are the same:

* retrieve(tt_number.oid)\g

Query sent to backend is "retrieve(tt_number.oid)"
---------------
| oid         |
---------------
| 151066      |
---------------
| 151066      |
---------------

I'm not sure how serious this is, but it doesn't seem like a good idea.
My hopes for using begin/end in this way are greatly weakened.

I hope someone can shed some light.

---

From: [email-masked] (Paul M. Aoki)
Date: Wed, 21 Apr 93 12:24:14 -0700

> WARN:Apr 21 11:56:23:WaitOnLock: error on wakeup - Aborting this transaction

right.  this is one approach to a standard locking/transaction problem.
given that timeout is done rather than deadlock prevention or more
complicated deadlock detection, this is what you would expect, modulo the 
fact that for me, the "long pause" happens when you start the replace 
statements (for the reasons i described in my last message) rather than
the end statements.

> 					replace tt_number (tt_number=3)\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.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: Jeff Meredith <[email-masked]>
Date: Wed, 21 Apr 93 14:06:50 -0700

> in the handling of dirty buffers.  i have a feeling this might be

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
[email-masked]

---

From: [email-masked] (Paul M. Aoki)
Date: Wed, 21 Apr 93 14:45:38 -0700

Jeff Meredith <[email-masked]> writes:
> that it will give you consistency problems.

hi, jeff.

there are several issues here:

(1) nat was looking for a way not to have to deal with aborts on
deadlock.  i suggested putting the update first in the xact as
a way to reduce aborts.  i have received mail from jean anderson
at saic pointing out that that it still doesn't eliminate them in her
app.  so maybe there is a window of vulnerability there after all,
or maybe the timeout value should be increased.  i will just shut 
up about this one until i have time to look at it more carefully.  
(the crowd cheers in the background..:-)

(2) nat and jean have both observed that multiple versions of
one tuple can be visible at once in the course of an xact.  i 
think this particular problem is different from what you're 
describing (this is uncommitted data)..  i think mao fixed the 
time qual code after 4.1 went out to deal with this.  i sent 
nat and jean a copy of the new tqual.c to see if this does in 
fact make their particular problem go away.. (mao!  come back 
from vienna!)

(3) seeing multiple tuples with the same oid after all the updates 
have committed/aborted is *clearly* wrong, no matter whose data 
visibility model you use :-)  that's the only reason i mentioned 
the buffer thing..

>					retrieve (pg_class.*)

hmm, your sql is showing :-)
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: Jeff Meredith <[email-masked]>
Date: Wed, 21 Apr 93 16:11:45 -0700

> (the crowd cheers in the background..:-)

a replace query of the form:

	replace foo (attribute1 = foo.attribute1 + 1)
    
will first do a scan over foo (acquiring a read lock) then attempt to
write a new instance/row/tuple (acquiring a write lock).  any time you
"upgrade" a read lock to a write lock you're vulnerable to deadlock.

> fact make their particular problem go away.. (mao!  come back 

you should take a close look at how STON87 defines when a tuple is
valid and just fix the code in HeapTupleSatisfiesNow() to reflect this.

> visibility model you use :-)  that's the only reason i mentioned 

i whole-heartedly agree.

> hmm, your sql is showing :-)

just be glad i remembered to use retrieve ;)

jeff (select * from tables;) meredith
[email-masked]