Loading...
Searching...
No Matches
Transaction classes

## 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.

## Detailed Description

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 Documentation

## pqxx::robusttransaction

class pqxx::robusttransaction

template<isolation_level ISOLATION = read_committed>
class pqxx::robusttransaction< ISOLATION >

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.

Inheritance diagram for pqxx::robusttransaction< ISOLATION >:
[legend]
Collaboration diagram for pqxx::robusttransaction< ISOLATION >:
[legend]

### Public Member Functions

| robusttransaction (connection &cx, std::string_view tname)
| robusttransaction (connection &cx, std::string &&tname)
| robusttransaction (connection &cx)

#### Constructor & Destructor Documentation

## robusttransaction() [1/3]

template<isolation_level ISOLATION = read_committed>

pqxx::robusttransaction< ISOLATION >::robusttransaction | ( | connection & | cx,
|  | std::string_view | tname )

inline

Create robusttransaction of given name.

Parameters

  cx | Connection inside which this robusttransaction should live.
  tname | optional human-readable name for this transaction.

## robusttransaction() [2/3]

template<isolation_level ISOLATION = read_committed>

pqxx::robusttransaction< ISOLATION >::robusttransaction | ( | connection & | cx,
|  | std::string && | tname )

inline

Create robusttransaction of given name.

Parameters

  cx | Connection inside which this robusttransaction should live.
  tname | optional human-readable name for this transaction.

## robusttransaction() [3/3]

template<isolation_level ISOLATION = read_committed>

pqxx::robusttransaction< ISOLATION >::robusttransaction | ( | connection & | cx | ) |

inline explicit

Create robusttransaction of given name.

Parameters

  cx | Connection inside which this robusttransaction should live.

## pqxx::transaction

class pqxx::transaction

template<isolation_level ISOLATION = isolation_level::read_committed, write_policy READWRITE = write_policy::read_write>
class pqxx::transaction< ISOLATION, READWRITE >

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.

extern connection cx;
work tx(cx);
try
{
tx.exec("UPDATE employees SET wage=wage*2").no_rows();
tx.commit(); // NOTE: do this inside try block
}
catch (exception const &e)
{
cerr << e.what() << endl;
tx.abort(); // Usually not needed; same happens when tx's life ends.
}
pqxx::work
transaction<> work
The default transaction type.
Definition transaction.hxx:100
Inheritance diagram for pqxx::transaction< ISOLATION, READWRITE >:
[legend]
Collaboration diagram for pqxx::transaction< ISOLATION, READWRITE >:
[legend]

### Public Member Functions

| transaction (connection &cx, std::string_view tname)
| Begin a transaction.
| transaction (connection &cx)
| Begin a transaction.

#### Constructor & Destructor Documentation

## transaction() [1/2]

template<isolation_level ISOLATION = isolation_level::read_committed, write_policy READWRITE = write_policy::read_write>

pqxx::transaction< ISOLATION, READWRITE >::transaction | ( | connection & | cx,
|  | std::string_view | tname )

inline

Begin a transaction.

Parameters

  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.

## transaction() [2/2]

template<isolation_level ISOLATION = isolation_level::read_committed, write_policy READWRITE = write_policy::read_write>

pqxx::transaction< ISOLATION, READWRITE >::transaction | ( | connection & | cx | ) |

inline explicit

Begin a transaction.

Parameters

  cx | Connection for this transaction to operate on. may contain letters and digits only.

## Function Documentation

## __attribute__()

class pqxx::__attribute__ | ( | (visibility("default")) |  | ) |

nodiscard

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.

Deprecated
  Use pqxx::array instead.

Clunky old API for parsing SQL arrays.

Warning
  This parser will only work reliably if your client encoding is UTF-8, ASCII, or a "safe ASCII superset" (such as the EUC encodings) where a byte value in the ASCII range can only occur as an actual ASCII character, never as one byte in a multi-byte character.
  The parser only supports array element types which use a comma (',') 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.

Warning
  On Unix-like systems, including GNU and BSD systems, your program may receive the SIGPIPE signal when the connection to the backend breaks. By default this signal will abort your program. Use "signal(SIGPIPE, SIG_IGN)" if you want your program to continue running after a connection fails.

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:

pqxx::connecting cg{};
// Loop until we're done connecting.
while (!cg.done())
{
wait_for_fd(cg.sock(), cg.wait_to_read(), cg.wait_to_write());
cg.process();
}
pqxx::connection cx = std::move(cg).produce();
// At this point, cx is a working connection. You can no longer use
// cg at all.

Deprecated
  Use connection::encrypt_password instead.

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.

Warning
  Do not read from the underlying stream or its cursor, move its read position, or change its stride, between the time the first icursor_iterator on it is created and the time its last icursor_iterator is destroyed.
  Manipulating these iterators within the context of a single cursor stream is not thread-safe. Creating a new iterator, copying one, or destroying one affects the stream as a whole.

A field represents one entry in a row. It represents an actual value in the result set, and can be converted to various types.

Deprecated
  Use the blob class instead.

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):

cout << row["date"].c_str() << ": " << row["name"].c_str() << endl;

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.