[pgpool-general: 2194] Parallel Query Hangs w/ specific rows

[Fantix King]

Hi,

I met a pretty weird issue in our production use of pgpool-II 3.3.1.
Executing SELECT through pgpool on some certain rows of a dist_def-ed table
always hang - you have to kill the "psql".

It keeps reproducing in our production system while I cannot find a similar
report anywhere else. I'm still trying to reproduce this issue individually.

My setup is like this:

 * 1 pgpool in parallel mode
 * 2 PostgreSQL 9.1.9 backends
 * 1 PostgreSQL 9.1.9 system db

The table "my_table" is partitioned with "character_id" key column, where
even number characters are on node #0, and odd number characters are on
node #1. This query hangs:

statement: SELECT my_table.id, my_table.character_id, my_table.my_col_one,
my_table.my_col_two, my_table.quality, my_table.properties FROM my_table
WHERE  my_table.character_id = 100;

I found that:

 * All other hanging queries (the same table) are on node #0 - even numbers
 * If I reduce any column from the SELECT list, the query works
 * Not all of the queries are hanging, e.g. character_id = 102 works fine

Then I diff-ed the pgpool log querying row 100(-) and 102(+):

  1  : ProcessFrontendResponse: kind from frontend Q(51)
  2  : pool_unset_doing_extended_query_message: done
  3 -: statement: SELECT my_table.id, my_table.character_id,
my_table.my_col_one, my_table.my_col_two, my_table.quality,
my_table.properties FROM my_table WHERE  my_table.character_id = 100;
  4  : pool_set_query_in_progress: done
  5  : initSelectStmt: ANALYZE now(0)
  6  : inside build_range_info num= 1 current_select=0
  7  : inside build_range_info dist 0
  8  : inside build_virtual_info dist state=P  my_table
  9  : append_virtual_table select=0,
no=0,col=id,type=bigint,table=my_table,state=P,valid=-1
 10  : append_virtual_table select=0,
no=1,col=character_id,type=bigint,table=my_table,state=P,valid=-1
 11  : append_virtual_table select=0,
no=2,col=my_col_one,type=boolean,table=my_table,state=P,valid=-1
 12  : append_virtual_table select=0,
no=3,col=my_col_two,type=integer,table=my_table,state=P,valid=-1
 13  : append_virtual_table select=0,
no=4,col=quality,type=integer,table=my_table,state=P,valid=-1
 14  : append_virtual_table select=0,
no=5,col=properties,type=bytea,table=my_table,state=P,valid=-1
...
 17  : pool_parallel_query:  0 th FD_SET: 19
 18  : pool_parallel_query:  1 th FD_SET: 21
 19  : pool_parallel_query: num_fds: 22
 20  : read_kind_from_one_backend: read kind from 0 th backend T
 21  : pool_parallel_exec: kind from backend: T
 22  : pool_parallel_exec: dummy from backend: C
 23 +: pool_parallel_query:  1 th FD_SET: 21
 24 +: pool_parallel_query: num_fds: 22
 25  : read_kind_from_one_backend: read kind from 1 th backend T
 26  : pool_parallel_exec: dummy kind from backend: T
 27 -: pool_parallel_exec: kind from backend: D 3
 28 +: pool_parallel_exec: kind from backend: D 4
 29  : pool_parallel_exec: kind from backend: C
 30  : pool_unset_query_in_progress: done
 31  : pool_unset_query_in_progress: done
* 32 -: pool_process_query: discard Z packet from backend 1*
 33 -: detect_error: kind: Z
 34 -: detect_error: kind: Z
 35 -: detect_error: kind: Z
 36  : read_kind_from_backend: kind: Z from 0 th backend
 37  : read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2
 38 +: read_kind_from_backend: kind: Z from 1 th backend
 39 +: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2
 40 +: ProcessBackendResponse: kind from backend: Z
 41 +: pool_read_message_length: slot: 0 length: 5
 42 +: pool_read_message_length: slot: 1 length: 5
 43 +: ReadyForQuery: transaction state:I
 44 +: ReadyForQuery: transaction state:I
 45 +: ProcessBackendResponse: Ready For Query

FYI some of the pgpool.conf:

backend_weight0 = 1
backend_weight1 = 1
load_balance_mode = on
replication_mode = on
parallel_mode = on

Please advice or let me know if I'm missing any info, thank you!


BR,
Fantix
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20131014/035c051e/attachment.html>