[pgpool-hackers: 641] Re: [pgpool-II 0000116]: LISTEN Notifications Not Reliably Delivered Using JDBC4 Demonstrator

[Tatsuo Ishii]

Usama,

Can you please take care of this?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

From: pgpool Bug Tracker <bugtracker at pgpool.net>
Subject: [pgpool-II 0000116]: LISTEN Notifications Not Reliably Delivered Using JDBC4 Demonstrator
Date: Fri, 26 Sep 2014 00:42:21 +0900
Message-ID: <f060790d97a5480a217ddad3ac0df15d at www.pgpool.net>

> 
> The following issue has been SUBMITTED. 
> ====================================================================== 
> http://www.pgpool.net/mantisbt/view.php?id=116 
> ====================================================================== 
> Reported By:                waynewinch
> Assigned To:                
> ====================================================================== 
> Project:                    pgpool-II
> Issue ID:                   116
> Category:                   Bug
> Reproducibility:            sometimes
> Severity:                   major
> Priority:                   normal
> Status:                     new
> ====================================================================== 
> Date Submitted:             2014-09-26 00:42 JST
> Last Modified:              2014-09-26 00:42 JST
> ====================================================================== 
> Summary:                    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.
> ====================================================================== 
> 
> Issue History 
> Date Modified    Username       Field                    Change               
> ====================================================================== 
> 2014-09-26 00:42 waynewinch     New Issue                                    
> ======================================================================
>