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

[Michael Ulitskiy]

I see where there problem is. Sniffing shows it very clearly.
When postgres sends asynchronous notification to pgpool it
doesn't forward it to the client right away, but holds it until client
requests something. Then it sends it along with a query result.
I.e. you have to poll pgpool for notifies performing something like
'select 1' or any other query. 
So it works in your test and it works in my script if I add a query in the
polling cycle. 
It's not very good because instead of polling local socket I have
to issue a query that is actually executed on the backend server.
Any way to have pgpool forward notifies  asynchronously?
Thanks,

Michael

On Tuesday 17 January 2006 08:10 pm, Tatsuo Ishii wrote:
> > > 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
>