X-Original-To: pgsql-bugs@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id D753792615E for ; Mon, 2 Jun 2003 03:14:28 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 07349-08 for ; Mon, 2 Jun 2003 03:14:18 -0400 (EDT) Received: from mail.quantium.net (b.ns.quantium.net [67.96.140.30]) by developer.postgresql.org (Postfix) with SMTP id 7EA26926072 for ; Mon, 2 Jun 2003 03:14:17 -0400 (EDT) Received: (qmail 30532 invoked from network); 2 Jun 2003 07:14:15 -0000 Received: from 20-pool1.ras10.coden-le3.alerondial.net (HELO dave.to) (206.149.128.20) by quantium.net with SMTP; 2 Jun 2003 07:14:15 -0000 Message-ID: <3EDAF956.9010307@dave.to> Date: Mon, 02 Jun 2003 01:14:30 -0600 From: Dave E Martin XXIII User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020623 Debian/1.0.0-0.woody.1 X-Accept-Language: en MIME-Version: 1.0 To: Bruno Wolff III Cc: pgsql-bugs@postgresql.org Subject: Re: Index speeds up one row table (why)? References: <3ED93338.80806@dave.to> <1054434573.11968.42.camel@jester> <3ED9A923.90500@dave.to> <20030601190409.GA7853@wolff.to> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new X-Archive-Number: 200306/13 X-Sequence-Number: 6108 Bruno Wolff III wrote: >Maybe you should reconsider how badly you want the app to be totally database >agnostic? Using a sequence might be less of a contortion than using vacuum >a few times a minute. You are likely to have similar performance issues >with other databases, so this section of code may not turn out to be very >portable in any case. > > Maybe I can further abstract out the generate unique-id portion, Since unique-id generation does seem to be a pretty common database extension (for some reason...), and then provide a generic schema definition, and a postgresql specific one (along with whatever others I can drum up). The generic one will rely on the software to come up with the unique id in the fashion I'm currently doing. Speaking of which, is there a better way than what i'm currently doing (when the database doesn't have any such support)? I've heard of one method based on something like "select max(id)+1 from table" but this seems error prone, at the very least, you'd have to have a unique index, and be prepared to redo on failure, which could get messy if its a big transaction, and frequent if there is a lot of concurrent inserting going on.