Re: Confusion over Python drivers - Mailing list pgsql-hackers
From | Andrew McNamara |
---|---|
Subject | Re: Confusion over Python drivers |
Date | |
Msg-id | 20100209015109.E6671206F9@longblack.object-craft.com.au Whole thread Raw |
In response to | Re: Confusion over Python drivers (Jeff Davis <pgsql@j-davis.com>) |
Responses |
Re: Confusion over Python drivers
|
List | pgsql-hackers |
>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) [(<PyPgCellname '?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) [(<PyPgCellname '?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/
pgsql-hackers by date: