X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D1BAED1B498 for ; Fri, 28 Nov 2003 21:40:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05036-08 for ; Fri, 28 Nov 2003 17:40:12 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id EE9E3D1B437 for ; Fri, 28 Nov 2003 17:39:19 -0400 (AST) Received: (qmail 24964 invoked by uid 500); 28 Nov 2003 21:38:28 -0000 Date: Fri, 28 Nov 2003 15:38:28 -0600 From: Bruno Wolff III To: "Burr, Colin" Cc: "'pgsql-sql@postgresql.org'" Subject: Re: Bug: Sequence generator insert Message-ID: <20031128213828.GA24904@wolff.to> Mail-Followup-To: "Burr, Colin" , "'pgsql-sql@postgresql.org'" References: <7D8D2AE3E5A9DB4C8D78DE0F950ABB510BDC3F56@miaxch01.herald.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <7D8D2AE3E5A9DB4C8D78DE0F950ABB510BDC3F56@miaxch01.herald.com> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/332 X-Sequence-Number: 16147 On Tue, Nov 25, 2003 at 18:37:41 -0500, "Burr, Colin" wrote: > > However, even though the new record failed to be inserted into the table, > the sequence generator was still updated. > > The sequence generator should only be updated if the record is successfully > inserted into the table, and should not be updated if the insert fails. That isn't how sequences work. If you need a sequence of numbers without gaps then you need to use something else. Note that the something else will need to have a way to let only one transaction at a time update the counter which may be a performance problem.