pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: Ivar Zarans <iff@alcaron.ee>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow UPADTE, compared to INSERT
Date: Fri, 5 Dec 2003 13:23:43 +0000
Message-ID: <200312051323.43966.dev@archonet.com> (raw)
In-Reply-To: <3FD07EEA.8030007@persistent.co.in>
References: <20031204185751.GA15893@alcaron.ee>
	<20031205123843.GA27170@alcaron.ee>
	<3FD07EEA.8030007@persistent.co.in>

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



view thread (24+ messages)  latest in thread

Message-ID: <200312051323.43966.dev@archonet.com>
Permalink:  ../200312051323.43966.dev@archonet.com/
Also on:    postgresql.org/message-id/200312051323.43966.dev@archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: dev@archonet.com, shridhar_daithankar@persistent.co.in, iff@alcaron.ee
  Subject: Re: Slow UPADTE, compared to INSERT
  In-Reply-To: <200312051323.43966.dev@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox