pipelining in v0.1.0

[davepacheco]

We noticed that v0.1.0 supports pipelining of "independent queries" -- thanks for that! I have some questions about the intended use case and guarantees. We'd really like to be able to issue a whole transaction, like this:

query 1: BEGIN;
query 2: INSERT INTO ... RETURNING ...;
query 3: UPDATE ...; (SQL does not depend on data returned from from query 2)
query 4: COMMIT;

My understanding is that this should work, provided that:

• the implementation guarantees that all queries will be issued on the same connection (which I assume it does because the caller chooses which AsyncPgConnection object to issue them on)
• the implementation guarantees that the queries will be issued in a defined order (does it? is the order they're created? or the order the futures are polled?)
• the caller is presumably responsible for exiting the transaction if the Rust code decides to unwind partway through this

The docs talk about pipelining being used for "independent queries". I assume what it means is that "even if query B is issued after query A, the SQL text of query B must not depend on the data returned by query A". Query B's behavior might still depend on the results of query A. Suppose query A is INSERT INTO Foo (a) VALUES (1) and query B is "SELECT * from Foo`. These queries are not independent in terms of their behavior, but the SQL for query B does not depend on the data from query A, and I think that's all this interface requires. Is that right?

[weiznich]

Using pipelining to optimize transactions is one of the use cases for which this feature is designed. "independent queries" does refer here mostly to not allow the following constructs:

let id = users::table.filter(users::name.eq("John")).select(users::id).get_result(conn).await?;
diesel::update(posts::table.filter(users::id.eq(id))).set(posts::title.eq("John's posts").execute(conn).await?;

This is obviously not possible as the second query depends on inputs returned by the first query. Any query combination that does not need to use the results from previous queries can theoretically use pipelining.

• the implementation guarantees that all queries will be issued on the same connection (which I assume it does because the caller chooses which AsyncPgConnection object to issue them on)

That's up to the user. Pipelining works if you use the same connection. If you use different connections it cannot use pipelining.

• the implementation guarantees that the queries will be issued in a defined order (does it? is the order they're created? or the order the futures are polled?)

Again that's up to the user. Queries are issued in the order their futures are polled. Before that the connection does not even know that a specific query exists. If queries are truly independent (== one query do not modify data that are read by an other query) the order does not really matter, otherwise you need to be a bit careful how you create the combined future. Use something like futures::stream::FuturesOrdered to ensure the poll order.

• the caller is presumably responsible for exiting the transaction if the Rust code decides to unwind partway through this

The transaction function does not handle unwinding or canceling the future. If that happens it results in both cases in having a connection with an open transaction. If you use a pool and the connection is returned to the pool, the pooling implementation will take of this, mark the connection as broken and close it afterwards. If you don't use a pool and reuse the same connection later, that's something you need to handle later.

Suppose query A is INSERT INTO Foo (a) VALUES (1) and query B is "SELECT * from Foo`. These queries are not independent in terms of their behavior, but the SQL for query B does not depend on the data from query A, and I think that's all this interface requires. Is that right?

Technically that would be fine, but can lead to different results depending on which query future is polled first.

For more details see postgresql's documentation on this topic

[davepacheco]

Great! All of that makes sense and matches what I'd expect (or at least what I'd hoped!).

I might suggest defining "independent" in the documentation. I'd also suggest documenting that the order in which queries are executed is the order in which their futures are polled.

Thanks!

[weiznich]

Will try to add these notes with the next update 👍