pg_query() – “Cannot set connection to blocking mode (Error No. 8)


Our application is inserting data from CSV files to Redshift using a COPY query. It uploads c. 700 GB in total across c. 11000 files. Each file maps to one database table. We run a SELECT COUNT(*) FROM <table> before and after each COPY for logging and sanity checking.

After a period of time (it seems to vary) the call to pg_query() returns this E_NOTICE PHP error:

This is returned for the SELECT COUNT(*) FROM <table> query; our application propagates all PHP Errors to Exceptions. Removing this propagation gives us this error message in addition to the E_NOTICE above on both the SELECT and the COPY:

The COPY query definitely does not actually insert the files.

Once present, this error happens on every attempt to insert a file. It does not seem to resolve itself.

We initially had one database connection open (opened with pg_connect()) at the start of the script and re-used it for all following SELECTs and COPYs. When we got the E_NOTICE above we then tried – just as an experiment – opening a fresh connection for each query. This changed nothing.

our current pgsql settings in the PHP ini file are:

What could be causing this error and how could it be resolved?

Update – See the attached screen. It seems we only have the default query queue with ‘concurrency’ set to 5 and the timeout set to 0 MS?

enter image description here

Also: we only have these DB users connected while the application is running (the one with ‘username_removed’ is the only one that is created by our application):


Have you tried to change pg_connect to pg_pconnect? This will reuse an existent connection and will decrease the connections to your database and the server will run smoothly.

I would say to never do a count using *. You are forcing the database to create a hash for each register and count it. Use some value that is unique. If you don’t have it, consider create a sequence and use it in an “auto_increment” field.
I see that you work with huge files and any performance improvement will help your work

You can also check your blocking mode config.

I got this searching the web, may work for you.
“From changing pgsql.auto_reset_persistent = Off to On and restarting Apache, this resolves the error.”

My last advice is about transactions, if you are using transactions you can set your count Select to ignore locked rows and it will make your count run faster.

Leave a Reply