Thread: Problem with PGStatement.getLastOID()

Hi!

I hope you can help me with a problem with the jdbc-driver devel version
(need it for 7.4).

With driver version pg73jdbc the following works fine:

int rowCount = -1;
long insertedOid = -1;
Connection con = null;
Statement stmt = null;

con = sec.getConnection(); // gets pooled connection
stmt = con.createStatement();
con.setAutoCommit(false);
rowCount = stmt.executeUpdate(query);
con.commit();
insertedOid = ((org.postgresql.PGStatement)stmt).getLastOID();
con.setAutoCommit(true);
con.close();

With devel version I get ClassCastException on row with insertedOid =
((org.postgresql.PGStatement)stmt).getLastOID();

Version 73 drivers were tested against our production server which is
running postgres 7.3 and devel drivers were tested against my own own
machine running 7.4 on Cygwin.

I hope you can help me with this.

B Rgds,
JariP

On Tue, Dec 02, 2003 at 12:50:26AM +0200, ListMan wrote:
> Hi!
>
> I hope you can help me with a problem with the jdbc-driver devel version
> (need it for 7.4).
>
> With driver version pg73jdbc the following works fine:
>
> int rowCount = -1;
> long insertedOid = -1;
> Connection con = null;
> Statement stmt = null;
>
> con = sec.getConnection(); // gets pooled connection
> stmt = con.createStatement();
> con.setAutoCommit(false);
> rowCount = stmt.executeUpdate(query);
> con.commit();
> insertedOid = ((org.postgresql.PGStatement)stmt).getLastOID();
> con.setAutoCommit(true);
> con.close();
>
> With devel version I get ClassCastException on row with insertedOid =
> ((org.postgresql.PGStatement)stmt).getLastOID();
>
> Version 73 drivers were tested against our production server which is
> running postgres 7.3 and devel drivers were tested against my own own
> machine running 7.4 on Cygwin.

Your comment above implies you're using a connection pool; is it possible
that you're actually getting a wrapper Connection/Statement implemented by
the pool infrastructure, rather than the Connection/Statement objects handed
out by the driver directly? What is the concrete class of 'stmt' reported in
the ClassCastException?

Alternatively it may be a classloading difference between the two machines
-- the cast will fail if your code and the JDBC code use versions of
org.postgresql.PGStatement loaded by different classloaders.

Either way, it sounds like the two environments differ by more than just
driver versions. Try the 7.3 drivers you use in production on your Cygwin
box (against a 7.3 server if necessary) and see if you get the same failure.

-O

Have to agree with Oliver, this is some weird classpath issue, ie
building with one library, running with another?

I just added a test case into MiscTest.java and it runs fine.

Dave
On Mon, 2003-12-01 at 17:50, ListMan wrote:
> Hi!
>
> I hope you can help me with a problem with the jdbc-driver devel version
> (need it for 7.4).
>
> With driver version pg73jdbc the following works fine:
>
> int rowCount = -1;
> long insertedOid = -1;
> Connection con = null;
> Statement stmt = null;
>
> con = sec.getConnection(); // gets pooled connection
> stmt = con.createStatement();
> con.setAutoCommit(false);
> rowCount = stmt.executeUpdate(query);
> con.commit();
> insertedOid = ((org.postgresql.PGStatement)stmt).getLastOID();
> con.setAutoCommit(true);
> con.close();
>
> With devel version I get ClassCastException on row with insertedOid =
> ((org.postgresql.PGStatement)stmt).getLastOID();
>
> Version 73 drivers were tested against our production server which is
> running postgres 7.3 and devel drivers were tested against my own own
> machine running 7.4 on Cygwin.
>
> I hope you can help me with this.
>
> B Rgds,
> JariP
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

At 01:25 2.12.2003, Oliver Jowett wrote:
>Your comment above implies you're using a connection pool; is it possible
>that you're actually getting a wrapper Connection/Statement implemented by
>the pool infrastructure, rather than the Connection/Statement objects handed
>out by the driver directly? What is the concrete class of 'stmt' reported in
>the ClassCastException?

