Re: queries against CIDR fail against 8.0.3?

On Thu, 29 Sep 2005, Russell Francis wrote:

> I have a web application which is running against PG 7.3.9 and seems to
> work without a problem.  Recently, I have been trying to run it against
> 8.0.3.  In both cases, I am using the jdbc3-8.0-312 driver.
>
> PreparedStatement s = dbConn.prepareStatement(
> "SELECT * FROM institution WHERE ( institution.network >>=  ? ) LIMIT 1" );
> s.setObject( 1, (String)request.getRemoteAddr() );
> if( s.execute() )
> {
>     ...
> }
>
> DEBUG http-8180-Processor25 net.fitne.vlrc.actions.IndexAction - ERROR:
> operator does not exist: cidr >>= character varying
> net.ev.dao.DAOException: ERROR: operator does not exist: cidr >>=
> character varying
>
> Does anyone have any ideas on how to address this issue?  Or at least an
> explanation as to why it works in 7.3.9 but not 8.0.3?
>

The 8.0 driver has added full V3 protocol support which is not available
in 7.3 servers, so it falls back to using the V2 protocol when connecting
to the 7.3 server.  Now, the 8.0 server fails because the V3 protocol uses
real prepared statements.  When you call setString() you are telling the
driver that you will be passing a string parameter, so it prepares a
server side statement taking a string data type.  This is the difference
between:

V2: WHERE network >>= '10.1.3.1'
V3: WHERE network >>= '10.1.3.1'::varchar

The first treats the parameter as an unknown literal which allows more
liberal casting while the second has the parameter type somewhat nailed
down.

The easiest solution is to write your query as "WHERE network >>= ?::cidr"
to so you get the correct type.

Kris Jurka