2

I have a Postgres 9.6.17 DBMS and a simple Java standalone program (no Spring) that connects to the DBMS and issues a query. Based on Postgres documentation a query can be executed in 'simple mode' or 'extended mode' (where parsing and query rewriting is done in once) but the binding happens repeatedly.

I have a simple table customer(cust_id integer, first_name varchar(100), last_name varchar(100)). The code I am executing is as follows:

Properties props = new Properties(); ....//typical properties such as user, password, database etc props.setProperty("preferQueryMode", "extendedForPrepared"); Connection con = DriverManager.getConnection(url, props); PreparedStatement st = con.prepareStatement("select * from ecommerce.customer where cust_id = ?"); st.setInt(1, 22); ResultSet rs = st.executeQuery();

I was hoping that this would produce an 'extended query mode' protocol use between JDBC client and DBMS backend. But it keeps produced a 'simple query mode' interaction. I verified this by enabling JDBC logging for postgres driver. I also looked at the implementations of Query interface within JDBC driver and all I could find is 3 implementations as follows:

  1. BatchedQuery
  2. CompositeQuery
  3. SimpleQuery

Despite the name CompositeQuery all it does is splits up a query involving multiple statements into a list of SimpleQuery objects.

So my question is what can I do to get the protocol used by my connection to an extended mode?

Improve this question

1 Answer 1

Reset to default
2

By default, the Postgres JDBC driver will only start using server-side statements after a prepared statement is used 5 times. From the JDBC driver property documentation:

prepareThreshold = int

Determine the number of PreparedStatement executions required before switching over to use server side prepared statements. The default is five, meaning start using server side prepared statements on the fifth execution of the same PreparedStatement object. More information on server side prepared statements is available in the section called “Server Prepared Statements”.

But that property can be changed.

This is the program I tested with:

Properties props = new Properties(); props.setProperty("user", "test"); props.setProperty("password", "test"); props.setProperty("loggerLevel", "TRACE"); props.setProperty("prepareThreshold", "1"); try (Connection con = DriverManager.getConnection("jdbc:postgresql:test", props); PreparedStatement stat = con.prepareStatement("select * from app_user where username = ?")) { for (String username : List.of("user1", "user2", "user3")) { stat.setString(1, username); try (ResultSet rs = stat.executeQuery()) { if (rs.next()) System.out.println("User " + username + " has ID: " + rs.getString(1)); } } }

When prepareThreshold is set to 1, the logs show:

Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl execute FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@33065d67, maxRows=0, fetchSize=0, flags=16 Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendParse FINEST: FE=> Parse(stmt=S_1,query="select * from app_user where username = $1",oids={1043}) Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendBind FINEST: FE=> Bind(stmt=S_1,portal=null,$1=<'user1'>) Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal FINEST: FE=> Describe(portal=null) Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute FINEST: FE=> Execute(portal=null,limit=0) ... Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl execute FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@35dab4eb, maxRows=0, fetchSize=0, flags=16 Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendBind FINEST: FE=> Bind(stmt=S_1,portal=null,$1=<'user2'>) Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute FINEST: FE=> Execute(portal=null,limit=0) ... Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl execute FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@2d901eb0, maxRows=0, fetchSize=0, flags=16 Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendBind FINEST: FE=> Bind(stmt=S_1,portal=null,$1=<'user3'>) Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute FINEST: FE=> Execute(portal=null,limit=0)

which show the query parse happens once, then there are repeated binds.

Compare this to when prepareThreshold is not set:

Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl execute FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@33065d67, maxRows=0, fetchSize=0, flags=17 Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendParse FINEST: FE=> Parse(stmt=null,query="select * from app_user where username = $1",oids={1043}) Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendBind FINEST: FE=> Bind(stmt=null,portal=null,$1=<'user1'>) Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal FINEST: FE=> Describe(portal=null) Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute FINEST: FE=> Execute(portal=null,limit=0) ... Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl execute FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@8519cb4, maxRows=0, fetchSize=0, flags=17 Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendParse FINEST: FE=> Parse(stmt=null,query="select * from app_user where username = $1",oids={1043}) Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendBind FINEST: FE=> Bind(stmt=null,portal=null,$1=<'user2'>) Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal FINEST: FE=> Describe(portal=null) Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute FINEST: FE=> Execute(portal=null,limit=0) ... Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl execute FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@35dab4eb, maxRows=0, fetchSize=0, flags=17 Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendParse FINEST: FE=> Parse(stmt=null,query="select * from app_user where username = $1",oids={1043}) Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendBind FINEST: FE=> Bind(stmt=null,portal=null,$1=<'user3'>) Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal FINEST: FE=> Describe(portal=null) Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute FINEST: FE=> Execute(portal=null,limit=0)

where there are 3 separate statement parses and binds. After 5 queries they should start getting reused, however.

Regarding the query mode being 'simple' vs 'extended', it looks like the logs always print out 'simple execute' regardless, however the difference is that true simple mode the parameters will not be bound in prepared statement and instead the entire query will be sent as text. In this example, if the preferQueryMode property is set to simple, the logs show:

Feb 18, 2020 2:06:19 PM org.postgresql.core.v3.QueryExecutorImpl execute FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@33065d67, maxRows=0, fetchSize=0, flags=1,041 Feb 18, 2020 2:06:19 PM org.postgresql.core.v3.QueryExecutorImpl sendSimpleQuery FINEST: FE=> SimpleQuery(query="select * from app_user where username = 'user1'") ... Feb 18, 2020 2:06:20 PM org.postgresql.core.v3.QueryExecutorImpl execute FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@28261e8e, maxRows=0, fetchSize=0, flags=1,041 Feb 18, 2020 2:06:20 PM org.postgresql.core.v3.QueryExecutorImpl sendSimpleQuery FINEST: FE=> SimpleQuery(query="select * from app_user where username = 'user2'") ... Feb 18, 2020 2:06:20 PM org.postgresql.core.v3.QueryExecutorImpl execute FINEST: simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@d737b89, maxRows=0, fetchSize=0, flags=1,041 Feb 18, 2020 2:06:20 PM org.postgresql.core.v3.QueryExecutorImpl sendSimpleQuery FINEST: FE=> SimpleQuery(query="select * from app_user where username = 'user3'")

notice the difference between this and the default - there are no parameters and the 'user1', 'user2' and 'user3' values are being sent inline with each query.

I think what you are really looking for then is to re-use server-side statements immediately and rebind parameters. In this case, setting prepareThreshold to 1 will do the job.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.