Message-ID: From: "vlsi (@vlsi)" To: "pgjdbc/pgjdbc" Date: Fri, 17 Dec 2021 16:39:06 +0000 Subject: Re: [pgjdbc/pgjdbc] issue #2325: Support pipelining queries In-Reply-To: References: List-Id: X-GitHub-Author-Login: vlsi X-GitHub-Comment-Id: 996861734 X-GitHub-Comment-Type: issue_comment X-GitHub-Issue: 2325 X-GitHub-Repo: pgjdbc/pgjdbc X-GitHub-Type: comment X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/2325#issuecomment-996861734 Content-Type: text/plain; charset=utf-8 We can try inventing a better "prepared statement batching". 1. JDBC spec requires that batch is executed only with `executeBatch` call. In other words, the spec does not allow sending queries earlier. We could do better if we were allowed to send queries as soon as the user calls `addBatch`. Unfortunately, the spec allows for `clearBatch` method, so eager sending should be opt-in (e.g. via special `setBatchMode(forward_only)`). On the other hand, we already have `rewrite batch inserts to insert values`, so we might add an option that would automatically pipeline after each `addBatch`, and it would throw an exception if the user calls `clearBatch` when some of the data has been pipelined. @davecramer , WDYT? 1. JDBC spec requires that the shape of all the batched statements is the same. This effectively blocks batching statements with different SQL. Of course, we might try adding our own API (e.g. something like `Copy` where you add `PreparedStatements`), however, it would be a non-standard API. ---- If we consider pipelining arbitrary SQLs, then it could be like: ``` nameResultSet = con.executeStatement("select name from object where id=1"); // pipelines query descriptionResultSet = con.executeStatement("select description from object where id=1"); // pipelines query nameResultSet.next(); // <-- blocks and waits for the result descriptionResultSet.next(); // <-- the result is likely ready by this time ``` Unfortunately, we can't move exception from `executeStatement` till `nameResultSet.next()` as it would change the semantics. The application might expect that "invalid SQL" would result in SQL exception when you call `executeStatement`, and they would be surprised in case the exception is delayed till the first `resultSet` operation or even till the statement is closed (e.g. in case resultset was not really used). On the other hand, I do not know if moving "exceptions" from `executeStatement` till "the first access of resultset" is really that breaking for the applications. It might be fun to try `autoPipelineAllQueries=true` approach so `statement.executeQuery()` does not really wait for the response from the backend.