I just spent 2 days tracking this error down in my own code, actually. What I wound up doing is having the two places where I generate the queries (everything in my system goes through those two points, as I'm using a middleware layer) check values used as identifying fields for the presence of a bigint, and if one exists, replaces it with a wrapper that does the coerced-string representation:

                       class Wrap:
                           def __init__( self, value ):
                               self.value = value
                           def __str__( self ):
                               return "'%s'::bigint"%(self.value,)
                           __repr__ = __str__
                       value = Wrap(value)

Just doing that for the indexing/identifying values ATM. pyPgSQL will back up to using simple repr for the object (rather than raising an error as it would if you were using a formatted string), but will otherwise treat it as a regular value for quoting and the like, so no other modifications to the code required.

By no means an elegant fix, but since your post (well, the resulting thread) managed to solve my problem, figured I should at least tell everyone thanks and how I worked around the problem. You wouldn't want this kind of hack down in the pyPgSQL level I would think, as it's DB-version specific. I suppose you could alter the __repr__ of the PgInt8 class/type to always use the string or coerced form, but it seems wrong to me. I'm actually hesitant to include it in our own middleware layer, but oh well, it does seem to be necessary for even somewhat reasonable performance.

BTW, my case was a largish (88,000 record) table with a non-unique bigint key, explain on update shows sequential search, while with 'int'::bigint goes to index search. Using pyPgSQL as the interface to 7.3.4 and 7.3.3.


Ivar Zarans wrote:

On Fri, Dec 05, 2003 at 10:08:20AM +0000, Richard Huxton wrote:


I am using PyPgSQL for PostgreSQL access and making update queries as this:


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.


Finally - what would be correct solution to this problem? Upgrading to
7.5 CVS is not an option :) One possibility is not to use PyPgSQL
variable substitution and create every query "by hand" - not very nice
solution, since variable substitution and quoting is quite convenient.

Second (and better) possibility is to ask PyPgSQL develeopers to take care
of PostgreSQL oddities.

Any other suggestions?

_______________________________________ Mike C. Fletcher Designer, VR Plumber, Coder http://members.rogers.com/mcfletch/

---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster

Reply via email to