View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000045 | Pgpool-II | Bug | public | 2012-12-12 00:09 | 2013-01-16 14:01 |
| Reporter | rpashin | Assigned To | t-ishii | ||
| Priority | high | Severity | major | Reproducibility | always |
| Status | resolved | Resolution | open | ||
| Platform | x86 | OS | Linux | OS Version | Debian 6.0 |
| Summary | 0000045: LISTEN/NOTIFY doesn' t work if cluster contains more then 1 node in streaming replication mode | ||||
| Description | There are two nodes in pgpool-II cluster configured in streaming replication mode. When I open one psql console on one box and do it from one console: postgres=# LISTEN TEST1; LISTEN postgres=# NOTIFY TEST1; NOTIFY Asynchronous notification "test1" received from server process with PID 8812. I'm getting notification response successfully. When I'm openinig two different psql sessions from one box, or from different boxes (it doesn't matter) and trying to do the same but from different consoles, "listening" console hangs when I'm trying to receive notification response. | ||||
| Steps To Reproduce | Add two nodes in cluster. Configure streaming replication between them. On host1: host1$ psql postgres postgres=# LISTEN TEST1; LISTEN postgres=# On host2: host2$ psql postgres postgres=# NOTIFY TEST1; NOTIFY postgres=# then on host1: postgres=# LISTEN TEST1; ... that's it. It hangs forever after this command. | ||||
| Additional Information | When both nodes are up and with running replication, if I disconnect first node (id=0), secondary node become primary node and if it's the only one alive node in cluster => LISTEN/NOTIFY works as expected. Until I return first node (id=0) back in cluster. When first node is back - listening console is about to hang again. | ||||
| Tags | No tags attached. | ||||
|
|
Sorry, forgot to say that it's last recent 3.2.1 version. |
|
|
Confirmed the problem. Thinking about how to fix it... |
|
|
Ok, I think I fixed the problem. Could you please try attached patches? |
|
|
|
|
|
Tatsuo, thank you very much! After patching LISTEN/NOTIFY works in our staging environment. In the beginning of next week we'll put it into production. I'll let you know about the result. |
|
|
Looks like something is still not working properly. Probably I was mistaken somewhere in my experiments in staging environment when I was saying that it works. After patch, second (listening) psql console is not hanging up after receiving notification. It do continue functioning, but there is not even sign of received notification. I.e. instead of getting this: postgres=# LISTEN TEST1; LISTEN Asynchronous notification "test1" received from server process with PID 28610. postgres=# I see only this: postgres=# LISTEN TEST1; LISTEN postgres=# And there are two more things I wanted to notice: 1. I have 2 sql servers in cluster UP and running and master is second one, i.e. primary server = 1, slave = 0 When I'm sending notification from one psql console to another, I see in logs this: Dec 26 07:24:18 ast-pg-test-1 pgpool[2857]: statement: NOTIFY TEST1; Dec 26 07:24:18 ast-pg-test-1 pgpool[2857]: DB node id: 1 backend pid: 28838 statement: NOTIFY TEST1; Dec 26 07:24:18 ast-pg-test-1 pgpool[2856]: pool_process_query: discard A packet from backend 1 Dec 26 07:24:20 ast-pg-test-1 pgpool[2856]: statement: LISTEN TEST1; Dec 26 07:24:20 ast-pg-test-1 pgpool[2856]: DB node id: 1 backend pid: 28836 statement: LISTEN TEST1; Looks like sql is sending A packet, but pgpool is discarding it because it consider sending node as "not master" while it is really. 2. Conditions the same. 2 SQL nodes Up and running. Mester id=1, slave=0. I connect 2 psql sessions to pgpool. Since I use streaming replication I suppose that requestst from both psql consoles will be forwarded to second SQL server (id=1) which is master in replication. In this case, if I disconnect first node (id=0) I should not even notice this in console. However if I disconnect first node from cluster via web-interface when consoles are connected, I see message that 'connection to the server was lost': postgres=# LISTEN TEST1; LISTEN postgres=# LISTEN TEST1; LISTEN postgres=# == Here I disconnect first node == postgres=# LISTEN TEST1; The connection to the server was lost. Attempting reset: Succeeded. postgres=# LISTEN TEST1; LISTEN It seems to me a bit strange, but probably I just misunderstanding something. Please tell me if I can provide any other information to help troubleshooting this. |
|
|
> After patch, second (listening) psql console is not hanging up after receiving notification. It do continue functioning, but there is not even sign of received notification. I confirmed this part happens only when primary is 1 and standby is 0. It seems there are some places where pgpool assumes that node 0 is primary. Will fix. |
|
|
Ok, attached (listen_notify-v2.patch) is a revised patch. Please try it. |
|
|
|
|
|
Sorry for delay, we were having NY holidays. I checked listen_notify-v2.patch in staging environment and looks like it works as expected after patch. I'll do a couple of additional tests and will reply shortly to confirm that everything works. |
|
|
Tatsuo, looks like last recent patch (listen_notify-v2.patch) completely resolved this issue. Bug can be considered as resolved with this patch. Thank you! |
|
|
Thanks! I have committed the patch to master and V3.2 stable tree. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2012-12-12 00:09 | rpashin | New Issue | |
| 2012-12-12 02:34 | rpashin | Note Added: 0000178 | |
| 2012-12-12 11:49 | t-ishii | Note Added: 0000179 | |
| 2012-12-12 11:49 | t-ishii | Assigned To | => t-ishii |
| 2012-12-12 11:49 | t-ishii | Status | new => assigned |
| 2012-12-13 16:40 | t-ishii | Note Added: 0000185 | |
| 2012-12-13 16:41 | t-ishii | File Added: listen-notify.patch | |
| 2012-12-14 22:16 | rpashin | Note Added: 0000186 | |
| 2012-12-27 01:03 | rpashin | Note Added: 0000207 | |
| 2012-12-27 12:23 | t-ishii | Note Added: 0000208 | |
| 2012-12-30 21:00 | t-ishii | Note Added: 0000209 | |
| 2012-12-30 21:02 | t-ishii | File Added: listen_notify-v2.patch | |
| 2013-01-08 09:46 | t-ishii | Status | assigned => feedback |
| 2013-01-11 14:05 | rpashin | Note Added: 0000211 | |
| 2013-01-11 14:05 | rpashin | Status | feedback => assigned |
| 2013-01-11 14:06 | rpashin | Note Edited: 0000211 | |
| 2013-01-15 16:41 | rpashin | Note Added: 0000212 | |
| 2013-01-16 13:25 | t-ishii | Note Added: 0000214 | |
| 2013-01-16 13:25 | t-ishii | Status | assigned => resolved |
| 2013-01-16 14:01 | t-ishii | Changeset attached | => pgpool2 master dc5b7ccc |
