13.2 An Example

Now that we've reviewed the theoretical background of persistent database connections with PHP and PostgreSQL, it's time to take a look at some practical examples.

If you are a user of PostgreSQL 7.2, you will see that two processes are running at startup time. You can easily find this out by piping the content of the process table to grep:

 [hs@athlon html]$ ps ax | grep postgres 21647 pts/4    S      0:00 postgres: stats buffer process 21649 pts/4    S      0:00 postgres: stats collector process  4338 pts/4    S      0:00 grep postgres 

Let's write a script that does nothing but connect to the database and display a message:

 <?php         $dbh = pg_connect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh)         {                 echo "error while connecting.<br>\n";         }         else         {                 echo "connection established<br>\n";         } ?> 

As you can see in the next listing, the number of processes running has not changed, although the connection to the database has not been closed explicitly:

 [hs@athlon html]$ ps ax | grep postgres 21647 pts/4    SW     0:00 postgres: stats buffer process 21649 pts/4    SW     0:00 postgres: stats collector process  4344 pts/4    S      0:00 grep postgres 

All processes started by PHP have been removed without any problems. In the next step you can modify the PHP script you just saw by changing the command used for connecting to the database to pg_pconnect:

 <?php         $dbh = pg_pconnect("host=localhost user=postgres dbname=phpbook");         if      (!$dbh)         {                 echo "error while connecting.<br>\n";         }         else         {                 echo "connection established<br>\n";         } ?>, 

The script still does the same thing, but this time a persistent connection to the database is established. If you take a look at all active PostgreSQL processes now, the situation will look different:

 [hs@athlon html]$ ps ax | grep postgres 21647 pts/4    S      0:00 postgres: stats buffer process 21649 pts/4    S      0:00 postgres: stats collector process  4347 pts/4    S      0:00 postgres: postgres phpbook 127.0.0.1 idle  4349 pts/4    S      0:00 grep postgres 

One active connection is still in memory because it has not been closed at the end of the PHP script. If you execute the PHP script more often than once, PostgreSQL will not always create a new backend process because running backends can be reused, and this will save time and reduce the load on your database.