[Pgpool-general] is there a way out of degeneration mode

[Michael Ulitskiy]

On Tuesday 17 January 2006 08:19 am, Tatsuo Ishii wrote:
> > If pgpool works in degeneration mode (master/slave configuration, no replication and either master or slave is down)
> > is there a way to let it know that the problem was fixed and it can
> > start using disabled node without restarting the whole thing?
> 
> Currently no.

Thanks for the reply. I guess this is a very desired functionality. 
Please consider it a feature request.
 
> > On a separate note, would LISTEN/NOTIFY work through pgpool or I have to be
> > directly connected to postgres for that?
> > Thanks a lot,
> 
> As far as I know LISTEN/NOTIFY should work fine with pgpool.

Then I'd like to report that it doesn't. At least for me.
Below is a very simple trigger on test table and a simple perl script to listen to notifies:
CREATE OR REPLACE FUNCTION trig_test_table() RETURNS trigger AS '
  BEGIN
    raise notice ''trigger: %, operator: %'',TG_NAME,TG_OP;
    notify blah;
    return NEW;
  END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER trig_test_table BEFORE INSERT OR UPDATE ON public.test_table
         FOR EACH ROW EXECUTE PROCEDURE trig_test_table();


#!/usr/bin/perl -w

use strict;
use DBI;
use DBD::Pg;

my $dbstr="dbi:Pg:dbname=users;host=localhost;port=5432";

my $dbh;
$dbh=DBI->connect($dbstr,  "pgsql", "12345")
   or do {
        logg("Cannot connect to local database: ".$dbh->errstr());
        exit(1);
   };

$dbh->do("LISTEN blah");

while(1) {
  my @res=$dbh->func('pg_notifies');
  if ($res[0]) {
     print "notification $res[0][0] received\n";
  }
}

It works fine if i connect directly to postgres. If I connect to pgpool,
the perl script won't see notifies. I can see in the postgres log that "LISTEN blah" is 
issued correctly on the master server and there's no errors in the logs, but
notifies don't reach the script.
I need to use LISTEN/NOTIFY in my application and if possible I'd prefer to do it through
pgpool. Otherwise I'd have determine which server is currently master and do health checks etc.
in the application, which of course doable, but seems kind of overhead.
So if this is supposed to work through pgpool, then please consider it a bug report :)

Thanks,
Michael

> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>