[pgpool-general: 299] Trouble with connection during long/large data queries

[Joe Barnett]

We are using pgpool in front of a postrgesql 9.1 database and, 
generally, we are experiencing tremendous improvement in performance 
(and even stability) over accessing the database directly.  We are 
seeing one behavior, however, which is troubling: long queries which 
produce much resulting data seem to kill (or are killed by) the pgpool 
process handling the connection.  By long/large, I mean connections 
lasting ninety minutes or more (often comprising just a handful of long 
running queries) and returning >1GB of data.  Specifically, this is 
happening when querying (and returning) large, global sets of geographic 
data (which are not conducive to being run as smaller, independent 
queries), though we can reproduce this problem with any query taking 
sufficient time and returning sufficient data.

The workaround is to run these queries against the database directly, 
bypassing pgpool.  We would, however, like to require nearly all access 
to the database to go through pgpool, restricting direct access to 
things of an administrative nature.

Ideas, advice, and criticism welcome, thanks,

Joe Barnett

--- Errors and logs follow ---

### Error returned when connection goes away (from Ruby script):

(with SSL enabled)
./bin/shapes.rb:150:in `exec': SSL SYSCALL error: EOF detected 
(PG::Error)

(with SSL disabled)
./bin/shapes.rb:150:in `exec': PG::Error

### Excerpts from pgpool.log:

2012-03-26 13:16:35 LOG:   pid 14860: statement: SELECT 
max(octet_length(hist_file_path::text)) from temp_shapes_daily
2012-03-26 13:16:35 LOG:   pid 14860: DB node id: 0 backend pid: 16005 
statement: SELECT max(octet_length(hist_file_path::text)) from 
temp_shapes_daily
2012-03-26 13:16:36 LOG:   pid 14860: statement: SELECT 
max(octet_length(hist_file_name::text)) from temp_shapes_daily
2012-03-26 13:16:36 LOG:   pid 14860: DB node id: 0 backend pid: 16005 
statement: SELECT max(octet_length(hist_file_name::text)) from 
temp_shapes_daily
2012-03-26 13:16:37 LOG:   pid 14860: statement:                        
SELECT *
                         FROM temp_shapes_daily
                         WHERE mission_phase_name = 'COMMISSIONING'
                         AND original_product_id LIKE 'nac%'

2012-03-26 13:16:37 LOG:   pid 14860: DB node id: 0 backend pid: 16005 
statement:                       SELECT *
                         FROM temp_shapes_daily
                         WHERE mission_phase_name = 'COMMISSIONING'
                         AND original_product_id LIKE 'nac%'

2012-03-26 13:17:28 LOG:   pid 14860: statement:                        
SELECT *
                         FROM temp_shapes_daily
                         WHERE mission_phase_name = 'COMMISSIONING'
                         AND original_product_id LIKE 'wac%'

2012-03-26 13:17:28 LOG:   pid 14860: DB node id: 0 backend pid: 16005 
statement:                       SELECT *
                         FROM temp_shapes_daily
                         WHERE mission_phase_name = 'COMMISSIONING'
                         AND original_product_id LIKE 'wac%'

2012-03-26 13:17:33 LOG:   pid 14846: connection received: 
host=localhost port=39636
2012-03-26 13:18:08 LOG:   pid 14860: statement:                        
SELECT *
                         FROM temp_shapes_daily
                         WHERE mission_phase_name = 'NOMINAL MISSION'
                         AND original_product_id LIKE 'nac%'

2012-03-26 13:18:08 LOG:   pid 14860: DB node id: 0 backend pid: 16005 
statement:                       SELECT *
                         FROM temp_shapes_daily
                         WHERE mission_phase_name = 'NOMINAL MISSION'
                         AND original_product_id LIKE 'nac%'

2012-03-26 13:22:38 LOG:   pid 14860: statement:                        
SELECT *
                         FROM temp_shapes_daily
                         WHERE mission_phase_name = 'NOMINAL MISSION'
                         AND original_product_id LIKE 'wac%'

2012-03-26 13:22:38 LOG:   pid 14860: DB node id: 0 backend pid: 16005 
statement:                       SELECT *
                         FROM temp_shapes_daily
                         WHERE mission_phase_name = 'NOMINAL MISSION'
                         AND original_product_id LIKE 'wac%'

2012-03-26 13:22:43 ERROR: pid 14860: pool_read2: EOF encountered with 
backend
2012-03-26 13:22:43 LOG:   pid 14860: do_child: exits with status 1 due 
to error

(I have pgpool logs with DEBUG info as well, but they are very large 
(and I am not certain how much useful information they contain).  I can 
certainly provide excerpts from those logs if anyone thinks they are 
useful--perhaps suggest things for which I might look.)

### Excerpts from postgresql log:

LOG:  could not send data to client: Connection reset by peer
STATEMENT:                      SELECT *
                                 FROM temp_shapes_daily
                                 WHERE mission_phase_name = 'NOMINAL 
MISSION'
                                 AND original_product_id LIKE 'wac%'

LOG:  could not send data to client: Broken pipe
STATEMENT:                      SELECT *
                                 FROM temp_shapes_daily
                                 WHERE mission_phase_name = 'NOMINAL 
MISSION'
                                 AND original_product_id LIKE 'wac%'

LOG:  SSL error: bad write retry
LOG:  could not send data to client: Connection reset by peer