Confusion over Python drivers
in [PgSql]
|
From: Andrew McNamara on 8 Feb 2010 21:56 >For a given type, the input function may be more likely to catch an >input error than the recv function; or the reverse. Either way, it is >very type-specific, and the only difference is the whether the input is >misinterpreted (type error not caught; bad) or an error is thrown (type >error caught; better). This is the crux of the matter: the type input functions are universally more forgiving since, by their nature, text formats are designed for us fuzzy humans, and users of adapters have come to expect this. -- Andrew McNamara, Senior Developer, Object Craft http://www.object-craft.com.au/ -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jeff Davis on 8 Feb 2010 23:20 On Tue, 2010-02-09 at 13:56 +1100, Andrew McNamara wrote: > >For a given type, the input function may be more likely to catch an > >input error than the recv function; or the reverse. Either way, it is > >very type-specific, and the only difference is the whether the input is > >misinterpreted (type error not caught; bad) or an error is thrown (type > >error caught; better). > > This is the crux of the matter: the type input functions are universally > more forgiving since, by their nature, text formats are designed for us > fuzzy humans, and users of adapters have come to expect this. Except that it's exactly the opposite with integers. Pass any 4 bytes to in4recv(), and it will accept it. However, try passing '4.0' to int4in(), and you get an error. If I had to make an educated guess about the forgiveness of various type input and type recv functions, I would say that the recv functions are more forgiving. After all, you would expect the binary format to be less redundant, and therefore less likely to catch inconsistencies. I don't see much of a universal truth there, however. This is getting pretty far off-topic, so let's just leave it at that. The drivers should support both formats; the type inference logic doesn't care at all about the contents of the unknown literals (text or binary); and queries should be written in such a way that the types are unambiguous and unsurprising. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Jeff Davis on 8 Feb 2010 23:32 On Mon, 2010-02-08 at 20:50 +0100, Florian Weimer wrote: > I saw your note that you have to specify the types for date values > etc. Is this really desirable or even necessary? Can't you specify > the type as unknown (OID 705, I believe)? I believe the problem that Andrew is describing is that: SELECT $1 + 1; will infer that $1 is of type int4. But if you really intended $1 to be a date (which is also valid), it will cause a problem. If the date is passed in text format, it will cause an error in int4in(), because the text representation of a date isn't a valid text representation for an integer. If the date is passed in binary format, it will pass it to int4recv() -- but because the date is 4 bytes, and int4recv is defined for any 4-byte input, it won't cause an error; it will produce a wrong result. In other words, the binary representation for a date _is_ a valid binary representation for an integer. The type inference has found the wrong type, but the recv function still accepts it, which causes a problem. The solution is to write the query in an unambiguous way: SELECT $1::date + 1; which is good practice, anyway. If it's not obvious to the type inference system, it's probably not obvious to you, and will probably surprise you ;) Or, as Andrew suggests, you can pass the type oid along with the parameter so that postgresql knows the right type. Either way, relying on a type input or a recv function to cause a type error is much more fragile. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew McNamara on 9 Feb 2010 01:53 >If the date is passed in binary format, it will pass it to int4recv() -- >but because the date is 4 bytes, and int4recv is defined for any 4-byte >input, it won't cause an error; it will produce a wrong result. In other >words, the binary representation for a date _is_ a valid binary >representation for an integer. The type inference has found the wrong >type, but the recv function still accepts it, which causes a problem. Yes - of the worst kind: silent data corruption. >The solution is to write the query in an unambiguous way: > > SELECT $1::date + 1; > >which is good practice, anyway. If it's not obvious to the type >inference system, it's probably not obvious to you, and will probably >surprise you ;) That address this specific case, but it's ugly and not general. The right thing is to set the correct type when you're marshalling the parameters... >Or, as Andrew suggests, you can pass the type oid along with the >parameter so that postgresql knows the right type. That's right - if using the binary parameters, you *must* pass an appropriate type oid for the data you send to the server. If you use the "unknown" oid, bad things will happen (sooner or later). While this is strictly true of both binary and text parameters, text parameters have enough redundancy built into the format that it's rarely a problem. Users have come to expect this leniency. -- Andrew McNamara, Senior Developer, Object Craft http://www.object-craft.com.au/ -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew McNamara on 11 Feb 2010 00:26
>That's just a matter of prioritizing the issues. Put the big ones at >the top, the trivia at the bottom, [...] I'd like to see a requirement for the use of PQexecParams() over PQexec() - even when using libpq's PQescapeStringConn(), PQexec() makes me uneasy. -- Andrew McNamara, Senior Developer, Object Craft http://www.object-craft.com.au/ -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |