Subject: incrementing counter

From: Nat Howard <[email-masked]>
Date: Wed, 21 Apr 93 11:18:10 -0400

I'd like to use postgres to keep track of an ever-increasing 
identifier for a ticket system.

How?

Although the manual mentions an increment operator, it's not
clear to me how to use it (if indeed it can be used this way) to 
generate a guaranteed-unique  ticket id.

I'd rather not use OID's, as I might have to reload the database
and having ticket id's change retrospectively is not an option.

*  retrieve(tt_number.tt_number)\g

---------------
| tt_number   |
---------------
| 3           |
---------------

>From the manual, and from some manual experience, the following should work:

	begin
	retrieve(tt_number.tt_number)
	(increment number somehow -- I did it manually -- let's say it was a 3)
	replace tt_number(tt_number=4)
	end

When I set up two of these by hand, one of the two transactions times
out, and the other finishes.

This is almost what I want, but it seems almost too good to be true.
Can anyone confirm that postgres is smart enough to understand that
the second part of this transaction may depend on information retrieved
in the first, and so is aborting the other, interleaved,  transaction?

What I *really* want, though, is a way get atomically-incremented 
values, so that neither transaction must time out:

	retrieve(increment(tt_number.tt_number))\g

Any suggestions?

---

From: [email-masked] (Paul M. Aoki)
Date: Wed, 21 Apr 93 11:01:55 -0700

Nat Howard <[email-masked]> writes:,
> values, so that neither transaction must time out:

this seems to work better:

begin
replace tt_number (tt_number = tt_number.tt_number + 1)
retrieve (tt_number.tt_number)
/* other stuff */
end

with the original solution, one xact will get a read lock,
after which one of two things can happen: either the second xact 
gets a read lock, or the first xact gets its write lock.  in the 
first case, neither xact can get a write lock (deadlock) so at 
least one xact must lose.  in the second, the first xact must 
commit before the second xact gets its read lock, and all is well.
(in the first case, there's no way postgres can decide not to grant
the second read lock because it can't read your mind and know
that you're going to upgrade to a write lock later in the xact.
putting the update up front is a way to avoid this problem.)

hmm, now that i think about it, my solution might just shorten
the window of vulnerability to something really small. (i'm not
sure that the write lock is obtained first in the replace statement.)
oh, well.

i'm on allergy drugs and sort of free-associating, so feel free to 
throw beer bottles from the stands, folks..
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki