Re: Slow UPADTE, compared to INSERT

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