[Pgpool-general] SQL sessions die when a node fails

[Tatsuo Ishii]

Yes, it's pretty normal. What behavior are you expecting?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Hi! If I shutdown a postgresql node while I am in a sql session, it will
> stop working. Is that normal?
> 
> I have the next scenario:
>   vapp1a: server with pgpool, listening port 5431
>   vpgsql1a: server with postgresql, backend 0
>   vpgsql1b: server with postgresql, backend 1
> 
> Setups for pgpool:
>     replication_mode = true
>     load_balance_mode = true
>     replicate_select = true
> 
> Pgpool start logs look this way:
>   Jun 25 18:31:49 vapp1a pgpool: 2009-06-25 18:31:41 DEBUG: pid 30820:
> num_backends: 2 num_backends: 2 total_weight: 2.000000
>   Jun 25 18:31:49 vapp1a pgpool: 2009-06-25 18:31:41 DEBUG: pid 30820:
> backend 0 weight: 1073741823.500000
>   Jun 25 18:31:49 vapp1a pgpool: 2009-06-25 18:31:41 DEBUG: pid 30820:
> backend 1 weight: 1073741823.500000
>   [....]
>   Jun 25 18:31:51 app1a pgpool: 2009-06-25 18:31:42 LOG:   pid 30820: pgpool
> successfully started
>   Jun 25 18:31:51 app1a pgpool: 2009-06-25 18:31:42 DEBUG: pid 30861: I am
> PCP 30861
> 
> I log-in to the database through  pgpool and execute some inserts:
>   pgpool at vapp1:~$ psql -h localhost -p 5431 -U auser mybase
>   mybase=> create table test (id integer);
>   mybase=> insert into test (1);
> 
> I see that row on both vpgsql1a and vpgsql1b. I won't exit the psql session
> yet...
> 
> Now I stop backend 1 and get the next pgpool logs:
>   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30860:
> detect_error: kind: E
>   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30860:
> detect_stop_postmaster_error: receive admin shutdown error from a node.
>   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 LOG:   pid 30860:
> notice_backend_error: 1 fail over request from pid 30860
>   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> failover_handler called
>   Jun 25 18:36:58 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> failover_handler: starting to select new master node
>   Jun 25 18:36:59 vapp1a pgpool: 2009-06-25 18:36:58 LOG:   pid 30820:
> starting degeneration. shutdown host vpgsql1b(5432)
>   Jun 25 18:36:59 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> failover_handler: kill 30829
>   [....]
>   Jun 25 18:37:00 vapp1a pgpool: 2009-06-25 18:36:58 LOG:   pid 30820:
> failover_handler: set new master node: 0
>   Jun 25 18:37:00 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30852: child
> receives shutdown request signal 3
>   [....]
>   Jun 25 18:37:04 vapp1a pgpool: 2009-06-25 18:36:58 LOG:   pid 30820:
> failover done. shutdown host vpgsql1b(5432)
>   Jun 25 18:37:04 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> reap_handler called
>   Jun 25 18:37:04 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> reap_handler: call wait3
>   Jun 25 18:37:04 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820: child
> 30829 exits with status 0 by signal 0
>   [....]
>   Jun 25 18:37:06 vapp1a pgpool: 2009-06-25 18:36:58 DEBUG: pid 30820:
> reap_handler: normally exited
> 
> In the psql session I try to insert a new row, getting:
>   mybase=> insert into kk values (2);
>   server closed the connection unexpectedly
>           This probably means the server terminated abnormally
>           before or while processing the request.
>   The connection to the server was lost. Attempting reset: Succeeded.
> 
> Now I exit psql, and log-in again, now successfully inserting a new row:
>   pgpool at vapp1:~$ psql -h localhost -p 5431 -U auser mybase
>   mybase=> create table test (id integer);
>   mybase=> insert into test (2);
>   INSERT 0 1
> 
> Is that normal?
> Thanks in advance,
> J. Carlos Muro