"Prepared Statement 'X' Already Exists" in the PostgreSQL Extractor Log
Problem
Errors such as those below appear in the PostgreSQL Extractor log.
### Error querying database. Cause: org.postgresql.util.PSQLException:
ERROR: prepared statement "S_2" already exists
### Error querying database. Cause: org.postgresql.util.PSQLException:
ERROR: bind message supplies 2 parameters, but prepared statement "S_4" requires 0
More Details
PgBouncer is a PostgreSQL connection pooler which maintains a pool of connections that database transactions share. There are three methods for pooling (https://www.pgbouncer.org/usage.html).
-
Session pooling: The most polite method. When a client connects, one server connection is assigned to it, lasting the whole time the client is connected. When the client disconnects, the server connection drops back into the pool. This is the default method.
-
Transaction pooling: A server connection is assigned to the client only during a transaction. When PgBouncer notices that the transaction is over, the server connection drops back into the pool.
-
Statement pooling: The most aggressive method. The server connection drops back into the pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break.
When transaction pooling is enabled, it prevents IBM Manta Data Lineage from using named prepared statements. There is no issue when session or statement pooling are enabled.
Complete exceptions in the logs look as follows.
2023-06-06 06:08:57.932 [pool-2-thread-2] 0 ERROR eu.profinit.manta.connector.postgresql.extractor.PostgresqlExtractorImpl [Context: TABLE gp_prd.wc_mktg_dst.[OID 49818262]]
EXTRACTION_ERRORS FAILED_TO_EXTRACT_OID
User message: Failed to extract TABLE with OID 49818262 in "gp_prd"."wc_mktg_dst".
Technical message: Failed to extract TABLE with OID 49818262 in "gp_prd"."wc_mktg_dst".
Solution: Please contact MANTA Support at portal.getmanta.com and submit a support bundle/log export.
Impact: SINGLE_INPUT
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: prepared statement "S_2" already exists
### The error may exist in URL [jar:file:/opt/mantaflow/cli/scenarios/manta-dataflow-cli/lib/manta-connector-postgresql-dictionary-extractor-37.1.0.jar!/eu/profinit/manta/connector/postgresql/extractor/mappers/postgresql/TableMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: with attr_metadata as ( select pc.oid as pc_oid, pc.relname as pc_relname, pc.relkind as pc_relkind, pa.attname as pa_attname, pa.attnotnull as pa_attnotnull, pa.atttypid as pa_atttypid, pa.atttypmod as pa_atttypmod, pa.attnum as pa_attnum from pg_class pc left join ( select pa.attname, pa.attnotnull, pa.atttypid, pa.atttypmod, pa.attnum from pg_attribute pa where pa.attrelid = ? and pa.attnum > 0 and pa.attisdropped is not true ) as pa on true where pc.relkind = 'r' and pc.oid = ? ) ,typ_metadata as ( select am.*, ( select typname from pg_type where oid = am.pa_atttypid) as t_typname, ( select typnamespace from pg_type where oid = am.pa_atttypid) as t_typnamespace, ( select typbasetype from pg_type where oid = am.pa_atttypid) as t_typbasetype, ( select typtype from pg_type where oid = am.pa_atttypid) as t_typtype, ( select typtypmod from pg_type where oid = am.pa_atttypid) as t_typtypmod, ( select adbin from pg_attrdef where adrelid = am.pc_oid and adnum = am.pa_attnum) as ad_adbin from attr_metadata am ) select m.pc_oid as oid, m.pc_relname as table_name, m.pa_attname as column_name, ( select nspname from pg_namespace where oid = m.t_typnamespace) as coltypeschema, m.t_typname as coltype, m.pa_attnotnull as not_null, null as external_location, pg_get_expr(m.ad_adbin , m.pc_oid) as column_default, information_schema._pg_char_max_length(CASE WHEN m.t_typtype = 'd' THEN m.t_typbasetype ELSE m.pa_atttypid END, CASE WHEN m.t_typtype = 'd' THEN m.t_typtypmod ELSE m.pa_atttypmod END) as character_maximum_length, information_schema._pg_numeric_precision(CASE WHEN m.t_typtype = 'd' THEN m.t_typbasetype ELSE m.pa_atttypid END, CASE WHEN m.t_typtype = 'd' THEN m.t_typtypmod ELSE m.pa_atttypmod END) as numeric_precision, information_schema._pg_numeric_scale(CASE WHEN m.t_typtype = 'd' then m.t_typbasetype ELSE m.pa_atttypid END, CASE WHEN m.t_typtype = 'd' THEN m.t_typtypmod ELSE m.pa_atttypmod END) as numeric_scale from typ_metadata m order by m.pa_attnum;
### Cause: org.postgresql.util.PSQLException: ERROR: prepared statement "S_2" already exists
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: prepared statement "S_2" already exists
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) ~[?:?]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[?:?]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[?:?]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[?:?]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[?:?]
at com.sun.proxy.$Proxy44.selectOne(Unknown Source) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166) ~[?:?]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87) ~[?:?]
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152) ~[?:?]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85) ~[?:?]
at com.sun.proxy.$Proxy47.getTable(Unknown Source) ~[?:?]
at eu.profinit.manta.connector.postgresql.extractor.dao.PostgresqlDaoImpl.getTable(PostgresqlDaoImpl.java:171) ~[?:?]
at eu.profinit.manta.connector.postgresql.extractor.PostgresqlParallelExtractorImpl$DdlLoader.load(PostgresqlParallelExtractorImpl.java:335) ~[?:?]
at eu.profinit.manta.connector.postgresql.extractor.PostgresqlParallelExtractorImpl$DdlLoader.lambda$getLoadingCallable$8(PostgresqlParallelExtractorImpl.java:482) ~[?:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: prepared statement "S_2" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[?:?]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[?:?]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[?:?]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[?:?]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[?:?]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166) ~[?:?]
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155) ~[?:?]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[?:?]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[?:?]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[?:?]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[?:?]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[?:?]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[?:?]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[?:?]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[?:?]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89) ~[?:?]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[?:?]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[?:?]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76) ~[?:?]
at jdk.internal.reflect.GeneratedMethodAccessor53.invoke(Unknown Source) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[?:?]
... 13 more
Or
2023-06-06 06:08:58.115 [pool-2-thread-1] 0 ERROR eu.profinit.manta.connector.postgresql.extractor.PostgresqlExtractorImpl [Context: TABLE gp_prd.wc_jzj_core.[OID 54231543]]
EXTRACTION_ERRORS FAILED_TO_EXTRACT_OID
User message: Failed to extract TABLE with OID 54231543 in "gp_prd"."wc_jzj_core".
Technical message: Failed to extract TABLE with OID 54231543 in "gp_prd"."wc_jzj_core".
Solution: Please contact MANTA Support at portal.getmanta.com and submit a support bundle/log export.
Impact: SINGLE_INPUT
org.springframework.dao.DataAccessResourceFailureException:
### Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: bind message supplies 2 parameters, but prepared statement "S_4" requires 0
### The error may exist in URL [jar:file:/opt/mantaflow/cli/scenarios/manta-dataflow-cli/lib/manta-connector-postgresql-dictionary-extractor-37.1.0.jar!/eu/profinit/manta/connector/postgresql/extractor/mappers/postgresql/TableMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: with attr_metadata as ( select pc.oid as pc_oid, pc.relname as pc_relname, pc.relkind as pc_relkind, pa.attname as pa_attname, pa.attnotnull as pa_attnotnull, pa.atttypid as pa_atttypid, pa.atttypmod as pa_atttypmod, pa.attnum as pa_attnum from pg_class pc left join ( select pa.attname, pa.attnotnull, pa.atttypid, pa.atttypmod, pa.attnum from pg_attribute pa where pa.attrelid = ? and pa.attnum > 0 and pa.attisdropped is not true ) as pa on true where pc.relkind = 'r' and pc.oid = ? ) ,typ_metadata as ( select am.*, ( select typname from pg_type where oid = am.pa_atttypid) as t_typname, ( select typnamespace from pg_type where oid = am.pa_atttypid) as t_typnamespace, ( select typbasetype from pg_type where oid = am.pa_atttypid) as t_typbasetype, ( select typtype from pg_type where oid = am.pa_atttypid) as t_typtype, ( select typtypmod from pg_type where oid = am.pa_atttypid) as t_typtypmod, ( select adbin from pg_attrdef where adrelid = am.pc_oid and adnum = am.pa_attnum) as ad_adbin from attr_metadata am ) select m.pc_oid as oid, m.pc_relname as table_name, m.pa_attname as column_name, ( select nspname from pg_namespace where oid = m.t_typnamespace) as coltypeschema, m.t_typname as coltype, m.pa_attnotnull as not_null, null as external_location, pg_get_expr(m.ad_adbin , m.pc_oid) as column_default, information_schema._pg_char_max_length(CASE WHEN m.t_typtype = 'd' THEN m.t_typbasetype ELSE m.pa_atttypid END, CASE WHEN m.t_typtype = 'd' THEN m.t_typtypmod ELSE m.pa_atttypmod END) as character_maximum_length, information_schema._pg_numeric_precision(CASE WHEN m.t_typtype = 'd' THEN m.t_typbasetype ELSE m.pa_atttypid END, CASE WHEN m.t_typtype = 'd' THEN m.t_typtypmod ELSE m.pa_atttypmod END) as numeric_precision, information_schema._pg_numeric_scale(CASE WHEN m.t_typtype = 'd' then m.t_typbasetype ELSE m.pa_atttypid END, CASE WHEN m.t_typtype = 'd' THEN m.t_typtypmod ELSE m.pa_atttypmod END) as numeric_scale from typ_metadata m order by m.pa_attnum;
### Cause: org.postgresql.util.PSQLException: ERROR: bind message supplies 2 parameters, but prepared statement "S_4" requires 0
; ERROR: bind message supplies 2 parameters, but prepared statement "S_4" requires 0; nested exception is org.postgresql.util.PSQLException: ERROR: bind message supplies 2 parameters, but prepared statement "S_4" requires 0
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:107) ~[?:?]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[?:?]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[?:?]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[?:?]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446) ~[?:?]
at com.sun.proxy.$Proxy44.selectOne(Unknown Source) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166) ~[?:?]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87) ~[?:?]
at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152) ~[?:?]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85) ~[?:?]
at com.sun.proxy.$Proxy47.getTable(Unknown Source) ~[?:?]
at eu.profinit.manta.connector.postgresql.extractor.dao.PostgresqlDaoImpl.getTable(PostgresqlDaoImpl.java:171) ~[?:?]
at eu.profinit.manta.connector.postgresql.extractor.PostgresqlParallelExtractorImpl$DdlLoader.load(PostgresqlParallelExtractorImpl.java:335) ~[?:?]
at eu.profinit.manta.connector.postgresql.extractor.PostgresqlParallelExtractorImpl$DdlLoader.lambda$getLoadingCallable$8(PostgresqlParallelExtractorImpl.java:482) ~[?:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: bind message supplies 2 parameters, but prepared statement "S_4" requires 0
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[?:?]
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[?:?]
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[?:?]
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[?:?]
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408) ~[?:?]
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166) ~[?:?]
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155) ~[?:?]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[?:?]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94) ~[?:?]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[?:?]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[?:?]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[?:?]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[?:?]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[?:?]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[?:?]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89) ~[?:?]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[?:?]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[?:?]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:76) ~[?:?]
at jdk.internal.reflect.GeneratedMethodAccessor53.invoke(Unknown Source) ~[?:?]
at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433) ~[?:?]
... 13 more
Solution
To resolve this, add ?prepareThreshold=0
(if this is a first parameter) or ;prepareThreshold=0
(if you already have some parameters there) to the end of the PostgreSQL JDBC URL connection string. See
PostgreSQL Resource Configuration for more details.