11.6 Tracing a PostgreSQL Connection

Especially for debugging purposes, tracing a connection to a PostgreSQL server can be useful. Tracing a connection means that the data transmitted from and to the back end is monitored and can be used to see what PostgreSQL does internally. In general, the information generated by a trace is only useful if you are familiar with PostgreSQL's internal protocol. However, we need to see how tracing can be turned on and off.

Take a look at the next example:

 <?php         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh) { echo "error while connecting.<br>\n"; }         $status = pg_trace("/tmp/trace.log");         $result = pg_exec($dbh, "SELECT id, name, color FROM plant LIMIT 1");         $rows = pg_numrows($result);         for     ($i = 0; $i < $rows; $i++)         {                 $data = pg_fetch_row($result, $i);                 echo "$data[0], $data[1], $data[2]<br>\n";         } ?> 

The target is to select one record from the table called plant and to store the tracing information in /tmp/trace.log. To turn on tracing, PHP provides a function called pg_trace. Let's see what will be displayed on the screen when the script is executed:

 1, Sambucus nigra, yellow 

One line is displayed on the screen. In addition, the logfile has been created and a number of lines have been added to it:

 To backend> Q To backend> SELECT id, name, color FROM plant LIMIT 1 From backend> P From backend> "blank" From backend> T From backend (#2)> 3 From backend> "id" From backend (#4)> 23 From backend (#2)> 4 From backend (#4)> -1 From backend> "name" From backend (#4)> 25 From backend (#2)> 65535 From backend (#4)> -1 From backend> "color" From backend (#4)> 25 From backend (#2)> 65535 From backend (#4)> -1 From backend> D From backend (1)> à From backend (#4)> 5 From backend (1)> 1 From backend (#4)> 18 From backend (14)> Sambucus nigra From backend (#4)> 10 From backend (6)> yellow From backend> C From backend> "SELECT" From backend> Z From backend> Z To backend> X 

As you can see, a lot of logging information has been written into the file. To use the logging information effectively, you must take a closer look at PostgreSQL's internals.

To turn off tracing again, pg_untrace must be used.