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

[Tatsuo Ishii]

> > 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 :)

I'm not familiar with perl and I don't understand your example. So I
did my own testings using psql and it seems LISTEN/NOTIFY works fine.

1. create table test_table(i int);

2. 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';

3. CREATE TRIGGER trig_test_table BEFORE INSERT OR UPDATE ON public.test_table
   FOR EACH ROW EXECUTE PROCEDURE trig_test_table();

4. start psql and connect to pgpool (psql A)

5. start another psql and connect to pgpool (psql B)

6. do "LISTEN blah;" on A

7. do "INSERT INTO test_table VALUES(1);" on B

8. do "SELECT 1" on A and got "Asynchronous notification "blah"
   received from server process with PID 20046."

I see no problem here.
--
Tatsuo Ishii
SRA OSS, Inc. Japan