Обсуждение: queries against CIDR fail against 8.0.3?
Greetings,
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.
The application fails when running against 8.0.3 at this query.
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
at net.ev.dao.DatabaseDAO.executeStatement(DatabaseDAO.java:656)
at net.ev.dao.DatabaseDAO.find(DatabaseDAO.java:1199)
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?
Thanks in advance,
Russ
Вложения
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
Russell Francis wrote: > PreparedStatement s = dbConn.prepareStatement( > "SELECT * FROM institution WHERE ( institution.network >>= ? ) LIMIT 1" ); > s.setObject( 1, (String)request.getRemoteAddr() ); > net.ev.dao.DAOException: ERROR: operator does not exist: cidr >>= > character varying You will need to either create a PGobject subclass that returns the correct typename (cidr), or explicitly cast to cidr in your query: SELECT * FROM institution WHERE ( institution.network >>= ?::cidr ) LIMIT 1 > 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 drivers type parameters more strongly than earlier drivers due to a change in the protocol used, so setObject(String) is passing the parameter explicitly as a 'text' value not as an untyped literal that gets implicitly casted to cidr. See the archives for more details. -O
Oliver Jowett wrote: > Russell Francis wrote: > > You will need to either create a PGobject subclass that returns the > correct typename (cidr), or explicitly cast to cidr in your query: > > SELECT * FROM institution WHERE ( institution.network >>= ?::cidr ) > LIMIT 1 > > >>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 drivers type parameters more strongly than earlier drivers due > to a change in the protocol used, so setObject(String) is passing the > parameter explicitly as a 'text' value not as an untyped literal that > gets implicitly casted to cidr. See the archives for more details. Oliver & Kris, Thanks much both of your responses were very helpful. I have decided to subclass PGobject to address the issue and have created a PGcidr class which seems to be working well. If there is any interest in adding this to the project, I would be happy to submit what I have for review and also provide PGinet and PGmacaddr classes to the core. Thanks again, Russ
On Fri, 30 Sep 2005, Russell Francis wrote: > Thanks much both of your responses were very helpful. I have decided to > subclass PGobject to address the issue and have created a PGcidr class which > seems to be working well. > > If there is any interest in adding this to the project, I would be happy > to submit what I have for review and also provide PGinet and PGmacaddr > classes > to the core. > Seems reasonable for us to offer a PGxxx class for every core backend type. Send them in and we'll take a look... Kris Jurka
>
> Seems reasonable for us to offer a PGxxx class for every core backend
> type. Send them in and we'll take a look...
>
> Kris Jurka
>
Kris,
Attached is an implementation for the proposed PGcidr & PGinet classes.
I have tried to follow the formatting conventions used in the geometric
extensions but is is a first attempt, please let me know if there is
anything else I should/could do to improve these.
I will take any comments / suggestions and incorporate them into the
PGmacaddr class also.
Cheers,
Russ
/*-------------------------------------------------------------------------
*
* Copyright (C) 2005, PostgreSQL Global Development Group
*
*--------------------------------------------------------------------------
*/
package org.postgresql.net;
import java.io.Serializable;
import java.sql.SQLException;
import org.postgresql.util.GT;
import org.postgresql.util.PGobject;
import org.postgresql.util.PGtokenizer;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
/**
* This represents org.postgresql's cidr datatype, which is
* used to hold network addresses.
*
* <p>
* This class wraps the postgresql specific CIDR datatype. It
* supports IPV4 network addresses in the following format.
* <p>
* a[.b[.c[.d]]][/e]
*
* @author Russell Francis < rfrancis@ev.net >
*/
public class PGcidr extends PGobject implements Serializable, Cloneable
{
protected int hashValue;
/**
* This constructor takes a string in the
* cidr format a[.b[.c[.d]]][/e] and creates
* a PGcidr to represent it.
*
* @param s The representation of the cidr as a string.
* @exception SQLException if the string is not in the proper format.
*/
public PGcidr( String s )
throws SQLException
{
this();
setValue( s );
}
/**
* A simple constructor.
*/
public PGcidr()
{
setType( "cidr" );
}
/**
* Set the value of this CIDR.
*
* <p>This accepts strings in the a[.b[.c[.d]]][/netmask] format.
*
* @param v The string representation of this network address.
* @exception SQLException If it is not in a valid cidr format.
*/
public void setValue( String v )
throws SQLException
{
int a = 0;
int b = 0;
int c = 0;
int d = 0;
int netmask = -1;
if( v == null )
{
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v } ), PSQLState.DATA_TYPE_MISMATCH ) );
}
PGtokenizer t = new PGtokenizer( v, '/' );
int size = t.getSize();
if( ( size != 1 ) && ( size != 2 ) )
{
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v } ), PSQLState.DATA_TYPE_MISMATCH ) );
}
try
{
if( size == 2 )
{
// we have a netmask to read
netmask = Integer.valueOf( t.getToken( 1 ) ).intValue();
if( ( netmask < 0 ) || ( netmask > 32 ) )
{
throw( new PSQLException(
GT.tr( "Conversion to type {0} failed: {1}.", new Object[]{ type, v } ),
PSQLState.DATA_TYPE_MISMATCH ) );
}
}
// read the body a.b.c.d
t = new PGtokenizer( t.getToken( 0 ), '.' );
size = t.getSize();
if( ( size < 1 ) || ( size > 4 ) )
{
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v } ), PSQLState.DATA_TYPE_MISMATCH ) );
}
a = Integer.valueOf( t.getToken( 0 ) ).intValue();
if( size >= 2 )
{
b = Integer.valueOf( t.getToken( 1 ) ).intValue();
}
if( size >= 3 )
{
c = Integer.valueOf( t.getToken( 2 ) ).intValue();
}
if( size >= 4 )
{
d = Integer.valueOf( t.getToken( 3 ) ).intValue();
}
}
catch( NumberFormatException e )
{
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v }), PSQLState.DATA_TYPE_MISMATCH, e ) );
}
if( ( a < 0 ) || ( a > 255 ) ||
( b < 0 ) || ( b > 255 ) ||
( c < 0 ) || ( c > 255 ) ||
( d < 0 ) || ( d > 255 ) )
{
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v } ), PSQLState.DATA_TYPE_MISMATCH ) );
}
// If the netmask is not set in the parameter,
// we will take a guess like PG does.
//
// 1.0.0.0 - 127.0.0.0 - class A, netmask 8
// 128.0.0.0 - 191.0.0.0 - class B, netmask 16
// 192.0.0.0 - 223.0.0.0 - class C, netmask 24
if( netmask == -1 )
{
// start with a safe default, we will try to trim
// this down depending on the values of a,b,c & d.
netmask = 32;
if( a >= 1 || a <= 127 )
{
if( b == 0 && c == 0 && d == 0 )
{
netmask = 8;
}
else if( c == 0 && d == 0 )
{
netmask = 16;
}
else if( d == 0 )
{
netmask = 24;
}
}
else if( a >= 128 && a <= 191 )
{
if( c == 0 && d == 0 )
{
netmask = 16;
}
else if( d == 0 )
{
netmask = 24;
}
}
else if( a >= 192 && a <= 223 )
{
if( d == 0 )
{
netmask = 24;
}
}
}
// verify that there are no bits to the right of the netmask
if( netmask < 32 )
{
int address = ( a << 24 ) | ( b << 16 ) | ( c << 8 ) | d;
address <<= netmask;
if( address != 0 )
{
// There are bits to the right of the netmask
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v }), PSQLState.DATA_TYPE_MISMATCH ) );
}
}
// Seems like it will generate a decent hash?
this.hashValue = ((a ^ b) << 24) | ((b ^ c) << 8) | ((c ^ d) << 16) | (d ^ netmask);
// ok, the parameter cleared all of out tests,
// a.b.c.d/netmask should contain out new CIDR value.
this.value = "" + a + "." + b + "." + c + "." + d + "/" + netmask;
}
/**
* Get the hash code for this network address.
*
* @return The hash value for this object.
*/
public int hashCode()
{
return( this.hashValue );
}
/**
* Compare two PGcidr objects for equality.
*
* <p>This will return true if the parameter obj is of type PGcidr
* and represents the same network as this.
*
* @param obj The object which we wish to compare.
* @return true if it represents the same network as this, false otherwise.
*/
public boolean equals( Object obj )
{
if( obj instanceof PGcidr )
{
PGcidr cidr = (PGcidr)obj;
if( this.getValue().equals( cidr.getValue() ) )
{
return( true );
}
}
return( false );
}
/**
* This will make a duplicate of the current PGcidr object.
*
* @return null on failure, or a new PGcidr object which
* represents the same network address as the invoking
* object.
*/
public Object clone()
{
try
{
return( new PGcidr( this.getValue() ) );
}
catch( SQLException e )
{
return( null );
}
}
}
/*-------------------------------------------------------------------------
*
* Copyright (C) 2005, PostgreSQL Global Development Group
*
*--------------------------------------------------------------------------
*/
package org.postgresql.net;
import java.io.Serializable;
import java.sql.SQLException;
import org.postgresql.util.GT;
import org.postgresql.util.PGobject;
import org.postgresql.util.PGtokenizer;
import org.postgresql.util.PSQLException;
import org.postgresql.util.PSQLState;
/**
* This represents org.postgresql's inet datatype, which is used
* to hold IPV4 network addresses and ip addresses.
*
* <p>
* This class wraps the postgresql specific INET datatype. It supports
* values in the following format.
* <p>
* a.b.c.d[/netmask]
*
* @author Russell Francis < rfrancis@ev.net >
*/
public class PGinet extends PGobject implements Serializable, Cloneable
{
private int hashValue;
/**
* This constructor takes a string in the inet format
* a.b.c.d[/netmask] and creates a new PGinet to
* represent it.
*
* @param s The string representation of the inet value.
* @exception SQLException If the string is invalid.
*/
public PGinet( String s )
throws SQLException
{
this();
this.setValue( s );
}
/**
* A simple constructor.
*/
public PGinet()
{
setType( "inet" );
}
/**
* This method sets the value of this PGinet object.
*
* @param v A string representation of an inet address a.b.c.d[/netmask]
* @exception SQLException If the parameter is not a valid inet address.
*/
public void setValue( String v )
throws SQLException
{
if( v == null )
{
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v } ), PSQLState.DATA_TYPE_MISMATCH ) );
}
PGtokenizer t = new PGtokenizer( v, '/' );
int size = t.getSize();
if( ( size != 1 ) && ( size != 2 ) )
{
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v } ), PSQLState.DATA_TYPE_MISMATCH ) );
}
int a = 0;
int b = 0;
int c = 0;
int d = 0;
int netmask = 32;
try
{
if( size == 2 )
{
netmask = Integer.valueOf( t.getToken( 1 ) ).intValue();
if( ( netmask < 0 ) || ( netmask > 32 ) )
{
throw( new PSQLException(
GT.tr( "Conversion to typ {0} failed: {1}.", new Object[]{ type, v } ),
PSQLState.DATA_TYPE_MISMATCH ) );
}
}
t = new PGtokenizer( t.getToken( 0 ), '.' );
if( t.getSize() != 4 )
{
throw( new PSQLException(
GT.tr( "Conversion to typ {0} failed: {1}.", new Object[]{ type, v } ),
PSQLState.DATA_TYPE_MISMATCH ) );
}
a = Integer.valueOf( t.getToken( 0 ) ).intValue();
b = Integer.valueOf( t.getToken( 1 ) ).intValue();
c = Integer.valueOf( t.getToken( 2 ) ).intValue();
d = Integer.valueOf( t.getToken( 3 ) ).intValue();
}
catch( NumberFormatException e )
{
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v } ), PSQLState.DATA_TYPE_MISMATCH, e ) );
}
// ensure that the values are within a valid range.
if( ( a < 0 ) || ( a > 255 ) ||
( b < 0 ) || ( b > 255 ) ||
( c < 0 ) || ( c > 255 ) ||
( d < 0 ) || ( d > 255 ) )
{
throw( new PSQLException( GT.tr( "Conversion to type {0} failed: {1}.",
new Object[]{ type, v } ), PSQLState.DATA_TYPE_MISMATCH ) );
}
this.hashValue = ((a ^ c) << 24) | ((b ^ d) << 16) | ((b ^ c) << 8) | (d ^ netmask);
this.value = "" + a + "." + b + "." + c + "." + d + ((netmask == 32) ? "" : ("/" + netmask));
}
/**
* Get the hash code for this network address.
*
* @return The hash value for this object.
*/
public int hashCode()
{
return( this.hashValue );
}
/**
* Compare two PGinet's for equality.
*
* @param obj The object which we wish to compare.
* @return true if it represents the same network or ip address
* as this PGinet, false otherwise.
*/
public boolean equals( Object obj )
{
if( obj instanceof PGinet )
{
PGinet inet = (PGinet)obj;
if( this.toString().equals( inet.toString() ) )
{
return( true );
}
}
return( false );
}
/**
* Make a duplicate of this PGinet object.
*
* @return null on failure, or a new PGinet address
* which is equal to this object.
*/
public Object clone()
{
try
{
return( new PGinet( this.getValue() ) );
}
catch( SQLException e )
{
return( null );
}
}
}
Russell Francis wrote: >>Seems reasonable for us to offer a PGxxx class for every core backend >>type. Send them in and we'll take a look... >> >>Kris Jurka >> > > > Kris, > > Attached is an implementation for the proposed PGcidr & PGinet classes. > I have tried to follow the formatting conventions used in the geometric > extensions but is is a first attempt, please let me know if there is > anything else I should/could do to improve these. > > I will take any comments / suggestions and incorporate them into the > PGmacaddr class also. > > Cheers, > Russ Just realized that INET and CIDR also support IPV6 addresses too. I feel dumb :) let me take another stab at this. Sorry, Russ
Russell Francis wrote: > Russell Francis wrote: > >>>Seems reasonable for us to offer a PGxxx class for every core backend >>>type. Send them in and we'll take a look... >>> >>>Kris Jurka >>> >> >> >>Kris, >> >>Attached is an implementation for the proposed PGcidr & PGinet classes. >> I have tried to follow the formatting conventions used in the geometric >>extensions but is is a first attempt, please let me know if there is >>anything else I should/could do to improve these. >> >>I will take any comments / suggestions and incorporate them into the >>PGmacaddr class also. >> >>Cheers, >>Russ > > > Just realized that INET and CIDR also support IPV6 addresses too. I > feel dumb :) let me take another stab at this. Hello all, I have completed what I think is a stable and well tested addition to the JDBC driver for PG's extended network based types inet, cidr and macaddr. For anyone who is interested at the following URL http://jdbc.ev.net/ There are 7 files available for review and hopefully inclusion in the next release if they are deemed fit. A unified diff against the 8.1-dev-401 src This includes the 3 new types, 3 Test classes as well as minor modifications to AbstractJdbc2Connection.java and PGtokenizer.java ------------------------------------- PGNetworkTypes.diff Implementation of the 3 types. ------------------------------------- PGcidr.java PGinet.java PGmacaddr.java JUnit Tests of the 3 types. ------------------------------------- PGcidrTest.java PGinetTest.java PGmacaddrTest.java Please let me know if there is anything I can do to aid with this addition. Cheers, Russ
On Mon, 3 Oct 2005, Russell Francis wrote:
> I have completed what I think is a stable and well tested addition to
> the JDBC driver for PG's extended network based types inet, cidr and macaddr.
>
I haven't fully reviewed this patch, but I'm going on vacation for two
weeks tomorrow, so I wanted to at least get back to you with my initial
observations.
1) It fails to pass the tests you've provided:
[junit] Testcase: testPGinetIPv4InvalidAddresses(org.postgresql.test.net.PGinetTest): FAILED
[junit] An invalid address was turned into a PGinet object: 255.255.1/23' failed.
[junit] junit.framework.AssertionFailedError: An invalid address was turned into a PGinet object: 255.255.1/23'
failed.
[junit] at org.postgresql.test.net.PGinetTest.testPGinetIPv4InvalidAddresses(PGinetTest.java:70)
2) It uses Inet[4|6]Address which is only available in JDK1.4+ while the
driver must compile with JDK1.2.
Kris Jurka
Kris Jurka wrote: > > > On Mon, 3 Oct 2005, Russell Francis wrote: > >> I have completed what I think is a stable and well tested addition to >> the JDBC driver for PG's extended network based types inet, cidr and >> macaddr. >> > > I haven't fully reviewed this patch, but I'm going on vacation for two > weeks tomorrow, so I wanted to at least get back to you with my initial > observations. Kris, Thanks for taking a partial look at it, I think your comments give me enough insight to make another attempt and ensure it will compile against jdk1.2. > 1) It fails to pass the tests you've provided: > > [junit] Testcase: > testPGinetIPv4InvalidAddresses(org.postgresql.test.net.PGinetTest): > FAILED > [junit] An invalid address was turned into a PGinet object: > 255.255.1/23' failed. > [junit] junit.framework.AssertionFailedError: An invalid address was > turned into a PGinet object: 255.255.1/23' failed. > [junit] at > org.postgresql.test.net.PGinetTest.testPGinetIPv4InvalidAddresses(PGinetTest.java:70) This is strange, I am unable to duplicate this on my machine (FreeBSD/jdk1.4.2/x86) I will see if I can duplicate this on a different machine. Out of curiosity, what OS/jdk/cpu are you using? > > 2) It uses Inet[4|6]Address which is only available in JDK1.4+ while the > driver must compile with JDK1.2. > Good point, I wasn't aware of that but I guess I won't be able to use the InetAddress classes. I will try to have another version of this patch which addresses the above issues for you when you get back from your vacation. Cheers, Russ