13.4 Dangers and Hardware Issues

Up to now you have seen that persistent connections can be used to gain a lot of performance, which is positive. However, persistent database connections can also be a dangerous thing to use. In this section you will learn about these dangers and you will take a closer look at some other aspects such as memory consumption and system configuration.

13.4.1 Operating System Issues

When working with persistent database connections, it can easily happen that the standard configuration of your system is not enough. With persistent connections, the number of open connections, in many cases, is higher than when working with nonpersistent connections. This will lead to higher consumption of filehandles, memory, and other system resources. In some cases, the configuration of your system needs to be changed in order to achieve higher performance and to keep the system up and running. If you have to change system overall settings, check out Chapter 10 to see how the most important settings can be changed. Keep in mind that in the event of failure, the problem might have nothing to do with PostgreSQL but could be caused by the configuration of the underlying operating system. In that case, it is important to find the bottleneck and to reconfigure your system.

13.4.2 Transactions

Persistent database connections and transactions are a crucial point. If your application is not fully debugged, you can easily run into severe problems that are difficult to solve. Let's take a look at a simple scenario.

First you can create a table:

 phpbook=# CREATE TABLE course (id int4, name text, description text); CREATE 

In the listing, you can see a script using explicit transactions:

 <?php         $dbh = pg_pconnect("user=postgres dbname=phpbook");         if      (!$dbh) { echo "cannot connect to database<br>\n"; }         $stat = pg_exec($dbh, "BEGIN");         if      (!$stat) { echo "Transaction cannot be started<br>\n"; }         echo "attempting to insert data<br>\n";         $sql = "INSERT INTO course VALUES (9, 'C Programming', NULL)";         $stat = pg_exec($dbh, $sql);         if      (!$stat) { echo "Cannot insert data<br>\n"; }         echo '<a href="data2.php">continue</a>';         pg_close($dbh); ?> 

First a transaction is started and then a record is added to the table. The first time the script is executed, no problems occur:

 attempting to insert data continue 

However, when executing the same script again, errors might occur. It is not certain that this error will occur (it depends on what you have done with the database before). Let's see what PHP will display:

 Transaction cannot be started attempting to insert data Cannot insert data continue 

The transaction cannot be started because a transaction is already in progress. Recall that persistent database connections are recycled connections, which means that no new backend process has been started when executing the script the second time. Therefore the transaction is still open, and because PostgreSQL does not support nested transactions, an error occurs.

After executing the scripts twice, let's take a look at the content of the table:

 phpbook=# SELECT * FROM course;  id | name | description ----+------+------------- (0 rows) 

No records have been inserted into the table, but the bigger problem is that the system is in a way locked. There is no script committing the transaction and the backend process is not killed automatically. Therefore the entire system cannot be used any more unless the problem is solved by restarting the database.

When you're working with nonpersistent connections, a transaction is automatically rolled back at the end of a PHP file when the backend process related to a connection is destroyed. With persistent connections, this does not happen, so open transactions will be in your system if your applications have not been implemented properly.