Re: Confusion over Python drivers

>On Tue, 2010-02-09 at 10:46 +1100, Andrew McNamara wrote:
>> The problem is deeper than that - when query parameters use the binary
>> option, the server has no way to decode the binary parameter without an
>> appropriate type OID.
>
>Postgres does not attempt to decode anything (text or binary format)
>until it figures out what type it is.

How does it figure out what type it is? Either by the type oid passed by
the caller, or by the context if the type oid is "unknown".

Now, with the text format parameters, the parser usually does the right
thing, since text formats have plenty of hints for us humans.

However, with the binary format, unless the caller tells us, there's no way
to tell whether we're correctly parsing the data. If the context implies
one type, but the user passes another, we'll either get an ugly error or,
worse, silently misparse their data.

Generally this isn't a big problem with python, as we have good type
information available. It's only an issue because people have gotten
used to the text parameter parsing being so forgiving.

Using my ocpgdb module, and interacting directly with the libpq wrapping
code, you can see how postgres reacts to various inputs:

>>> from oclibpq import *
>>> from ocpgdb import pgoid
>>> db=PgConnection('')

No parameters:

>>> r=db.execute('select 1', ())
>>> r.status
PGRES_TUPLES_OK
>>> list(r)
[(<PyPgCell name '?column?', type 23, modifier -1, value '\x00\x00\x00\x01' at 0xb7514200>,)]

Int4 parameter, type specified:

>>> r=db.execute('select $1', [(pgoid.int4, '\x00\x00\x00\x01')])
>>> r.status
PGRES_TUPLES_OK
>>> list(r)
[(<PyPgCell name '?column?', type 23, modifier -1, value '\x00\x00\x00\x01' at 0xb75141c0>,)]

Int4 parameter, type unknown, can't be determined from context:

>>> r=db.execute('select $1', [(pgoid.unknown, '\x00\x00\x00\x01')])
>>> r.status
PGRES_FATAL_ERROR
>>> r.errorMessage
'ERROR: could not determine data type of parameter $1\n'

Int4 parameter, type unknown, can be determined from context:

>>> r=db.execute('select $1 + 1', [(pgoid.unknown, '\x00\x00\x00\x01')])
>>> r.status
PGRES_TUPLES_OK
>>> list(r)
[(<PyPgCell name '?column?', type 23, modifier -1, value '\x00\x00\x00\x02' at 0xb7514200>,)]

Text parameter, type unknown, mismatching context - surprising:

>>> r=db.execute('select $1 + 1', [(pgoid.unknown, '1111')])
>>> r.status
PGRES_TUPLES_OK
>>> list(r)
[(<PyPgCell name '?column?', type 23, modifier -1, value '1112' at 0xb7514360>,)]

Date parameter, type unknown, int context, the value gets misinterpreted:

>>> r=db.execute('select $1 + 1', [(pgoid.unknown, '\x00\x00\x01n')])
>>> r.status
PGRES_TUPLES_OK
>>> list(r)
[(<PyPgCell name '?column?', type 23, modifier -1, value '\x00\x00\x01o' at 0xb75144a0>,)]

--
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/