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 E65C0D1B459 for ; Fri, 5 Dec 2003 13:24:20 +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 52549-02 for ; Fri, 5 Dec 2003 09:23:53 -0400 (AST) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id 74625D1B44A for ; Fri, 5 Dec 2003 09:23:49 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 1ASFvv-000Bfj-0W; Fri, 05 Dec 2003 13:23:47 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id 2B8CE1789C; Fri, 5 Dec 2003 13:23:46 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 41C731789A; Fri, 5 Dec 2003 13:23:44 +0000 (GMT) From: Richard Huxton To: Shridhar Daithankar , Ivar Zarans Subject: Re: Slow UPADTE, compared to INSERT Date: Fri, 5 Dec 2003 13:23:43 +0000 User-Agent: KMail/1.5 Cc: pgsql-performance@postgresql.org References: <20031204185751.GA15893@alcaron.ee> <20031205123843.GA27170@alcaron.ee> <3FD07EEA.8030007@persistent.co.in> In-Reply-To: <3FD07EEA.8030007@persistent.co.in> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200312051323.43966.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/124 X-Sequence-Number: 4984 On Friday 05 December 2003 12:49, Shridhar Daithankar wrote: > Ivar Zarans wrote: > > It seems, that PyPgSQL query quoting is not aware of this performance > > problem (to which Cristopher referred) and final query, sent to server > > is correct SQL, but not correct, considering PostgreSQL bugs. > > Will following help? > > qry = "UPDATE table1 SET status = %s WHERE recid = '%s'" > cursor.execute(qry, status, recid) Better IMHO would be: "UPDATE table1 SET status = %s WHERE recid = %s::int8" PG is very strict regarding types - normally a good thing, but it can hit you unexpectedly in this scenario. The reason is that the literal number is treated as int4, whereas quoted it is marked as type unknown. Unkown gets cast to int8, whereas int4 gets left as-is. If you want to know why int4 doesn't get promoted to int8 automatically, browse the hackers list for the last couple of years. -- Richard Huxton Archonet Ltd