3

I have a php app with a postgresql backend, using PDO to interact with the database. It has been up and running for a couple of months without issue but all of a sudden tonight i'm getting a persistent 500 error on page loads that require a database connection and the error.log file for the server shows this:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[08006] 
[7] FATAL:  connection limit exceeded for non-superusers'

The user who connects to the database has a rollconnlimit of -1 so this should not be an issue. When I query the pg_stat_activity table, I can't see any connections.

Any idea what would be causing this? I haven't changed any code that would result in more or less database connections.

I am happy to post some code, but not sure what parts of my code to post without guidance on what might be causing the issue.

Alpaus
  • 646
  • 1
  • 7
  • 21
  • Are you using persistent mode connections? http://stackoverflow.com/questions/3332074/what-are-the-disadvantages-of-using-persistent-connection-in-pdo – GordonM Feb 03 '12 at 12:00
  • No, I'm using single use connections (I was put off persistence by the very answer you linked to!) so they should be dying. I've done some more debugging and believe it's an issue with my code, using am existing db method to retrieve a single item in a for each loop (lazy) rather than just writing a new db method which retrieves the records once THEN loop through them. – Alpaus Feb 03 '12 at 21:00

1 Answers1

3

The immediate reason for the Postgres error is probably the setting of max_connections.

But what I read in your comment, you should really fix that. Going to the DB for every single record and then loop locally in the app is the worst case scenario.

Retrieving all rows at once and processing them in the app is far superior, but still not good. As far as possible, you should have the database engine process data and only retrieve the results you need in your app. That's what database engines are good at. No need to ship loads of data and only actually use a tiny fraction of it. I wrote more here.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    Thanks Erwin, sorry, I was writing on my phone so being too brief. At the moment, it uses a query to get IDs and then passes those IDs to php object which loops and retrieves details for each one. Rookie mistake! My preference (and what I'll be changing the code to) is modify the original query to bring back all the data I need with the first (filtered) query and then loop through the resultset to output. – Alpaus Feb 03 '12 at 23:30