25.4. Dynamic Tracing

PostgreSQL provides facilities to support dynamic tracing of the database server. This allows an external utility to be called at specific points in the code and thereby trace execution. Currently, this facility is primarily intended for use by database developers, as it requires substantial familiarity with the code.

A number of trace points, often called probes, are already inserted into the source code. By default these probes are disabled, and the user needs to explicitly tell the configure script to make the probes available in PostgreSQL.

Currently, only the DTrace utility is supported, which is only available on Solaris Express and Solaris 10+. It is expected that DTrace will be available in the future on FreeBSD and Mac OS X. Supporting other dynamic tracing utilities is theoretically possible by changing the definitions for the PG_TRACE macros in src/include/pg_trace.h.

25.4.1. Compiling for Dynamic Tracing

By default, trace points are disabled, so you will need to explicitly tell the configure script to make the probes available in PostgreSQL. To include DTrace support specify --enable-dtrace to configure. See Section 14.5 for further information.

25.4.2. Built-in Trace Points

A few standard trace points are provided in the source code (of course, more can be added as needed for a particular problem). These are shown in Table 25-3.

Table 25-3. Built-in Trace Points

transaction__start(int transactionId)The start of a new transaction.
transaction__commit(int transactionId)The successful completion of a transaction.
transaction__abort(int transactionId)The unsuccessful completion of a transaction.
lwlock__acquire(int lockid, int mode)An LWLock has been acquired.
lwlock__release(int lockid, int mode)An LWLock has been released.
lwlock__startwait(int lockid, int mode)An LWLock was not immediately available and a backend has begun to wait for the lock to become available.
lwlock__endwait(int lockid, int mode)A backend has been released from its wait for an LWLock.
lwlock__condacquire(int lockid, int mode)An LWLock was successfully acquired when the caller specified no waiting.
lwlock__condacquire__fail(int lockid, int mode)An LWLock was not successfully acquired when the caller specified no waiting.
lock__startwait(int locktag_field2, int lockmode)A request for a heavyweight lock (lmgr lock) has begun to wait because the lock is not available.
lock__endwait(int locktag_field2, int lockmode)A request for a heavyweight lock (lmgr lock) has finished waiting (i.e., has acquired the lock).

25.4.3. Using Trace Points

The example below shows a DTrace script for analyzing transaction counts on the system, as an alternative to snapshotting pg_stat_database before and after a performance test.

#!/usr/sbin/dtrace -qs 

      @start["Start"] = count();
      self->ts  = timestamp;

      @abort["Abort"] = count();

      @commit["Commit"] = count();
      @time["Total time (ns)"] = sum(timestamp - self->ts);

Note how the double underline in trace point names needs to be replaced by a hyphen when using D script. When executed, the example D script gives output such as:

# ./txn_count.d `pgrep -n postgres`

Start                                          71
Commit                                         70
Total time (ns)                        2312105013

You should remember that trace programs need to be carefully written and debugged prior to their use, otherwise the trace information collected may be meaningless. In most cases where problems are found it is the instrumentation that is at fault, not the underlying system. When discussing information found using dynamic tracing, be sure to enclose the script used to allow that too to be checked and discussed.

25.4.4. Defining Trace Points

New trace points can be defined within the code wherever the developer desires, though this will require a recompilation.

A trace point can be inserted by using one of the trace macros. These are chosen according to how many variables will be made available for inspection at that trace point. Tracing the occurrence of an event can be achieved with a single line, using just the trace point name, e.g.

PG_TRACE (my__new__trace__point);

More complex trace points can be provided with one or more variables for inspection by the dynamic tracing utility by using the PG_TRACEn macro that corresponds to the number of parameters after the trace point name:

PG_TRACE3 (my__complex__event, varX, varY, varZ);

The definition of the transaction__start trace point is shown below:

static void

     * generate a new transaction id
    s->transactionId = GetNewTransactionId(false);


    PG_TRACE1(transaction__start, s->transactionId);


Note how the transaction ID is made available to the dynamic tracing utility.

The dynamic tracing utility may require you to further define these trace points. For example, DTrace requires you to add new probes to the file src/backend/utils/probes.d as shown here:

provider postgresql {
      probe transaction__start(int);

You should take care that the data types specified for the probe arguments match the datatypes of the variables used in the PG_TRACE macro. This is not checked at compile time. You can check that your newly added trace point is available by recompiling, then running the new binary, and as root, executing a DTrace command such as:

dtrace -l -n transaction-start