| From: | Richard Huxton <dev(at)archonet(dot)com> |
|---|---|
| To: | Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in>, Ivar Zarans <iff(at)alcaron(dot)ee> |
| Cc: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Slow UPADTE, compared to INSERT |
| Date: | 2003-12-05 13:23:43 |
| Message-ID: | 200312051323.43966.dev@archonet.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Shridhar Daithankar | 2003-12-05 13:51:38 | Re: Slow UPADTE, compared to INSERT |
| Previous Message | Ivar Zarans | 2003-12-05 13:13:25 | Re: Slow UPADTE, compared to INSERT |