X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C14D5D1B442 for ; Fri, 5 Dec 2003 17:29:17 +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 89372-08 for ; Fri, 5 Dec 2003 13:28:47 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 802FED1B43A for ; Fri, 5 Dec 2003 13:28:46 -0400 (AST) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id B086336FFD; Fri, 5 Dec 2003 12:28:30 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1ASJkk-0001zl-00; Fri, 05 Dec 2003 12:28:30 -0500 To: Ivar Zarans Cc: pgsql-performance@postgresql.org Subject: Re: Slow UPADTE, compared to INSERT References: <20031204185751.GA15893@alcaron.ee> <20031205014514.GA21079@alcaron.ee> <20031205020728.GA21440@alcaron.ee> <200312051008.20317.dev@archonet.com> <20031205123843.GA27170@alcaron.ee> <3FD07EEA.8030007@persistent.co.in> <20031205131325.GB27524@alcaron.ee> In-Reply-To: <20031205131325.GB27524@alcaron.ee> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 05 Dec 2003 12:28:30 -0500 Message-ID: <87ad672myp.fsf@stark.dyndns.tv> Lines: 20 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/132 X-Sequence-Number: 4992 Ivar Zarans writes: > > qry = "UPDATE table1 SET status = %s WHERE recid = '%s'" > > cursor.execute(qry, status, recid) > > Yes, this helps. But then it sort of obsoletes PyPgSQL-s own quoting > logic. I would prefer to take care of this all by myself or trust some > underlying code to do this for me. And PyPgSQL is quite nice - it > checks datatype and acts accordingly. You should tell the PyPgSQL folk to use the new binary protocol for parameters so that there are no quoting issues at all. But if it's going to interpolate strings into the query then pyPgSQL really ought to be doing '%s' as above even for numbers. This lets postgres decide what the optimal datatype is based on what you're comparing it to. Skipping the quotes will only cause headaches. -- greg