libpqxx
The C++ client library for PostgreSQL
|
Classes | |
class | pqxx::robusttransaction< ISOLATION > |
Slightly slower, better-fortified version of transaction. More... | |
class | pqxx::transaction< ISOLATION, READWRITE > |
Standard back-end transaction, templatised on isolation level. More... | |
Functions | |
class | pqxx::__attribute__ ((visibility("default"))) array_parser |
Low-level parser for C++ arrays. | |
All database access goes through instances of these classes. In libpqxx you can't execute SQL directly on the connection object; that all happens only on a transaction object. If you don't actually want to start a transaction on the server, there's a nontransaction class which operates in autocommit, i.e. without a transaction.
(Why do you always need a transaction object? It ended up being the cleaner choice in terms of interface design. It avoids a bunch of API maladies: duplicating API between classes, messy inheritance, inviting mistakes by making the transaction afterthought, and so on.)
Like most other things in libpqxx, transactions follow RAII principles. Creating a transaction object starts the transaction on the backend (if appropriate), and to destroying one ends the transaction. But there's one extra step: if you want to make the transaction's changes permanent, you need to commit it before you destroy it. If you destroy the transaction object without committing, or if you call its abort()
member function, then any transaction type (other than nontransaction) will roll back its changes to the database instead.
There is a choice of transaction types. To start with you'll probably want to use work, represents a regular, vanilla transaction with the default isolation level.
All the actual transaction functionality, including all the functions for executing SQL statements, lives in the abstract transaction_base class. It defines the API for each type of transaction. You create a transaction, you use it by calling transaction_base member functions, and then you either commit or (in the case of failure) abort. If you destroy your transaction object without doing either, it automatically aborts.
Once you're done with your transaction, you can start a new one using the same connection. But there can be only one main transaction going on on a connection at any given time. (You can have more "nested" transactions, but I'm not counting those as "main" transactions here. See below.)
The concrete transaction types, all derived from transaction_base, are:
First and foremost, the plain transaction template. Template parameters let you select isolation level, and whether it should be read-only. Two aliases are usually more convenient: work is a regular, run-of-the-mill default transaction. read_transaction is a read-only transaction that will not let you modify the database.
Then there's nontransaction. This one runs in autocommit, meaning that we don't start any transaction at all. (Technically in this mode each SQL command runs in its own little transaction, hence the term "autocommit." There is no way to "undo" an SQL statement in this kind of transaction.) Autocommit is sometimes a bit faster, and sometimes a bit slower. Mainly you'll use it for specific operations that cannot be done inside a database transaction, such as some kinds of schema changes.
And then ther's robusttransaction to help you deal with those painful situations where you don't know for sure whether a transaction actually succeeded. This can happen if you lose your network connection to the database just while you're trying to commit your transaction, before you receive word about the outcome. You can re-connect and find out, but what if the server is still executing the commit?
You could say that robusttransaction is not more robust, exactly, but it goes to some extra effort to try and figure these situations out and give you clarity. Extra effort does actually mean more things that can go wrong, and it may be a litte slower, so investigate carefully before using this transaction class.
All of the transaction types that actually begin and commit/abort on the database itself are derived from dbtransaction, which can be a useful type if your code needs a reference to such a transaction but doesn't need to enforce a particular one. These types are transaction, work, read_transaction, and robusttransaction.
Finally, there's subtransaction. This one is not at all like the others: it can only exist inside a dbtransaction. (Which includes subtransaction itself: you can nest them freely.) You can only operate on the "innermost" active subtransaction at any given time, until you either commit or abort it. Subtransactions are built on savepoints in the database; these are efficient to a point but do consume some server resources. So use them when they make sense, e.g. to try an SQL statement but continue your main transation if it fails. But don't create them in enormous numbers, or performance may start to suffer.
class pqxx::robusttransaction |
Slightly slower, better-fortified version of transaction.
Requires PostgreSQL 10 or better.
robusttransaction is similar to transaction, but spends more time and effort to deal with the hopefully rare case that the connection to the backend is lost just while it's trying to commit. In such cases, the client does not know whether the backend (on the other side of the broken connection) managed to commit the transaction.
When this happens, robusttransaction tries to reconnect to the database and figure out what happened.
This service level was made optional since you may not want to pay the overhead where it is not necessary. Certainly the use of this class makes no sense for local connections, or for transactions that read the database but never modify it, or for noncritical database manipulations.
Besides being slower, it's also more complex. Which means that in practice a robusttransaction could actually fail more instead of less often than a normal transaction. What robusttransaction tries to achieve is to give you certainty, not just be more successful per se.
Public Member Functions | |
robusttransaction (connection &cx, std::string_view tname) | |
robusttransaction (connection &cx, std::string &&tname) | |
robusttransaction (connection &cx) | |
|
inline |
Create robusttransaction of given name.
cx | Connection inside which this robusttransaction should live. |
tname | optional human-readable name for this transaction. |
|
inline |
Create robusttransaction of given name.
cx | Connection inside which this robusttransaction should live. |
tname | optional human-readable name for this transaction. |
|
inlineexplicit |
Create robusttransaction of given name.
cx | Connection inside which this robusttransaction should live. |
class pqxx::transaction |
Standard back-end transaction, templatised on isolation level.
This is the type you'll normally want to use to represent a transaction on the database.
Usage example: double all wages.
Public Member Functions | |
transaction (connection &cx, std::string_view tname) | |
Begin a transaction. | |
transaction (connection &cx) | |
Begin a transaction. | |
|
inline |
Begin a transaction.
cx | Connection for this transaction to operate on. |
tname | Optional name for transaction. Must begin with a letter and may contain letters and digits only. |
|
inlineexplicit |
Begin a transaction.
cx | Connection for this transaction to operate on. may contain letters and digits only. |
class pqxx::__attribute__ | ( | (visibility("default")) | ) |
Low-level parser for C++ arrays.
Interface definition (and common code) for "transaction" classes.
Reference to one row in a result.
Build a parameter list for a parameterised or prepared statement.
Identity of a large object.
Reference to a field in a result set.
Approximate istream_iterator for icursorstream.
Simple read-only cursor represented as a stream of results.
Common definitions for cursor types.
Encrypt a password.
An ongoing, non-blocking stepping stone to a connection.
Connection to a database.
Clunky old API for parsing SQL arrays.
) as the separator between array elements. All built-in SQL types use comma, except for
box` which uses semicolon. However some custom types may not work.The input is a C-style string containing the textual representation of an array, as returned by the database. The parser reads this representation on the fly. The string must remain in memory until parsing is done.
Parse the array by making calls to get_next until it returns a juncture of done
. The juncture tells you what the parser found in that step: did the array "nest" to a deeper level, or "un-nest" back up?
Binary large object.
This is how you store data that may be too large for the BYTEA
type. Access operations are similar to those for a file: you can read, write, query or set the current reading/writing position, and so on.
These large objects live in their own storage on the server, indexed by an integer object identifier ("oid").
Two blob
objects may refer to the same actual large object in the database at the same time. Each will have its own reading/writing position, but writes to the one will of course affect what the other sees.
This is the first class to look at when you wish to work with a database through libpqxx. As per RAII principles, the connection opens during construction, and closes upon destruction. If the connection attempt fails, you will not get a connection object; the constructor will fail with a pqxx::broken_connection exception.
When creating a connection, you can pass a connection URI or a postgres connection string, to specify the database server's address, a login username, and so on. If you don't, the connection will try to obtain them from certain environment variables. If those are not set either, the default is to try and connect to the local system's port 5432.
Find more about connection strings here:
https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
The variables are documented here:
https://www.postgresql.org/docs/current/libpq-envars.html
To query or manipulate the database once connected, use one of the transaction classes (see pqxx/transaction_base.hxx) and perhaps also the transactor framework (see pqxx/transactor.hxx).
When a connection breaks, or fails to establish itself in the first place, you will typically get a broken_connection exception. This can happen at almost any point.
Use this when you want to create a connection to the database, but without blocking your whole thread. It is only available on systems that have the <fcntl.h>
header, and Windows.
Connecting in this way is probably not "faster" (it's more complicated and has some extra overhead), but in some situations you can use it to make your application as a whole faster. It all depends on having other useful work to do in the same thread, and being able to wait on a socket. If you have other I/O going on at the same time, your event loop can wait for both the libpqxx socket and your own sockets, and wake up whenever any of them is ready to do work.
Connecting in this way is not properly "asynchronous;" it's merely "nonblocking." This means it's not a super-high-performance mechanism like you might get with e.g. io_uring
. In particular, if we need to look up the database hostname in DNS, that will happen synchronously.
To use this, create the connecting
object, passing a connection string. Then loop: If wait_to_read returns true, wait for the socket to have incoming data on it. If wait_to_write returns true, wait for the socket to be ready for writing. Then call process to process any incoming or outgoing data. Do all of this until done returns true (or there is an exception). Finally, call produce to get the completed connection.
For example:
In C++ terms, fetches are always done in pre-increment or pre-decrement fashion–i.e. the result does not include the row the cursor is on at the beginning of the fetch, and the cursor ends up being positioned on the last row in the result.
There are singular positions akin to end()
at both the beginning and the end of the cursor's range of movement, although these fit in so naturally with the semantics that one rarely notices them. The cursor begins at the first of these, but any fetch in the forward direction will move the cursor off this position and onto the first row before returning anything.
SQL cursors can be tricky, especially in C++ since the two languages seem to have been designed on different planets. An SQL cursor has two singular positions akin to end()
on either side of the underlying result set.
These cultural differences are hidden from view somewhat by libpqxx, which tries to make SQL cursors behave more like familiar C++ entities such as iterators, sequences, streams, and containers.
Data is fetched from the cursor as a sequence of result objects. Each of these will contain the number of rows defined as the stream's stride, except of course the last block of data which may contain fewer rows.
This class can create or adopt cursors that live outside any backend transaction, which your backend version may not support.
Intended as an implementation of an input_iterator (as defined by the C++ Standard Library), this class supports only two basic operations: reading the current element, and moving forward. In addition to the minimal guarantees for istream_iterators, this class supports multiple successive reads of the same position (the current result set is cached in the iterator) even after copying and even after new data have been read from the stream. This appears to be a requirement for input_iterators. Comparisons are also supported in the general case.
The iterator does not care about its own position, however. Moving an iterator forward moves the underlying stream forward and reads the data from the new stream position, regardless of the iterator's old position in the stream.
The stream's stride defines the granularity for all iterator movement or access operations, i.e. "ici += 1" advances the stream by one stride's worth of rows, and "*ici++" reads one stride's worth of rows from the stream.
A field represents one entry in a row. It represents an actual value in the result set, and can be converted to various types.
Encapsulates the identity of a large object.
A largeobject must be accessed only from within a backend transaction, but the object's identity remains valid as long as the object exists.
When calling a parameterised statement or a prepared statement, in many cases you can pass parameters into the statement in the form of a pqxx::params
object.
A row represents one row (also called a row) in a query result set. It also acts as a container mapping column numbers or names to field values (see below):
The row itself acts like a (non-modifyable) container, complete with its own const_iterator and const_reverse_iterator.
Abstract base class for all transaction types.