<div dir="ltr">Hi,<div><br></div><div>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".</div>
<div><br></div><div>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.</div><div><br></div><div>My setup is like this:</div>
<div><br></div><div> * 1 pgpool in parallel mode</div><div> * 2 PostgreSQL 9.1.9 backends</div><div> * 1 PostgreSQL 9.1.9 system db</div><div><br></div><div>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:</div>
<div><br></div><div><font face="courier new, monospace">statement: SELECT <a href="http://my_table.id" target="_blank">my_table.id</a>, 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;<br>
</font></div><div><br></div><div>I found that:</div><div><br></div><div> * All other hanging queries (the same table) are on node #0 - even numbers</div><div> * If I reduce any column from the SELECT list, the query works<br>
</div><div><div> * Not all of the queries are hanging, e.g. character_id = 102 works fine</div></div><div><br></div><div>Then I diff-ed the pgpool log querying row 100(-) and 102(+):</div><div><br></div><div><div><font face="courier new, monospace"> 1 : ProcessFrontendResponse: kind from frontend Q(51)</font></div>
<div><font face="courier new, monospace"> 2 : pool_unset_doing_extended_query_message: done</font></div><div><font face="courier new, monospace"> 3 -: statement: SELECT <a href="http://my_table.id" target="_blank">my_table.id</a>, 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;</font></div>
<div><font face="courier new, monospace"> 4 : pool_set_query_in_progress: done</font></div><div><font face="courier new, monospace"> 5 : initSelectStmt: ANALYZE now(0)</font></div><div><font face="courier new, monospace"> 6 : inside build_range_info num= 1 current_select=0</font></div>
<div><font face="courier new, monospace"> 7 : inside build_range_info dist 0</font></div><div><font face="courier new, monospace"> 8 : inside build_virtual_info dist state=P my_table</font></div></div><div><div><font face="courier new, monospace"> 9 : append_virtual_table select=0, no=0,col=id,type=bigint,table=my_table,state=P,valid=-1</font></div>
<div><font face="courier new, monospace"> 10 : append_virtual_table select=0, no=1,col=character_id,type=bigint,table=my_table,state=P,valid=-1</font></div><div><font face="courier new, monospace"> 11 : append_virtual_table select=0, no=2,col=my_col_one,type=boolean,table=my_table,state=P,valid=-1</font></div>
<div><font face="courier new, monospace"> 12 : append_virtual_table select=0, no=3,col=my_col_two,type=integer,table=my_table,state=P,valid=-1</font></div><div><font face="courier new, monospace"> 13 : append_virtual_table select=0, no=4,col=quality,type=integer,table=my_table,state=P,valid=-1</font></div>
<div><font face="courier new, monospace"> 14 : append_virtual_table select=0, no=5,col=properties,type=bytea,table=my_table,state=P,valid=-1</font></div></div><div><font face="courier new, monospace">...</font></div><div>
<div><font face="courier new, monospace"> 17 : pool_parallel_query: 0 th FD_SET: 19</font></div><div><font face="courier new, monospace"> 18 : pool_parallel_query: 1 th FD_SET: 21</font></div><div><font face="courier new, monospace"> 19 : pool_parallel_query: num_fds: 22</font></div>
<div><font face="courier new, monospace"> 20 : read_kind_from_one_backend: read kind from 0 th backend T</font></div><div><font face="courier new, monospace"> 21 : pool_parallel_exec: kind from backend: T</font></div><div>
<font face="courier new, monospace"> 22 : pool_parallel_exec: dummy from backend: C</font></div><div><font face="courier new, monospace"> 23 +: pool_parallel_query: 1 th FD_SET: 21</font></div><div><font face="courier new, monospace"> 24 +: pool_parallel_query: num_fds: 22</font></div>
<div><font face="courier new, monospace"> 25 : read_kind_from_one_backend: read kind from 1 th backend T</font></div><div><font face="courier new, monospace"> 26 : pool_parallel_exec: dummy kind from backend: T</font></div>
<div><font face="courier new, monospace"> 27 -: pool_parallel_exec: kind from backend: D 3</font></div><div><font face="courier new, monospace"> 28 +: pool_parallel_exec: kind from backend: D 4</font></div><div><font face="courier new, monospace"> 29 : pool_parallel_exec: kind from backend: C</font></div>
<div><font face="courier new, monospace"> 30 : pool_unset_query_in_progress: done</font></div><div><font face="courier new, monospace"> 31 : pool_unset_query_in_progress: done</font></div><div><b><font face="courier new, monospace"> 32 -: pool_process_query: discard Z packet from backend 1</font></b></div>
<div><font face="courier new, monospace"> 33 -: detect_error: kind: Z</font></div><div><font face="courier new, monospace"> 34 -: detect_error: kind: Z</font></div><div><font face="courier new, monospace"> 35 -: detect_error: kind: Z</font></div>
<div><font face="courier new, monospace"> 36 : read_kind_from_backend: kind: Z from 0 th backend</font></div><div><font face="courier new, monospace"> 37 : read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2</font></div>
<div><font face="courier new, monospace"> 38 +: read_kind_from_backend: kind: Z from 1 th backend</font></div><div><font face="courier new, monospace"> 39 +: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2</font></div>
<div><font face="courier new, monospace"> 40 +: ProcessBackendResponse: kind from backend: Z</font></div><div><font face="courier new, monospace"> 41 +: pool_read_message_length: slot: 0 length: 5</font></div><div><font face="courier new, monospace"> 42 +: pool_read_message_length: slot: 1 length: 5</font></div>
<div><font face="courier new, monospace"> 43 +: ReadyForQuery: transaction state:I</font></div><div><font face="courier new, monospace"> 44 +: ReadyForQuery: transaction state:I</font></div><div><font face="courier new, monospace"> 45 +: ProcessBackendResponse: Ready For Query</font></div>
</div><div><br></div><div>FYI some of the pgpool.conf:</div><div><br></div><div><div><div><font face="courier new, monospace">backend_weight0 = 1</font></div><div><font face="courier new, monospace">backend_weight1 = 1</font></div>
</div></div><div><div><font face="courier new, monospace">load_balance_mode = on</font></div><div><font face="courier new, monospace">replication_mode = on</font></div></div><div><div><font face="courier new, monospace">parallel_mode = on</font></div>
</div><div><br></div><div>Please advice or let me know if I'm missing any info, thank you!</div><div><br></div><div><br></div><div>BR,</div><div>Fantix</div></div>