pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: 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, 05 Dec 2003 19:21:38 +0530
Message-ID: <3FD08D6A.4080207@persistent.co.in> (raw)
In-Reply-To: <20031205131325.GB27524@alcaron.ee>
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>

Ivar Zarans wrote:
> On Fri, Dec 05, 2003 at 06:19:46PM +0530, Shridhar Daithankar wrote:
> 
> 
>>>is correct SQL, but not correct, considering PostgreSQL bugs.
>>Personally I don't consider a bug but anyways.. You are the one facing 
>>problem so I understand..
> Well, if this is not bug, then what is consideration behind this
> behaviour? BTW, according to Cristopher it is fixed in 7.5 CVS.
> Why fix it if this is not a bug? :))

This is not a bug. It is just that people find it confusing when postgresql 
planner consider seemingly same type as different. e.g. treating int8 as 
different than int4. Obvious thinking is they should be same. But given 
postgresql's flexibility with create type, it is difficult to promote.

AFAIK, the fix in CVS is to make indexes operatable with seemingly compatible 
types. Which does not change the fact that postgresql can not upgrade data types 
on it's own.

Write good queries which adhere to strict data typing. It is better to 
understand anyway.

> One more question - is this "feature" related only to "bigint" fields,
> or are other datatypes affected as well?

Every data type is affected. int2 will not use a int4 index and so on.

>>Will following help?
>>
>>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.

Well, then pypgsql should be upgraded to query the pg catalogd to find exact 
type of column. But that would be too cumbersome I guess.

  Shridhar




view thread (24+ messages)  latest in thread

Message-ID: <3FD08D6A.4080207@persistent.co.in>
Permalink:  ../3FD08D6A.4080207@persistent.co.in/
Also on:    postgresql.org/message-id/3FD08D6A.4080207@persistent.co.in

 · 

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: shridhar_daithankar@persistent.co.in, iff@alcaron.ee
  Subject: Re: Slow UPADTE, compared to INSERT
  In-Reply-To: <3FD08D6A.4080207@persistent.co.in>

* 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