preferredTestQuery never use with c3p0 0.9.5.2 and Tomcat 7

Question

I use tomcat7 with c3p0-0.9.5.2 and postgresql-9.3-1102-jdbc41, the preferredTestQuery (select 1) is never use, in product Only.

The same configuration work fine in test env (check with update query).

In product there is a lot of call to the getTable postgresql query. (> 1000cpm - New Relic data).

 <Resource name="jdbc/database_read_only"
          auth="Container"
          type="com.mchange.v2.c3p0.ComboPooledDataSource"
          description="Ma description"
          jdbcUrl="jdbc:postgresql://hostname:5432/mabase"
          driverClass="org.postgresql.Driver"
          user="monuser"
          password="monpassword"
          initialPoolSize="10"
          minPoolSize="10"
          maxPoolSize="100"
          acquireIncrement="10"
          maxIdleTime="300"
          maxConnectionAge="1800"
          connectionTesterClassName="com.mchange.v2.c3p0.impl.DefaultConnectionTester"
          preferredTestQuery="select 2"
          testConnectionOnCheckout="true"
          testConnectionOnCheckin="false"
          idleConnectionTestPeriod="300"
          maxIdleTimeExcessConnections="60"
          unreturnedConnectionTimeout="10"
          factory="org.apache.naming.factory.BeanFactory"/>

The log looks fine :

 2016-12-23 15:26:10,138 : Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 10, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> ddsdsdsqd, debugUnreturnedConnectionStackTraces -> false, description -> ma description, driverClass -> org.postgresql.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> mytoken, idleConnectionTestPeriod -> 300, initialPoolSize -> 10, jdbcUrl -> jdbc:postgresql://hostname:5432/monapp, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 1800, maxIdleTime -> 300, maxIdleTimeExcessConnections -> 60, maxPoolSize -> 100, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 10, numHelperThreads -> 3, preferredTestQuery -> select 1, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]

Why c3p0 use complexe request and not "select 1" ?

sample of complex requete :

SELECT * FROM (SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) AS attnotnull,a.atttypmod,a.attlen,row_number() OVER (PARTITION BY a.attrelid ORDER BY a.attnum) AS attnum, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc,dsc.description,t.typbasetype,t.typtype FROM pg_catalog.pg_namespace

SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   WHEN 'S' THEN 'TEMPORARY SEQUENCE'   WHEN 'v' THEN 'TEMPORARY VIEW'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  WHEN 'f' THEN 'FOREIGN TABLE'  WHEN 'm' THEN 'MATERIALIZED VIEW'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname LIKE 'availability_table' ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME 

I take all the ideas.

thanks

Answer 1

Finally,

I change request : select 1; by a simple "select value from singleRowTable"
=> the request "select value from singleRowTable" is log few time by Newrelic, => the preferredQuery work.

After look in detail the request, and the link stacktrace, She come from org.springframework.jdbc.core.simple.SimpleJdbcInsert.

In fact, the SimpleJdbcInsert was use like prototype for every insert. I change this to instance dao variable and metaData was request one by instance only.

Answer 2

I don't think you are working with what you think you are working with.

c3p0-0.9.5.2 running against Postgres' JDBC 4.1 driver would never the old default table name query. It would use the Connection.isValid(...) method if no preferredTestQuery were set. I suspect you have an older version of c3p0 and/or Postgres JDBC somewhere in your application's CLASSPATH.

Note there is weirdness you should try to chase down in the stuff that you've posted. The preferredTestQuery you actually configure is "select 2", while the preferredTestQuery of the DataSource in your logs is "select 1". Similarly, you configure an unreturnedConnectionTimeout of 10, but in the logged config it is unset (0). You are logging a DataSource similar to, but not the same as, the one that you are configuring.

I suspect that the reason things get weird in production is because in your production environment but not your test environment, you have older libraries (c3p0 and/or postgres drivers) and other DataSources set up for older applications.

Note that c3p0 is very stable about its semantics. It's unlikely to harm your older applications if you upgrade everywhere to 0.9.5.2. (Just be sure to include its dependency mchange-commons-java 0.2.11 or later.)