Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.10.1/8.10.1) with ESMTP id e7PEZhI62353 for ; Fri, 25 Aug 2000 10:35:43 -0400 (EDT) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-interfaces@postgresql.org; Fri, 25 Aug 2000 09:35:42 -0500 (CDT) Date: Fri, 25 Aug 2000 09:35:42 -0500 From: "Ross J. Reedstrom" To: pgsql-interfaces@postgresql.org Subject: Re: JDBC problem Message-ID: <20000825093542.A22705@rice.edu> Mail-Followup-To: pgsql-interfaces@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: ; from cedarc@visionforisrael.com on Fri, Aug 25, 2000 at 11:49:32AM +0300 X-Archive-Number: 200008/155 On Fri, Aug 25, 2000 at 11:49:32AM +0300, Cedar Cox wrote: > > > One detail that seems to have an advantage over the MAX+1 method is that > in the case of adding a new record, deleting it, then adding another new > record the ID will not be reused (correct?). > > Question: What happens when transactions enter the picture? Will you get > duplicate values, or holes, or does it work just fine? Well, in my opinion it works just fine, but that does mean you get holes. This has been discussed do death over on GENERAL a number of times. In order to avoid locking on the serial seequence generator, multiple transacations each get to increment the counter, but do not roll it back on transaction failure. And each connection maintains it's own state of the underlying sequence, so that currval('table_field_seq') is multiuser safe. And as you mention above, not reusing numbers also leads to holes, so your app needs to be able to handle that, anyway. Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005