Chapter 2. Class overview

connection represents a connection from your program to a PostgreSQL™ back-end server. Naturally your program may open multiple connections, simultaneous or not, to any number of databases.

work, actually an alias for the transaction class template, represents a transaction being executed in the context of a connection. This is a unit of work that must be completed as a whole by the database backend. If execution fails, everything done within that transaction is undone by the backend as if it had never happened. ^[1] You may execute any number of transactions on a single connection, but only one at a time.

I recommend that you do not set up your own transactions, but have them managed for you by a transactor instead (see below).

A result is a container holding the resulting data coming out of a query or command executed in a transaction. It behaves just like a standard C++ container as found in the STL, although its contents can't be modified in any way. (You can assign or copy it cheaply though; it's basically a smart pointer). The rows held in the result in turn behave much like non-modifiable containers themselves.

broken_connection is an exception class that is thrown if libpqxx™ loses its connection to the back-end. It is derived from the standard C++ exception std::runtime_error, and can generally be treated as such.

sql_error is an exception class that is thrown if a query to the back-end fails. Like broken_connection it is derived from std::runtime_error, but it also carries information about the failed query. This may be useful for debugging.

icursorstream is a way of iterating over a query result using an SQL cursor. This can be useful for selectively retrieving parts of a query, or for retrieving and processing results in chunks so your program can do useful work on one chunk while the next is still coming in over a network connection. Or you may want to use this to give progress reports to the user between chunk transfers.

in_doubt_error is an exception class to indicate a rare and difficult error condition. Explaining this is a bit painful, since it invalidates some of what was said above (and is commonly said in the database world).

In theory, a "transaction" is an integral, atomic unit of work that either fails or succeeds. This is a beautiful idea, but in practice there is nothing that cannot be broken. There is a tiny risk that, like Schroedinger's Cat, the transaction can end up in an indeterminate state. This happens when the connection to the backend is lost just when you were waiting for confirmation after completing your transaction. Your transaction may have succeeded (in fact it probably has), but if the connection is lost during just this small time window, there is no way to confirm this to your application.

When this happens, libpqxx throws an in_doubt_error on the client side, which you may want to catch as a special case. If you do, make sure all alarm bells go off!

See also the description for robusttransaction, which sacrifices some performance to minimize this risk.

nontransaction is just like a regular transaction as far your code is concerned (except that it's not a template). The big difference is that where the latter opens a back-end transaction to keep your view and modifications of the database atomic, the former does nothing to maintain integrity. It just passes your queries directly to the database, with no enclosing transaction. This difference is expressed by the fact that the other transaction classes are derived from dbtransaction, an abstract class that expresses the presence of a real backend transaction, whereas nontransaction is not.

This may give you better performance if your transaction does not modify the database, but if it does, may have unforeseen implications if you expect certain levels of consistency in the database--especially if other processes or threads may be modifying the database at the same time. You'll probably only want to use this in the following cases:

robusttransaction (a template just like transaction) is essentially the same as a transaction, ie. an atomic unit of work, except that it works harder to prevent in_doubt_errors from happening. This comes at the cost of some performance, which is one reason why this behaviour isn't the default. It also creates some extra tables in your database to keep track of what's happening.

Use this class instead of plain old transaction if you get in_doubt_errors in your application--but only after thoroughly inspecting your code for bugs that might be causing the problem, or you'll only be hiding glitches that should be fixed instead.

transaction_base defines the common public interface for the transaction-like classes: nontransaction, robusttransaction, and transaction itself. To look up methods available in these classes, see the definition (or documentation) for transaction_base instead.

You'll usually want to refer to the default transaction type as work, an alias that means "transaction with default (read-committed) isolation level."

transactor<> provides a framework that makes it easier for you to write correct, robust transaction code to be executed in a transaction. You should generally try to write your code as a class derived from transactor<>, but this is not required.

A transactor-derived class may select its own "quality of service" in transactional integrity by choosing a transaction type as its template argument, e.g. transactor<robusttransaction<> >. The default transactor<> is equivalent to transactor<work>. Within your class you can refer to the transaction type used as argument_type.

notification_receiver is an instance of the Observer design pattern. Any code connected to a backend may flag an event using the PostgreSQL™ NOTIFY command. Frontends connected to that same backend database may be listening for an event of that name to occur. A frontend using libpqxx™ does this by registering an observer object derived from notification_receiver, which will be invoked by libpqxx™ to handle the event when it occurs.

Something every programmer using notification receivers in PostgreSQL should know, by the way, is that notifications are not delivered to your program while it is inside a back-end transaction. Nor are they sent until the notifying process has committed any transaction it may be in. This makes sense from a correctness point of view (otherwise your processing of the event might get rolled back just because the transaction you're in happens to fail), but may be a little confusing to the unaware programmer.

row refers to a row of data within a result object.

field refers to one field within a row. A field contains one retrieved value such as a number or a string, and can be read into variables of various types such as ints or strings.

result::const_iterator allows you to enumerate the rows in a result in a similar way to the iterators defined by any standard C++ container.

result::row::const_iterator similarly iterates over the fields in a row.

esc, found in the transaction classes, is the canonical way to use strings in your SQL. This function escapes any quotes, backslashes, and other characters in your input string that would otherwise cause syntax errors (or worse, security holes) in your queries when included directly.

Use these functions whenever you want to have a variable string in your SQL queries. If you don't, you'll be left with annoying bugs or possibly even security holes in your program. Use this function, use this function, use this function. Please. Make me proud.

to_string knows how to render many types of values as strings. This is used internally by libpqxx to convert values coming out of or going into the backend, and doesn't respect locale settings.

from_string is the inverse of to_string. It is used by eg. field::to() to convert the incoming field data (which is in text format) to the desired C++ type.