Re: Confusion over Python drivers

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: Florian Weimer <fw(at)deneb(dot)enyo(dot)de>
Cc: Andrew McNamara <andrewm(at)object-craft(dot)com(dot)au>, Greg Smith <greg(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Confusion over Python drivers
Date: 2010-02-09 04:32:26
Message-ID: 1265689946.29919.2583.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2010-02-09 04:32:31 Re: Hot standby documentation
Previous Message M Z 2010-02-09 04:24:46 Re: CVS checkout source code for different branches