Yes, we have implemented connection pool in Tomcat.

from server.xml:

<Resource name="jdbc/reltest" scope="Shareable"
type="org.postgresql.jdbc3.Jdbc3PoolingDataSource" auth="Container"/>
<ResourceParams name="jdbc/reltest">
     <parameter><name>factory</name><value>org.postgresql.jdbc3.Jdbc3ObjectFactory</value></parameter>
     <parameter><name>initialConnections</name><value>1</value></parameter>
     <parameter><name>maxConnections</name><value>1000</value></parameter>
     <parameter><name>dataSourceName</name><value>reltest</value></parameter>
     <parameter><name>serverName</name><value>localhost</value></parameter>
...etc

and only thing I change when switching drivers is the serverName parameter
between localhost and our prod server. Above configuration hasn't changed
in a loooong time.

Datasource handling:

protected org.postgresql.jdbc3.Jdbc3PoolingDataSource source = null;

void init() {
     Context initCtx = null;
     Context envCtx = null;
     initCtx = new InitialContext();
     envCtx = (Context) initCtx.lookup("java:comp/env");
     source = (org.postgresql.jdbc3.Jdbc3PoolingDataSource)
envCtx.lookup("jdbc/reltest");
}

Conenction getCon() {
     Connection con = source.getConnection();
     con.setAutoCommit(true);
     return con;
}

And that connection I have used in the code snippet I posted previously.
These also haven't changed in a long time.

I don't get much out of the exception except stack trace:

Entity.executeUpdate: java.lang.ClassCastException
java.lang.ClassCastException
         at com.like.entity.Entity.executeUpdate(Entity.java:180)
         at com.like.entity.Document.save(Document.java:686)
         at
org.apache.jsp.DocumentEditView$jsp._jspService(DocumentEditView$jsp.java:181)
...

>Alternatively it may be a classloading difference between the two machines
>-- the cast will fail if your code and the JDBC code use versions of
>org.postgresql.PGStatement loaded by different classloaders.

ALL code is running locally in my environment in single JVM. Only thing
changed between tests is pgsql server address and jdbc-driver.

Although, I've had problems with tomcat before with connection pooling, so
that's not too far fetched except that this installation indeed is
completely running on my machine.

>Either way, it sounds like the two environments differ by more than just
>driver versions. Try the 7.3 drivers you use in production on your Cygwin
>box (against a 7.3 server if necessary) and see if you get the same failure.

Yes, that is what I have to do just to rule any discrepansies in that out.
I'm not expecting much of a result since this problem originates from
tomcat/netbeans/java/jdbc in some way.

Most likely my configuration is foobared in a way it works with 7.3 but not
anymore with newer drivers... ;) Nevertheless, this is something I have to
get solved...

Thanks for your suggestions!

Hopefully someone can help me track this down.

B Rgds,
JariP

At 01:37 2.12.2003, Dave Cramer wrote:
>Have to agree with Oliver, this is some weird classpath issue, ie
>building with one library, running with another?

That's what I suspected at first also.

That is why I removed all instances of any jdbc drivers between tests and
downloaded fresh into webapp/web-inf/lib directory.

Must be something related to the way we've implemented connection pooling
(see my reply to Oliver) or indeed a bug with jdbc. (I'm not expecting a
bug, but it is a possibility among others)

B Rgds,
JariP

On Tue, Dec 02, 2003 at 02:23:21AM +0200, ListMan wrote:

> I don't get much out of the exception except stack trace:
>
> Entity.executeUpdate: java.lang.ClassCastException
> java.lang.ClassCastException
>         at com.like.entity.Entity.executeUpdate(Entity.java:180)
>         at com.like.entity.Document.save(Document.java:686)
>         at
> org.apache.jsp.DocumentEditView$jsp._jspService(DocumentEditView$jsp.java:181)
> ...

