View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000116 | Pgpool-II | Bug | public | 2014-09-26 00:42 | 2015-04-28 18:05 |
| Reporter | waynewinch | Assigned To | Muhammad Usama | ||
| Priority | normal | Severity | major | Reproducibility | sometimes |
| Status | resolved | Resolution | fixed | ||
| Platform | x86_64 | OS | Centos/Red Hat | OS Version | 6.3 |
| Summary | 0000116: LISTEN Notifications Not Reliably Delivered Using JDBC4 Demonstrator | ||||
| Description | The LISTEN/NOTIFY JDBC4 official demonstrator program at http://jdbc.postgresql.org/documentation/93/listennotify.html occasionally fails to signal notification reception when this program is run against a typical streaming replication master/slave (one each) pgpool setup. | ||||
| Steps To Reproduce | 1) Set up a PostgreSQL 9.3 instance and configure with streaming replication to backup node. 2) Set up latest stable pgpool (3.3.4-1 as of this writing) in master slave streaming sub-mode with load balancing. Ensure both backend db nodes are properly configured. 3) Grab the JDBC4 demonstrator program at the URL above. Change connection details in NotificationTest.java source as required. Compile Java program using class path reference to postgresql-9.3-1102.jdbc41.jar or equivalent. 4) Run LISTEN/NOTIFY JDBC4 demonstrator program several times. On some runs, notice that the "Got notification: mymessage" never appears. | ||||
| Additional Information | Research into this issue suggests that when the problem occurs the program is stuck in the line ResultSet rs = stmt.executeQuery("SELECT 1"); waiting for the normal PostgreSQL protocol replies (Parse completion, Bind completion, Row description, Data row, Command completion, Read for query, etc.) that, unfortunately, never arrive. Two things are also ALWAYS evident when the problem occurs: a) The pgpool log entry "pool_process_query: received A packet from backend 0. Don't dicard and read A packet from master" appears. b) Through netstat and Wireshark, it has become apparent that the SELECT 1 query has been issued to the non-master node. A workaround was devised that ALWAYS seems to work in this case: i) Craft a substitute SELECT 1 PL/PGSQL function, say pgpool_master_ping(), in the db cluster which simply returns the integer 1. This will serve as the dummy query issued through pgpool to the master db node in order to fetch the pending notifications list for setting into PGConnection. ii) Modify pgpool.conf to set the new function name in (i) above in the "black_function_list" configuration item. This forces pgpool to direct the query to the master db node. iii) Change NotificationTest.java to perform "SELECT public.pgpool_master_ping()" instead of SELECT 1. Recompile and retest. With this set up, the LISTEN notifications are ALWAYS reported. | ||||
| Tags | No tags attached. | ||||
|
|
Hello Thanks for the report. I am able to reproduce the problem, The issue is pgpool-II expects to receive the NOTIFY message from the node to which it has sent the (SELECT 1) query, and when that node is not the primary PostgreSQL server node, pgpool-II gets stuck on waiting for NOTIFY message from it which never arives since PostgreSQL only sends the NOTIFY from primary node. I have committed the fix in master and 3.4 branches and will backport it to older branches in a few days http://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=328c53b86ec83d3f02f64a22e6821a6cde00b61f Thanks |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2014-09-26 00:42 | waynewinch | New Issue | |
| 2014-11-06 08:14 | t-ishii | Assigned To | => Muhammad Usama |
| 2014-11-06 08:14 | t-ishii | Status | new => assigned |
| 2014-11-25 23:08 | Muhammad Usama | Note Added: 0000496 | |
| 2015-04-28 18:05 | Muhammad Usama | Status | assigned => resolved |
| 2015-04-28 18:05 | Muhammad Usama | Resolution | open => fixed |
