I have a mod_perl2.0.4 / Apache2.2 web app running on CentOS 6.4 with PostgreSQL 9.0.
Until recently, I had this setup: Apache::DBI and DBI->connect_cached for all connections, which was starting to give FATAL: sorry, too many clients already
even in my development area where I'm the only user.
In an effort to debug this, I have removed all references to Apache::DBI, upgraded to the latest DBI, and replaced all occurrences of connect_cached with plain DBI->connect. It seems to me now that somewhat less connections are made and then left <IDLE>
. However, I realize that I haven't been calling disconnect() on all of my statement handles, because it had sounded like under Apache::DBI it wouldn't have made a difference.
My connections currently connect all as the same user, then lower their privileges based on which user it is via SET SESSION AUTHORIZATION. I do it this way because some other apps that use the database allow for a passworded login, which can pass the credentials directly to the database, but this particular web app uses an honour system login screen whereby you just click your name to log in. So it's future-security-ready but convenience-enabled at the moment. Also, database triggers for history and such rely on the session user being set correctly to track who did what.
Because I was concerned about a database handle being reused with the wrong session user, I pass { private_user_login => $login_role_name, PrintError => 0, RaiseError => 1, AutoCommit => 1}
to connect_cached to differentiate each connection by user. But since I always set the session authorization immediately after connecting, I suppose that all the private_user_login
hash does is make it so that for a given Apache process, there might be at least as many DB connections created and left idle as there are users, if eventually every user manages to randomly use a given Apache process. Meanwhile, because I don't disconnect any handles, they eventually get used up.
My question is, is it safe to take out the private_user_login
to make all the connection handles look the same, to cut down on the number of connections left open, or is it possible that a connection handle could be re-used in the middle of a script (after setting the session user) by a different user, thus creating a race condition? Also, although Apache::DBI's docs say I needn't remove disconnect()
calls, should I still have such a call at the end of every one of my scripts so that Apache::DBI can decide whether to disconnect?
In other words, without my private connection variable, do SET SESSION AUTHORIZATION's effects persist when the next Apache::DBI->connect() reuses the existing connection? If so, is it ever possible that a connection is re-used by another request while one request is currently executing but not currently using the database handle?