postgresql libpqxx: Several queries as one transaction

Question

When inserting/updating data in postgresql, it is easy to execute multiple statements in one transaction. (My goal here is to avoid a server round-trip for each statement, although the transactional isolation is often useful.)

When querying, I'm unclear if this is possible. I'd somehow need to know what function is going to consume each bit and how to separate the bits.

connection c("dbname=test user=postgres hostaddr=127.0.0.1");
work w(c);
w.exec("SELECT a, b FROM my_table WHERE c = 3;");
w.exec("SELECT x, y, z FROM my_other_table WHERE c = 'dog';");
w.commit();

Assume I've got functions my_parse_function() and my_other_parse_function() that can read rows from each of these queries, were I doing them separately.

Answer 1

If your goal is to avoid round trips, transactions don't help.

Transaction isolation in Postgres (as with most RDBMSs) doesn't rely on the server executing all of your statements at once. Each statement in your transaction will be sent and executed at the point of the exec() call; isolation is provided by the engine's concurrency control model, allowing multiple clients to issue commands simultaneously, and presenting each with a different view of the database state.

If anything, wrapping a sequence of statements in a transaction will add more communication overhead, as additional round-trips are required to issue the BEGIN and COMMIT commands.

---

If you want to issue several commands in one round-trip, you can do so by calling exec() with a single semicolon-separated multi-statement string. These statements will be implicitly treated as a single transaction, provided that there is no explicit transaction already active, and that the string doesn't include any explicit BEGIN/COMMIT commands.

If you want to send multiple queries, the protocol does allow for multiple result sets to be returned in response to a multi-query string, but exec() doesn't give you access to them; it's just a wrapper for libpq's PQexec(), which discards all but the last result.

Instead, you can use a pipeline, which lets you issue asynchronous queries via insert(), and then retrieve() the results at your leisure (blocking until they arrive, if necessary). Setting a retain() limit will allow the pipeline to accumulate statements, and then send them together as a multi-command string.