Escape processing

[bbobcik]

The standard, section 13.4, defines JDBC escape syntax, such as {fn ...}. Processing of such sequences can be disabled for regular java.sql.Statement via setEscapeProcessing() method, however there is no way to disable this for java.sql.PreparedStatement.

This is consistent with the text of the standard, "... setEscapeProcessing method does not work for a PreparedStatement object because its SQL string may have been precompiled when the PreparedStatement object was created."
However, the JavaDoc of the setEscapeProcessing() method mentions this: "The Connection and DataSource property escapeProcessing may be used to change the default escape processing behavior." Yet, after exploring the driver's source code, I am quite certain that no such property is currently supported.

My questions are:

• Is there any way (by means of public, documented driver API) how to create an instance of PreparedStatement without escape syntax parsing?
• Would it be useful to add such DataSource/Connection-level property, even though it is not defined in the JDBC 4.3 Specification? (I have already implemented the change, more or less ready for pull request, but I thought I will explore whether it is the right way to proceed.)

Let me add a bit of rationale: a weird service that I am supporting synthesizes multi-megabyte SQL queries. When the escape processing is enabled, regardless of the fact that there are no escape sequences, the driver allocates extra buffer and practically copies the original query there, leading to 2x memory consumption and suboptimal performance.

[davecramer]

I've read the code and I don't think it matters if it's a PreparedStatement or not, we will honour setEscapeProcessing.
As for the datasource property, we probably should add one if it is documented.

[bbobcik]

As far as I can see, the call to Connection.prepareStatement(sql) leads to the following sequence of actions:

• a constructor of PgPreparedStatement is invoked;
• the constructor calls connection.borrowQuery(sql), which is forwarded to QueryExecutorBase.borrowQuery(sql) first, then to LruCache.borrow(sql);
• here (assuming this is the first occurrence of sql) we call createAction.create(sql), which leads us to CachedQueryCreateAction.create(sql);
• and here, at

  pgjdbc/pgjdbc/src/main/java/org/postgresql/core/CachedQueryCreateAction.java

  Line 43 in 3301e07

  Parser.replaceProcessing(parsedSql, true, queryExecutor.getStandardConformingStrings());

  , the escape parsing is done unconditionally, because the key argument is String.

All this happens before an instance of PreparedStatement is created, therefore there is no way to bypass escape processing.

Perhaps I am missing some alternative way how to obtain an instance of PreparedStatement?

[vlsi]

We could skip escape processing for SQLs that miss { chars. Of course, it might be more efficient to just skip escape processing when you know the application does not use it, however, it might indeed be more efficient to check for { presence first.

We might integrate replaceProcessing into Parser.parseJdbcSql as well, so it does both "analyzing for ? bind parameters`" and "jdbc escape processing"

[davecramer]

it appears that setEscapeParsing on the connection is a thing. Seems trivial to me ?

[vlsi]

JDBC specifies Statement#setEscapeProcessing, so we should perfer Connection#setEscapeProcessing rather than setEscapeParsing.

Anyway, a connection-level setting sounds reasonable, and it should be not that hard to implement.

It should probably invalidate the cache if the setting changes, or we should include it to the cache key.

[davecramer]

I would say that if someone sets it on a statement then it over-rides the connection setting.

good point about the cache.

[davecramer]

interesting, I will have a look

[bbobcik]

Thank you

[davecramer]

So looking at the Connection and DataSource docs there is no mention of any property for escapeProcessing. However it does appear that IBM agrees with you https://www.ibm.com/docs/en/i/7.4?topic=connections-jdbc-driver-connection-properties#conprop__doescape. I'll have a crack at adding it.

[bbobcik]

Yes, as I wrote earlier, this "hidden" property is mentioned only once, in https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/Statement.html#setEscapeProcessing(boolean)