Re: PostgreSQL/Tomcat JNDI Datasource Questions

This is my config and works well. Look
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html
for details.

/David
<Resource name="jdbc/webCatalog" auth="Container"
type="javax.sql.DataSource"/>

<ResourceParams name="jdbc/webCatalog">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>org.postgresql.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:postgresql://toto.tata.com:5432/webCatalog</value>
</parameter>
<parameter>
<name>username</name> <!--Dont forget to change the realm
username/password to-->
<value>ecologic</value>
</parameter>
<parameter>
<name>password</name>
<value>eco10_</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>25</value>
</parameter>
<!-- Maximum number of idle dB connections to retain in pool.
Set to 0 for no limit.
-->
<parameter>
<name>maxIdle</name>
<value>0</value>
</parameter>
<!-- Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<parameter>
<name>maxWait</name>
<value>-1</value>
</parameter>
<!-- To configure a DBCP DataSource so that abandoned dB
connections are removed and
recycled add the following paramater to the ResourceParams
configuration for your DBCP
DataSource Resource: -->
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<!--Use the removeAbandonedTimeout parameter to set the number of
seconds a dB connection has been idle before it is considered
abandoned. -->
<parameter>
<name>removeAbandonedTimeout</name>
<value>12600</value> <!--Wait 3 hour before statuating a
connection is abandoned-->
</parameter>

<!-- The logAbandoned parameter can be set to true if you want
DBCP to log a stack trace
of the code which abandoned the dB connection resources. -->

<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>

</ResourceParams>

Warren Killian wrote:

> Hello group,
>
> I have several questions concerning configuring and using a JNDI
> DataSource
> with PostgreSQL and Apache Tomcat. Currently I have
> successfully configured a "basic" DataSource. Currently, my
> configuration
> which resides in a context.xml file is as follows:
>
> <Context className="org.apache.catalina.core.StandardContext"
> backgroundProcessorDelay="-1"
> cachingAllowed="true"
>
> charsetMapperClass="org.apache.catalina.util.CharsetMapper"
>
> configFile="/usr/local/jwsdp-1.3/conf/Catalina/localhost/test.xml"
> cookies="true"
> crossContext="false"
> debug="0"
> displayName="test"
> docBase="/usr/local/jwsdp-1.3/webapps/test"
> domain="Catalina"
> engineName="Catalina"
> j2EEApplication="none"
> j2EEServer="none"
> lazy="true"
> managerChecksFrequency="6"
> path="/test"
> privileged="false"
> reloadable="true"
> startupTime="8"
> swallowOutput="false"
> tldScanTime="0"
> useNaming="true"
> wrapperClass="org.apache.catalina.core.StandardWrapper">
>
> <Resource name="jdbc/test" scope="Shareable"
> type="javax.sql.DataSource"/>
> <ResourceParams name="jdbc/test">
> <parameter>
> <name>factory</name>
>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
> <parameter>
> <name>driverClassName</name>
> <value>org.postgresql.Driver</value>
> </parameter>
> <parameter>
> <name>url</name>
> <value>jdbc:postgresql://myIP/test</value>
> </parameter>
> <parameter>
> <name>username</name>
> <value>Whoopie</value>
> </parameter>
> <parameter>
> <name>password</name>
> <value>Doopie</value>
> </parameter>
> <parameter>
> <name>removeAbandoned</name>
> <value>true</value>
> </parameter>
> <parameter>
> <name>removeAbandonedTimeout</name>
> <value>20</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>5000</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>5</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>2</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>SELECT * FROM test</value>
> </parameter>
> </ResourceParams>
> </Context>
>
> Using the above configuration, my JSP pages can connect to PostgreSQL and
> perform all required database operations.
>
> But all is not right! I have the following issues:
>
> 1.) Connections are NEVER closed
> Say I hit my JSP page which perform a query and display the results.
> Using netstat I can see that one connection to PostgreSQL has been
> established. But then, if I uninstall and reinstall my web app and
> hit my
> JSP page again, then the output of "netstat -p -t" shows that a second
> connection to PostgreSQL is established. The first connection never
> goes away! If I continue to
> uninstall/reinstall/hit JSP page, the connections continue to acrue and
> never go away. The number of connections can even exceed the value of
> "maxActive" defined in my context.xml file. What am I doing wrong?
>
> 2.) Idle connections aren't closed
> As mentioned above, the connections which are opened by my web app are
> kept around forever and never closed. In my context.xml file above, I've
> defined the "removeAbandoned" parameter to true and have set the
> "removeAbandonedTimeout" parameter to 20 seconds. But the connections
> are
> never closed. Every time I run "netstat -p -t", the connections are
> still there. :(
>
> 3.) Is my configuration correct for performing Pooling?
> I "can" get "a" database connection using this configuration. But is
> this
> adequate to perform connection pooling? How can I tell if/when
> connection
> pooling is actually being performed correctly?
> Any help would be greatly appreciated.
>