postgresql php connection limit exceeded for non-superusers

Question

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.

Are you using persistent mode connections? stackoverflow.com/questions/3332074/… — GordonM
– GordonM, Commented Feb 3, 2012 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
– Alpaus, Commented Feb 3, 2012 at 21:00

Community · Accepted Answer · 2017-05-23 11:43:27Z

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.

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Feb 3, 2012 at 23:09

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alpaus Over a year ago

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.

Collectives™ on Stack Overflow

postgresql php connection limit exceeded for non-superusers

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related