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.