Pg_query () - "Unable to establish connection with lock mode (error # 8)

Question

Our application inserts data from CSV files into Redshift using a query COPY. He loads c. 700 GB in total through c. 11000 files. Each file is mapped to one database table. We run SELECT COUNT(*) FROM <table>before and after each COPYfor registration and health checks.

After a certain period of time (seems to change), the call pg_query()returns this E_NOTICEPHP error :

pg_query() - "Cannot set connection to blocking mode (Error No. 8)

This is returned for the request SELECT COUNT(*) FROM <table>; our application throws all PHP errors to exceptions. Removing this distribution gives us this error message in addition to the E_NOTICEabove on SELECTboth and COPY:

Failed to run query: server closed the connection unexpectedly
    This probably means the server terminated abnormally

The request COPYdefinitely does not insert files.

Once this error occurs every time you try to insert a file. He does not seem to allow himself.

First, we opened one database connection (opened with pg_connect()) at the beginning of the script and reused it for all the following SELECTand COPYs. When we got E_NOTICEabove, we tried - as an experiment - to open a new connection for each request. This has not changed anything.

our current pgsql parameters in the PHP ini file:

pgsql.allow_persistent = Off
pgsql.auto_reset_persistent = Off
pgsql.max_persistent = -1
pgsql.max_links = -1
pgsql.ignore_notice = 0
pgsql.log_notice = 0

What could be the cause of this error and how can it be solved?

Refresh . See attached screen. It seems we only have the default request queue with 'concurrency' set to 5, and the timeout set to 0 MS?

: , ( "username_removed" , ):

main=# select * from stv_sessions;
       starttime        | process |                     user_name                      |                      db_name
------------------------+---------+----------------------------------------------------+----------------------------------------------------
 2017-03-24 10:07:49.50 |   18263 | rdsdb                                              | dev
 2017-03-24 10:08:41.50 |   18692 | rdsdb                                              | dev
 2017-03-30 10:34:49.50 |   21197 | <username_removed>                              | main
 2017-03-24 10:09:39.50 |   18985 | rdsdb                                              | dev
 2017-03-30 10:36:40.50 |   21605 | root                                               | main
 2017-03-30 10:52:13.50 |   23516 | rdsdb                                              | dev
 2017-03-30 10:56:10.50 |   23886 | root                                               | main

Answer 1

pg_connect pg_pconnect? , .

, , *. . . , "auto_increment". , , .

.

, . " pgsql.auto_reset_persistent = Off Apache, ".

, , , , .

https://www.postgresql.org/docs/9.5/static/explicit-locking.html#LOCKING-ROWS

Answer 2

. , keepalives .

keepalives=1 keepalive . keepalives_idle=60.

keepalives, , .

TCP Keep-Alive PDO Connection.