[pgpool-hackers: 2245] Re: [pgpool-committers: 3836] pgpool: Branch refs/heads/bug271 was created

[Tatsuo Ishii]

Sergey,

> Tatsuo,
> 
> I'm struggling to reproduce the problem, but can you please take a look at the attached pgpool log.
> The log clearly says that PARSE message and BIND (B) messages were sent to the backend 0.

Right.

> 1) The request was identified in Postgres and strace says it waits 'epoll_wait(3':
> 
> [root at OAA-a01149b7a559 ~]# sudo -i -u postgres psql --port=15432
> psql (9.6.2)
> Type "help" for help.
> 
> postgres=# select * from pg_stat_activity where pid = 29108;
>  datid | datname |  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change
> | wait_event_type | wait_event | state  | backend_xid | backend_xmin |                                                                              query
> -------+---------+-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+------------------------------
> +-----------------+------------+--------+-------------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  16389 | oss     | 29108 |    16390 | oss     |                  | 127.0.0.1   |                 |       37570 | 2017-04-06 11:33:37.428804+03 | 2017-04-06 11:34:03.018548+03 | 2017-04-06 11:34:03.018738+03 | 2017-04-06 11:34:03.01874+03
> |                 |            | active |             |      1835366 |  SELECT 1 FROM ui_locked_users WHERE  LOWER(username) = LOWER( $1 ) AND  (device_tag IS NULL OR device_tag =  $2 ) AND  (unlock_on IS NULL OR unlock_on >=  $3 )
> (1 row)
> 
> 2) The DB application stuck on the execution of SELECT query.  It was blocked until the corresponding pgpool process was killed.
> 
> 3) Pgpool log is attached.
> 
> 
> Is it possible that pgpool didn't forward EXECUTE command to postgres?

Yes, if Pgpool-II is waiting for something from either frontend or
backend.

> The problem is reproduced reliably if balancing is enabled, but I'm failing to reproduce it in a standalone test so far.
> 
> 
> Sergey.
> ________________________________
> From: Tatsuo Ishii <ishii at sraoss.co.jp>
> Sent: Thursday, April 6, 2017 3:48 AM
> To: Sergey Kim
> Cc: Boris Voskanov; pgpool-hackers at pgpool.net
> Subject: Re: [pgpool-hackers: 2227] Re: [pgpool-committers: 3836] pgpool: Branch refs/heads/bug271 was created
> 
>> Tatsuo,
>>
>>
>> the symptoms of the "hanged" pgpool are following:
>> 1) DB applications fail with 2 types of errors:
>>   - failed to prepare statement (PARSE)
>>   - failed to extract ResultSet - failed selects
>>
>> 2) The problem is reproduced within a single JDBC connection, series of transactions are passed into it before the problem appears.
>>
>> I'm deeply analysed pgpool logs from the series of failures and revealed a sample case (i.e. pgpool process). It's logs are attached.
>> I'm not sure I'm right but it seems the balancing logic is broken it its sequence of messages - how the are processed. I'am triggered by the fact it was sending PARSE messages to the both backends.
> 
> Please provide a reproducable test case. I do not see any error
> message in the log.
> 
>> P/S: I was trying to attach the debugger to pgpool but I'm failed to detect a piece of code which I should focus at. Also it is a little bit hard to detect a pgpool process which should be debugged.
> 
> Use ps to find a pgpool process which shows 'idle' state. Or you could
> set num_init_children to 1. In this case there's only one pgpool child
> process.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> 
>> Sergey.
>> ________________________________
>> From: Sergey Kim
>> Sent: Wednesday, April 5, 2017 1:48 PM
>> To: Tatsuo Ishii
>> Cc: Boris Voskanov; pgpool-hackers at pgpool.net; Sergey Onuchin
>> Subject: Re: [pgpool-hackers: 2227] Re: [pgpool-committers: 3836] pgpool: Branch refs/heads/bug271 was created
>>
>>
>> ... also it happens only when balancing is enabled in pgpool.
>>
>>
>> ________________________________
>> From: Sergey Kim
>> Sent: Wednesday, April 5, 2017 1:34:45 PM
>> To: Tatsuo Ishii
>> Cc: Boris Voskanov; pgpool-hackers at pgpool.net; Sergey Onuchin
>> Subject: Re: [pgpool-hackers: 2227] Re: [pgpool-committers: 3836] pgpool: Branch refs/heads/bug271 was created
>>
>>
>> Tatsuo,
>>
>>
>> the 2nd day of tests revealed a problem in pgpool which I trying to reproduce in a standalone test.
>>
>>
>> Sometimes PARSE messages hang in pgpool and DB clients hang due to this reason. I'm just wanted to notify you on the problem. I'm fighting to reproduce it artificially.
>>
>>
>> Sergey.
>>
>>
>> ...the primary message was cut