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

[Sergey Kim]

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.


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? 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20170406/d0fa7fdf/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool.log
Type: text/x-log
Size: 9064 bytes
Desc: pgpool.log
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20170406/d0fa7fdf/attachment-0001.bin>