Help

The attentive programmer will therefore want to do something to make sure the SIGTERM terminates not only the CGI program but also the database process. One variant would simply be to kill it. PostgreSQL starts a separate process for each user, so that is equivalent to aborting the current transaction.

You now have three problems to solve:

• The SIGTERM must be treated promptly.
• The PID of the process database has to be found.
• The database process must be stopped.

As a solution to the first problem, an experienced Perl programmer will probably immediately think of $SIG{TERM}.

Unfortunately, that does not work here because modern Perl versions only deliver asynchronous signals at secure points in the program. The middle of a C function being called does not qualify as such.

The handler will thus not be called until $sth->execute returns in line 12. The POSIX::sigaction module, however, lets you install the signal handler so that the signal is delivered promptly.

Network Problem

A glance at the documentation for DBD::Pg [1] discloses a solution to the second problem. The driver provides the PID of the database process in $dbh->{pg_pid}.

Another problem arises, however, if the database can be running on another computer. Even if the CGI process has the necessary rights to send a signal to the database process, it cannot terminate the process on another computer.

However, the PostgreSQL documentation [2] reveals a database function for this purpose: pg_cancel_backend(int).

Final Product

Listing 4 shows the complete program, burn1.pl. There are some new lines: 5 and 12 to 22. Line 22 installs the sig function as a signal handler for SIGTERM. When the signal arrives, line 13 opens a new connection to the database and calls the pg_cancel_backend function. After this the program terminates. The call to

$ curl http://localhost/cgi/burn1.pl\?120

now returns after five seconds, and the database process is also gone.

01 #!/usr/bin/perl
02
03 use common::sense;
04 use DBI;
05 use POSIX qw(SIGTERM);
06
07 print "Status: 200\nContent-Type: text/plain\n\n";
08 $|=1; $|=0;                     # flush
09
10 my $dbh=DBI->connect('dbi:Pg:dbname=r2', 'ipp', undef, {RaiseError=>1});
11
12 sub sig {
13   my $db2=DBI->connect('dbi:Pg:dbname=r2', 'postgres', undef, {RaiseError=>1});
14   my $st2=$db2->prepare('select pg_cancel_backend(?)');
15   $st2->execute($dbh->{pg_pid});
16   if( !$st2->fetchrow_arrayref->[0] ) {
17     warn "couldn't stop backend PID=$dbh->{pg_pid}\n";
18   }
19   exit 1;
20 }
21
22 POSIX::sigaction(SIGTERM, POSIX::SigAction->new(\&sig)) or die "Error setting SIGTERM handler: $!\n";
23
24 my $sth=$dbh->prepare('select burncpu(?)');
25 $sth->execute((($ENV{QUERY_STRING}+0) || .5).'s');
26
27 while( my $row=$sth->fetchrow_arrayref ) {
28   print "@$row\n";
29 }

However, this solution has serious drawbacks. The first is a security problem. Only the administrator of the database is allowed to call pg_cancel_backend. The program thus logs in as user postgres for the second connection. From the viewpoint of security, a CGI program should not have administrative rights to the database.

Moreover, the POSIX::sigaction style of signal handling causes the same problems that prompted the introduction of safe signals. In the given case, it is probably irrelevant because, when the signal arrives, the process is very likely stuck in a read, recv, or similar system call, waiting for data. Additionally, the signal handler terminates the program. In other words, the signal does not interrupt any critical areas, such as malloc, which would lead to a memory management error followed by a crash. Also, there are no memory leaks because the program terminates anyway. Generally, however, use of POSIX::sigaction is discouraged in Perl programs.

A third problem arises from the need to open a second database connection just to terminate an existing one. It is rather like taking a sledgehammer to crack a nut, and you could hit limits so that the new connection cannot be opened.