Question: Why prepare statement requires extra round-trip?

[manswami23]

Hi all,

I'm a newbie to rust coming from a java background. I've been doing some comparisons between tokio-postgres and the postgres jdbc driver and was curious about some of the differences I saw.

One difference was that it seemed the postges Parse message was requiring an extra round trip whereas in jdbc, the parse/bind/execute messages are inline. Here's an example captured packet for the latter:

I'm curious, what are the differences between the rust and jdbc driver implementations where the latter eliminates the extra round-trip. So far, the only thing I can tell is the rust implementation looks at the prepared statement output during the Bind phase. e.g. when making sure the caller passes the expected number of inputs:

rust-postgres/tokio-postgres/src/query.rs

Line 234 in 1e1f6bf

if params.len() != statement.params().len() {

. On the other hand, I think jdbc calculates the expected number of inputs by parsing the sql on the client-side. Are there any other reasons or examples of dependencies on the Statement result that I'm missing? I would appreciate any insight into this.

Thanks!

[sfackler]

So far, the only thing I can tell is the rust implementation looks at the prepared statement output during the Bind phase. e.g. when making sure the caller passes the expected number of inputs:

Yep, that's the reason. You can use query_typed to bypass this if you need to, but if you're that concerned about perf you should probably be preparing the statements up front and reusing them across requests.

[manswami23]

Thanks @sfackler .

Regarding the query_typed API, is it safe for pipelining and does it guarantee order of execution? Asking because I believe it uses unnamed statements, and I'm not sure how that interacts in a pipeline. I also came across #930 which is a couple years older, but it seemed the consensus was that order of execution was only guaranteed if the statement was prepared up front. Does that still apply for query_typed?

[sfackler]

query_typed does use the unnamed statement but only locally to a single command batch so it should be safe in the context of pipelined requests. The discussion in #930 was before #1147.

[manswami23]

thanks @sfackler

What would be the best way to achieve jdbc's batch behavior (multiple prepared statements with separate bindings but all in one round trip).

• For my use case, the sql is dynamically generated for each request, so the ability to reuse named statements is somewhat limited. As a result, I'm leaning towards using unnamed statements.
• Also, the whole batch of sqls is expected to be processed in a single transaction. And so preferably, when the first statement fails, none of the other statements in the transaction execute and the whole thing is rolled back.

At the moment, I've got something like this

let (first_stream, second_stream) = future::try_join4(
    client.query_typed(
            "BEGIN",
            &vec![],
    ),
    client.query_typed_raw(
        "UPDATE table set name = $1",
        table_params_update,
    ),
    client.query_typed_raw(
        "UPDATE other_table set othername = $1",
        other_params_update,
    ),
   client.query_typed(
            "COMMIT",
            &vec![],
    )
).await?;

The only gap I'm seeing is that if the UPDATE table set name = $1 fails, the subsequent update isn't discarded as it is in Jdbc. Instead, it still runs and I get a ERROR: current transaction is aborted, commands ignored until end of transaction block in the query log. I suspect the difference in behavior comes from the Sync message at the end of each query, because after the Sync, the backend will try and fail to process the next query.

Is there any mechanism by which I could force statements after a failure to be discarded? Is the only way for pipelined queries to be followed by a single Sync message instead of a Sync per statement?