Re: Cast java.sql.Connection to PGConnection

Yes I am investigating that issue...

It is a bit complicated since a few months ago, I had built my own "toy
connection pooler" to serve a seldom used part of my application. At the
time, I didn't want to take the risk of breaking the rest of my application
by making use of a generic Connection Pool (like the Tomcat one), so I
figured out (perhaps wrongly) that this was the safest and easiest way to
bridge between my requirements for a (small) connection pool at the time
and the full blown thing... Not very surprisingly, my "toy connection
pooler" included a class called PooledConnection, that I though last night
might interfere with a similarly named class in the Tomcat Connection Pool,
and thus cause the issues I am having! (You may wonder why I didn't remove
my "toy connection pooler" as the first step of this "implement the real
thing (Tomcat Connection Pool)" project; the reason is that I wanted to do
this critical change in an incremental fashion, making sure that at each
step I had not broken anything. Keeping my "toy connection pooler" in
there, while not using it anymore appeared to me as the safest route since
it guaranteed at least that my project would still compile...)

So this morning, my first attempt at cracking this issue was to include my
"toy connection pool" classes in my "bare metal" test environment (without
making use of it, but just to be sure they were "visible" in my
environment), to check whether my hypothesis was right (I was convinced
this was the issue)... Unfortunately, this didn't change anything: the
"bare metal" app still works just fine!

To confirm again that this was not the case in the full blown app, I
renamed my PooledConnection class to PooledConnection1 and I still have the
issues.

So both of these experiments tend to confirm that my PooledConnection class
is not the cause of the issues I am having...

Next step was to see, as you suggested, that the libraries that I am
including in my app are not in conflict with the ones that are in the
Tomcat lib. Indeed, I have the two libraries I suspect might be the cause
of these issues (tomee-jdbc-1.7.1.jar & postgresql-9.4.1208.jre.jar) in
both places. Obviously, the first one (tomee-jdbc-1.7.1.jar) was in the
Tomcat/lib by default since it is part of the install. I had to put the
second one (postgresql-9.4.1208.jar) in the Tomcat/lib since I had found a
few days ago that if it wasn't there, the Tomcat Connection pool would not
be initialized/loaded... Again, having my "bare metal" app to check this
environment seems to confirm that, since everything works in there and it
does not have any of those two libraries bundled into it... In fact, it
does not have any libraries at all... I just confirmed that the postgres
library is required under the Tomcat/lib; if it is not there, when I try to
instantiate the Tomcat Pool, I get the rather cryptic SQLException:
"java.sql.SQLException: org.postgresql.Driver" and my "bar metal" app does
not even compile - though this is not surprising, that library has to be
somewhere!

So I removed those two libraries from my app... well I tried but the Jasper
report component wants to see the postgresql one in my app library to
compile, so I had to put it back... and it is still broken! You will
understand that my application is a bit more complicated than the few lines
of code I sent you, among other things, it includes about 45 libraries...
So I have to figure out how to try to dig into my issue: either adding more
and more stuff to my "bare metal" app until it breaks, or remove more and
more stuff from my app until it is repaired... I think I will take the
first option, but obviously it will take me time to do...

Thanks again for your help and comments. At least making me build a "bare
metal" app confirmed to me that there is light at the end of the tunnel,
although the tunnel may be longer than what I initially expected...

Once I have succeeded cracking that nut, I will look at my CopyManager
invocation to account for your comments and use the proper constructor...

Thanks again!

Eric

On Wed, May 18, 2016 at 9:28 AM, Vitalii Tymchyshyn <vit(at)tym(dot)im> wrote:

> I'd really suspect a classloading issue here. TS gets connection through
> JNDI and then tries to unwrap/cast. By any chance, have you got two
> postgresql driver jars in different classloaders (e.g. two webapps or
> tomcat lib and webapp or tomcat common lib)?
>
> Best regards, Vitalii Tymchyshyn
>
> Вт, 17 трав. 2016 17:06 користувач David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> пише:
>
>> Except that didn't appear to work ... haven't confirmed the behavior
>> myself.
>>
>> In any case an apparent second report of this just surfaced:
>>
>>
>> http://www.postgresql.org/message-id/CANOVrEhYbmO=mJT==YA=JZ_ZYZHEuq_wra5eYOQuZ+xF1feiHA@mail.gmail.com
>>
>> David J.
>>
>> On Tue, May 3, 2016 at 3:18 AM, Philippe Marschall <
>> Philippe(dot)Marschall(at)netcetera(dot)com> wrote:
>>
>>> Hi
>>>
>>> The following is the way to do it:
>>>
>>> PGConnection pgCon = conn.unwrap(PGConnection.class);
>>>
>>> Cheers
>>> Philippe
>>> ________________________________________
>>> From: pgsql-jdbc-owner(at)postgresql(dot)org <pgsql-jdbc-owner(at)postgresql(dot)org>
>>> on behalf of peter.penzov <peter(dot)penzov(at)gmail(dot)com>
>>> Sent: Monday, May 2, 2016 20:29
>>> To: pgsql-jdbc(at)postgresql(dot)org
>>> Subject: [JDBC] Cast java.sql.Connection to PGConnection
>>>
>>> I want to upload a file in PostgreSQL using Tomcat:
>>>
>>> @Resource(name = "jdbc/DefaultDB")
>>> private DataSource ds;
>>> Connection conn = ds.getConnection();
>>>
>>> I tried to cast the conn object this way:
>>>
>>> PGConnection pgCon = ((org.apache.commons.dbcp.DelegatingConnection)
>>> conn).getInnermostDelegate();
>>>
>>> I get
>>>
>>> Caused by: java.lang.ClassCastException:
>>> org.apache.tomcat.dbcp.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper
>>> cannot be cast to org.postgresql.PGConnection
>>>
>>> I also tried:
>>>
>>> Connection unwrap = conn.unwrap(Connection.class);
>>> connSec = (org.postgresql.PGConnection) unwrap;
>>>
>>> I get
>>>
>>> java.sql.SQLException: Cannot unwrap to org.postgresql.PGConnection
>>>
>>> LargeObjectManager pgCon = pgCon.getLargeObjectAPI();
>>>
>>> What is the proper way to implement the code?
>>>
>>> P.S
>>>
>>> I tried this:
>>>
>>> PGConnection pgConnection = null;
>>>
>>> if (conn.isWrapperFor(PGConnection.class))
>>> {
>>> pgConnection = conn.unwrap(PGConnection.class);
>>> }
>>>
>>> LargeObjectManager lobj = pgConnection.getLargeObjectAPI();
>>>
>>> But I get NPE at this line LargeObjectManager lobj =
>>> pgConnection.getLargeObjectAPI();
>>>
>>>
>>>
>>>
>>> --
>>> View this message in context:
>>> http://postgresql.nabble.com/Cast-java-sql-Connection-to-PGConnection-tp5901531.html
>>> Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>
>>
>>