View Issue Details

IDProjectCategoryView StatusLast Update
0000116Pgpool-IIBugpublic2015-04-28 18:05
ReporterwaynewinchAssigned ToMuhammad Usama 
PrioritynormalSeveritymajorReproducibilitysometimes
Status resolvedResolutionfixed 
Platformx86_64OSCentos/Red HatOS Version6.3
Product Version 
Target VersionFixed in Version 
Summary0000116: LISTEN Notifications Not Reliably Delivered Using JDBC4 Demonstrator
DescriptionThe 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 Reproduce1) 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 InformationResearch 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.
TagsNo tags attached.

Activities

Muhammad Usama

2014-11-25 23:08

developer   ~0000496

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

Issue History

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