Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0
Date: Thu, 5 Aug 2004 12:50:48 -0700 (PDT)
From: Oscar Tuscon <obtuse@bmwe30.net>
To: pgsql-general@postgresql.org
Subject: Re: Sequence Question
Reply-To: obtuse@bmwe30.net
Message-Id: <20040805195048.B00977274@sitemail.everyone.net>

>>>>
I'm not sure but I don't think that's safe since nextval doesn't lock the
sequence until the setval occurs. Though it might be unlikely to actually
occur in real life.
You could create a table with as many entries as you will ever need and then
select nextval() from that table and read all the records you get. That will
avoid the many round trips but it will still be slow since you will still have
to move all those individual values to your client.
Perhaps you should give up on the idea of using sequences at all.
If you have a table with one record (or one record per similar application).
You could "SELECT n FROM counters FOR UPDATE" the value in the record, then
"UPDATE counters SET n = n+?". It would serialize your accesses which would be
bad if you had lots of clients doing small increments, but if you have few
clients doing large increments it shouldn't be a problem.
Do make sure to vacuum this table frequently though.
-- 
greg
<<<<

Thanks, I figured that, but was hoping otherwise. I realize that the timing would make it unlikely, but unfortunately I need 100% guaranteed. I have an alternative in that I control the accessing clients (my app) and can apply a lock to prevent it from happening.

I found the average select nextval() call was taking 2ms, which seems a bit slow to me. Throw in the fsync I suppose and that'd explain it.
Interestingly, in the tests I ran the minimum select nextval() was 400us, and the max was 35ms, with an average of 2ms. This was on a DL380 dual 2.4G processors, 2.5G RAM, 5x10k SCSI drives, and no load - pretty much idle (well, a processes checking for entries in a command table 10 times per second).

Oscar


_____________________________________________________________
The BMW E30 community on the web---> http://www.bmwe30.net