Can you print stmt.getClass() just before the cast then? (usually you'll get
the concrete class name in the exception, not sure why it's not there in
this case).

> >Alternatively it may be a classloading difference between the two machines
> >-- the cast will fail if your code and the JDBC code use versions of
> >org.postgresql.PGStatement loaded by different classloaders.
>
> ALL code is running locally in my environment in single JVM. Only thing
> changed between tests is pgsql server address and jdbc-driver.

Doesn't matter how many JVMs are involved, you can get classloader-related
casting problems in a single JVM as soon as you have >1 classloader
involved. The safest thing to do is make sure that there are no copies of
PGStatement.class anywhere outside the postgresql driver jar (I'm guessing
that Tomcat includes the driver in a parent classloader of the app? If not
you may need to shift that class up to a common parent of the driver and
app..)

-O

At 02:38 2.12.2003, you wrote:
>Can you print stmt.getClass() just before the cast then? (usually you'll get
>the concrete class name in the exception, not sure why it's not there in
>this case).

Well, "System.out.println("stmt.class: " + stmt.getClass());" just before
the cast produced:
stmt.class: class $Proxy1

>Doesn't matter how many JVMs are involved, you can get classloader-related
>casting problems in a single JVM as soon as you have >1 classloader
>involved. The safest thing to do is make sure that there are no copies of
>PGStatement.class anywhere outside the postgresql driver jar (I'm guessing
>that Tomcat includes the driver in a parent classloader of the app? If not
>you may need to shift that class up to a common parent of the driver and
>app..)

Shouldn't be possible as jdbc drivers are loaded with the web application.
ONLY instance of the .jar file is in web applications lib directory and
just to make sure I did do a full search for jdbc drivers and all files
named PGStatement.class. Didn't find any besides the jar file in the lib
directory. Tomcat does not include any drivers for postgres. (just to make
sure I even looked inside some of the jar packages they have some db
related stuff in)

B Rgds,
JariP

On Tue, Dec 02, 2003 at 03:16:41AM +0200, ListMan wrote:
> At 02:38 2.12.2003, you wrote:
> >Can you print stmt.getClass() just before the cast then? (usually you'll
> >get
> >the concrete class name in the exception, not sure why it's not there in
> >this case).
>
> Well, "System.out.println("stmt.class: " + stmt.getClass());" just before
> the cast produced:
> stmt.class: class $Proxy1

Ok, it's connection pooling at fault then -- '$Proxy1' will be a
reflection-based dynamic proxy class that wraps the actual Statement object
returned by the driver.

i.e. the reason you can't cast the statement to PGStatement is that it
really isn't a PGStatement, but a java.lang.reflect.Proxy instance generated
by the pooling code that probably only implements java.sql.Statement.

Unfortunately I can't see a simple way of avoiding this problem short of
turning off connection pooling or modifying the pooling code itself (the
latter could probably be done generically by making the proxy implement all
interfaces present on the underlying object).

I don't know why it works on your production system though; is there any
difference in JVM or Tomcat version on the two systems that might affect how
the pooling code behaves?

-O

At 03:31 2.12.2003, Oliver Jowett wrote:
>Unfortunately I can't see a simple way of avoiding this problem short of
>turning off connection pooling or modifying the pooling code itself (the
>latter could probably be done generically by making the proxy implement all
>interfaces present on the underlying object).

Only thing strange with this is that this works perfectly with older
drivers. I would perfectly understand this if this wouldn't work at all and
would just conclude that pg extensions can't be used with tomcat under
connection pooling... ;)

>I don't know why it works on your production system though; is there any
>difference in JVM or Tomcat version on the two systems that might affect how
>the pooling code behaves?

No. Running all instances on testing purposes on
NetBeans/tomcat4.0.6/JDK1.4_02 and on the _same computer_.

Just to make sure, I just ran on standalone Tomcat 4.1.29 (same JVM) with
different method of initializing the connection pool. Exactly same results.

