[Pgpool-general] Connection dropping unexpectedly

[Tatsuo Ishii]

> Hello!
> 
> I'm using pgpool-II-2.1 (had 2.0.1 from around february, directly from
> cvs), and I'm getting some strange errors. Apparently, pgpool is
> dropping or terminating the connection without a reason.
> I have 2 backends, replicate is true, and load balance is also true.
> It works fine, but sometimes it drops the connection and I get an
> error at the page. I'm using PHP to connect to pgpool.
> 
> Error I get in PHP is when it tries to run pg_escape_string(), which
> expects a string as a parameter to add quotes. However, an object is
> passed instead. A dump of the object gives me this:
> 
> O:8:"DB_Error":8:{s:20:"error_message_prefix";s:0:"";s:4:"mode";i:1;s:5:"level";i:1024;s:4:"code";i:-1;s:7:"message";s:23:"DB
> Error: unknown error";s:8:"userinfo";s:178:"SELECT
> NEXTVAL('log_id_seq') [nativecode=server closed the connection
> unexpectedly
>         This probably means the server terminated abnormally
>         before or while processing the
> request.]";s:9:"backtrac............................
> 
> This message is directly serialized() from php. It seems when it tries
> to run that select, the connection is already severed, and it
> generates that error. I don't know what runs just before that query,
> as that would require to set debug mode, and it fills the hard disk
> really really fast (I can get the debug if needed, though).

The strange object comes from an PostgreSQL error message indicating
the connection to PostgreSQL has been closed. This could happen
because you are probably using persistent connection in PHP. The
persistent connection is killed by client_idle_limit. I suggest two
things:

1) don't use persistent connection in PHP

2) check if connection to PostgreSQL (actually pgpoo) is active before
   using pg_escape_string in your PHP code
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Using just a -n, I get this output:
> 
> 2008-08-28 17:15:33 LOG:   pid 2337: pool_process_query: child
> connection forced to terminate due to client_idle_limit(70) reached
> 2008-08-28 17:15:58 LOG:   pid 2274: pool_process_query: child
> connection forced to terminate due to client_idle_limit(70) reached
> 2008-08-28 17:16:45 LOG:   pid 2401: pool_process_query: child
> connection forced to terminate due to client_idle_limit(70) reached
> 2008-08-28 17:16:50 LOG:   pid 2400: pool_process_query: child
> connection forced to terminate due to client_idle_limit(70) reached
> 2008-08-28 17:17:08 LOG:   pid 2301: ProcessFrontendResponse: failed
> to read kind from frontend. frontend abnormally exited
> 2008-08-28 17:37:32 LOG:   pid 3445: pool_process_query: child
> connection forced to terminate due to client_idle_limit(70) reached
> 2008-08-28 17:37:32 LOG:   pid 3451: pool_process_query: child
> connection forced to terminate due to client_idle_limit(70) reached
> 2008-08-28 17:37:32 LOG:   pid 3429: pool_process_query: child
> connection forced to terminate due to client_idle_limit(70) reached
> 2008-08-28 17:38:35 ERROR: pid 3400: pool_flush_it: write failed
> (Connection reset by peer) offset: 0 wlen: 461
> 2008-08-28 17:39:01 LOG:   pid 3513: pool_process_query: child
> connection forced to terminate due to client_idle_limit(70) reached
> 
> The 70 second idle limit is normal, I set it up on pgpool to avoid
> processes hanging the connection forever. I had an issue with this in
> the past. However, there are those errors (connection reset by peer),
> and also the abnormal exit from the frontend.
> What else should I need to send to get it analyzed properly? Running
> pgpool with -d got me a half gig file about half an hour later. I'd
> also find it hard to find anything inside a big file like this, but if
> you can give me directions on where to find what's needed, I'll be
> happy to help.
> 
> Thanks!
> 
> -- 
> Bruno Lustosa <bruno at lustosa.net>
> http://www.lustosa.net/
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general