In my app I am reading and writing to the database using pqxx::work, which resolves to using work = transaction<>.
Taking the read as a case in point, it is a single, albeit relatively complex, SELECT statement query on a single table. My read takes (approx) 240ms. The latency to the server is 70ms. If instead of pqxx::work I use nontransaction, the read takes 100ms. This is consistent with 3 round trips to the server in the work (transaction) case, and only 1 in the nontransaction case.
I see something similar on the write. Here I have two SQL statements, but they don't have to be in a transaction: the first simply reserves some bigint primary keys using nextval(pg_get_serial_sequence(..)), which is used by the second INSERT statement, and I am unconcerned about wasting bigints if there were to be a failure. Using nontransaction instead of transaction for writes is consistent with 2 rather than 4 trips to the server.
My question is whether there is any disadvantage in using pqsl::nontransaction in my case, as my app read and writes run twice as slowly with my current, pqsl::work (transaction) approach.
I did some research, below, and my gut feel is that nontransaction is just like running a single statement in psql where it's going to be in its own transaction. However while I am new to both Postgres and libpqxx, I have done enough to realize that it is a complex area and there are many subtleties.
FWIW, pattern of use is that all records are immutable once written (i.e. vanilla INSERT only; no UPDATE or DELETE); however this may change in the future.
From the libqpxx code / docs:
nontransaction does not maintain any kind transactional integrity
but also, somewhat at odds with this
Any query executed in a nontransaction is committed immediately
On a github issue, the author comments:
AFAICT the "SELECT ... FOR UPDATE" runs outside of any transaction. Which means that that statement runs as a transaction of its own, which commits immediately when the statement completes.
PostgreSQL states in https://www.postgresql.org/docs/current/tutorial-transactions.html:
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it.
So, what about this pqsl::nontransaction? This is just a client-side library; they basically designed a transaction class based on a transaction_base class, and then just created the nontransaction as another class with the same API, to make it easier to use (because they both derive from the same baseclass).
It has no "transaction protection" simply because it does not do a BEGIN -- so every statement that is run through this class will be an individual statement as per the PostgreSQL description above. And while you can call commit or abort on it (remember: same API), these are non-functional.
So, no -- unless you want additional parameters that you might be able to set on an actual transaction, there is no disadvantage. It might even be a tiny tiny tiny bit faster because the client library doesn't need to talk to the PostgreSQL server about transactions, and just lets the server do it automatically. On the other hand, from a software perspective, it might be easier to forget changing a nontransaction into an actual one when another statement is added.