Re: Pg + perl + apache

You could try something like this:

Assuming you are using perl+DBI (as per your examples).

If you are using modperl:
[modperl can override the "connect to db" for connection reuse/pooling]

begin cgi web app:
connect to db
rollback
do stuff
do more stuff
commit if ok
by default rollback
disconnect.

If you are using fastcgi:
[fastcgi - explicit DB connection reuse]
connect to db
while (fastcgi connection) {
  rollback
  do stuff
  do more stuff
  commit if ok
  by default rollback
}
rollback
disconnect

You probably want to do a rollback before you do stuff so that the
transaction times are correct. AFAIK Perl DBI automatically does a BEGIN
after a rollback or commit, so if the previous transaction was rolled back
or committed 1 hour ago, you'd be reusing a transaction that begun 1 hour
ago for something that's happening now.

This would cause select 'now'::timestamp or SELECT CURRENT_TIMESTAMP return
times that are 1 hour ago, which is usually not what you want.

Hope that helps,
At 12:40 AM 2/16/2004 -0500, Michael L. Artz wrote:

>Thanks, that did help to debug the application.  I found that my errors
>weren't so random after all ... if I went to a page with a bad query, then
>I would start getting the error.
>
>should throw an error for *both* queries?  So I guess I need to issue a
>commit after I do my queries, or else turn autocommit on.