Class::DBI, PostgreSQL and Transactions

Once again, I managed to see the dreaded ”<IDLE> in transaction” message from PostgreSQL today. This means that I have a connection to the database open, which is currently inside a transaction. This is bad because locks are held open in a transaction. So, what to do about it? Firstly, examine the documentation.

1. <a href="http://search.cpan.org/_{dbdpg/DBD-Pg-1.43/Pg.pm#Transactions”>DBD::Pg on transactions.}
2. <a href="http://search.cpan.org/timb/DBI-1.48/DBI.pm#Transactions”>DBI on transactions.
3. <a href="http://search.cpan.org/_{tmtm/Class-DBI-v3.0.4/lib/Class/DBI.pm#TRANSACTIONS”>Class::DBI on transactions (which I’m amused to see that I contributed when I first came across this problem several years ago)}

Now, the application I’m working on at present has no particular need for transactions for the most part. So it would definitely be easier to turn on <a href="http://search.cpan.org/timb/DBI-1.48/DBI.pm#AutoCommit”>AutoCommit and only drop into a transaction when I absolutely have to.

What do I know so far?

• AutoCommit defaults to on.
• But Class::DBI disables it by default for PostgreSQL.
• My application is running as part of an HTML::Mason web page.
• When I make any call that goes near the database on a Class::DBI object, DBD::Pg executes an implicit BEGIN before the SQL that it has been sent executes. Only the first time though. This is because DBD::Pg is a database where a transaction must be explicitly started. DBI says the driver will automatically begin an explicit transaction when AutoCommit is turned off.
• In a nutshell, I need to get AutoCommit turned on until I need it.

There, wasn’t that simple?