pg73jdbc.jar works against pgsql73 and devpgjdbc3.jar against pgsql74 does
not, on exactly same platform, code and software. (only difference still is
that pgsql itself is running on 2 different machines with different os'es
but both have been confirmed to be working ok on their own)

B Rgds,
JariP

At 04:31 2.12.2003, Oliver Jowett wrote:
>1) Can you verify that the problematic bit of code is actually getting run
>when you test with the 7.3 drivers? Maybe they're causing different
>application behaviour for some reason, so the cast is never reached.
>
>2) What's the concrete class (via that System.println) of the Statement
>object when you use the 7.3 drivers?

Yes, on both points:

stmt.class: class org.postgresql.jdbc3.Jdbc3Statement
insertedoidbefore: -1
insertedoidafter: 424152

Only thing I changed was remove devel driver and copy 73 driver in place.

B Rgds,
JariP

At 06:22 2.12.2003, Oliver Jowett wrote:
>If you do discover what triggers this behaviour, let me know & maybe we can
>find a workaround.

Well, I asked a former colleague who has some experience fixing early
pgsql7.3 jdbc problems. He suggested to check if there is a proxy
implementation introduced for statements.

I found that the following patch seems to fix the ClassCastException.

The reason this didn't exhibit itself with 7.3 drivers is because proxy
instances were not implemented (or so I was told, didn't check the 7.3
sources myself ;) for statements, only for connections (and they had
similar problems in the beginning).

I briefly tested my changes and it fixed the problem we had. Didn't raise
any immediate new problems either. (my test did only test createStatement case)

Maybe you might like to review the patch and possibly commit the results to
CVS?

B Rgds,
Jari Paljakka

diff -c -r1.7 PooledConnectionImpl.java
*** org/postgresql/jdbc2/optional/PooledConnectionImpl.java     25 Mar 2003
02:46:23 -0000      1.7
--- org/postgresql/jdbc2/optional/PooledConnectionImpl.java     3 Dec 2003
14:44:22 -0000
***************
*** 266,282 ****
               else if(method.getName().equals("createStatement"))
               {
                   Statement st = (Statement)method.invoke(con, args);
!                 return
Proxy.newProxyInstance(getClass().getClassLoader(), new
Class[]{Statement.class}, new StatementHandler(this, st));
               }
               else if(method.getName().equals("prepareCall"))
               {
                   Statement st = (Statement)method.invoke(con, args);
!                 return
Proxy.newProxyInstance(getClass().getClassLoader(), new
Class[]{CallableStatement.class}, new StatementHandler(this, st));
               }
               else if(method.getName().equals("prepareStatement"))
               {
                   Statement st = (Statement)method.invoke(con, args);
!                 return
Proxy.newProxyInstance(getClass().getClassLoader(), new
Class[]{PreparedStatement.class}, new StatementHandler(this, st));
               }
                         else
                         {
--- 266,282 ----
               else if(method.getName().equals("createStatement"))
               {
                   Statement st = (Statement)method.invoke(con, args);
!                 return
Proxy.newProxyInstance(getClass().getClassLoader(), new
Class[]{Statement.class, org.postgresql.PGStatement.class}, new
StatementHandler(this, st));
               }
               else if(method.getName().equals("prepareCall"))
               {
                   Statement st = (Statement)method.invoke(con, args);
!                 return
Proxy.newProxyInstance(getClass().getClassLoader(), new
Class[]{CallableStatement.class, org.postgresql.PGStatement.class}, new
StatementHandler(this, st));
               }
               else if(method.getName().equals("prepareStatement"))
               {
                   Statement st = (Statement)method.invoke(con, args);
!                 return
Proxy.newProxyInstance(getClass().getClassLoader(), new
Class[]{PreparedStatement.class, org.postgresql.PGStatement.class}, new
StatementHandler(this, st));
               }
                         else
                         {