Re: 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

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