<div dir="ltr">Oh sure, thank you very much for helping here!<div><br></div><div>Table definition:</div><div><br></div><div><font face="courier new, monospace">CREATE TABLE my_table (<br></font></div><div><font face="courier new, monospace"> id BIGINT NOT NULL, </font></div>
<div><font face="courier new, monospace"> character_id BIGINT NOT NULL, </font></div><div><font face="courier new, monospace"> </font><span style="font-family:'courier new',monospace;font-size:13px">my_col_one</span><font face="courier new, monospace"> BOOLEAN NOT NULL, </font></div>
<div><font face="courier new, monospace"> </font><span style="font-family:'courier new',monospace;font-size:13px">my_col_two</span><font face="courier new, monospace"> INTEGER NOT NULL, </font></div><div><font face="courier new, monospace"> quality INTEGER NOT NULL, </font></div>
<div><font face="courier new, monospace"> properties BYTEA, </font></div><div><font face="courier new, monospace"> PRIMARY KEY (id)</font></div><div><font face="courier new, monospace">);</font></div><div><br></div>
dist_def for this table:<div><br></div><div><div><font face="courier new, monospace">INSERT INTO pgpool_catalog.dist_def VALUES (</font></div><div><font face="courier new, monospace"> 'my_db',</font></div><div>
<font face="courier new, monospace"> 'public',</font></div>
<div><font face="courier new, monospace"> 'my_table',</font></div><div><font face="courier new, monospace"> 'character_id',</font></div><div><font face="courier new, monospace"> ARRAY['id', 'character_id', '</font><span style="font-family:'courier new',monospace;font-size:13px">my_col_one</span><font face="courier new, monospace">', '</font><span style="font-family:'courier new',monospace;font-size:13px">my_col_two</span><font face="courier new, monospace">', 'quality', 'properties'],</font></div>
<div><font face="courier new, monospace"> ARRAY['bigint', 'bigint', 'boolean', 'integer', 'integer', 'bytea'],</font></div><div><font face="courier new, monospace"> 'pgpool_catalog.dist_def_demo'</font></div>
<div><font face="courier new, monospace">);</font></div><div><div><font face="courier new, monospace">CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_demo(anyelement)</font></div><div><font face="courier new, monospace">RETURNS integer AS $$</font></div>
<div><font face="courier new, monospace"> SELECT CASE WHEN $1 % 2 = 0 THEN 0</font></div><div><font face="courier new, monospace"> ELSE 1</font></div><div><font face="courier new, monospace"> END;</font></div>
<div><font face="courier new, monospace">$$ LANGUAGE sql;</font></div></div><div><br></div><div>There're also some other irrelevant definitions in dist_def and replicate_def, but they have nothing to do with this table at all (no foreign key, no constraints, etc.). Please let me know if you need the full file. :)</div>
</div><div><br></div><div><br></div><div>BR,</div><div>Fantix</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Oct 15, 2013 at 1:33 PM, Yugo Nagata <span dir="ltr"><<a href="mailto:nagata@sraoss.co.jp" target="_blank">nagata@sraoss.co.jp</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
Thanks for your reporting and analyzing. I'll handle the problem. However, it may<br>
take some time for me to resoleve sinse I have other issues now.<br>
<br>
For analysis, could you please tell me the definition of table "my_table",<br>
and contents of dist_def, replicate_def ?<br>
<div><div><br>
On Mon, 14 Oct 2013 20:08:07 +0800<br>
Fantix King <<a href="mailto:fantix.king@gmail.com" target="_blank">fantix.king@gmail.com</a>> wrote:<br>
<br>
> Hi,<br>
><br>
> I met a pretty weird issue in our production use of pgpool-II 3.3.1.<br>
> Executing SELECT through pgpool on some certain rows of a dist_def-ed table<br>
> always hang - you have to kill the "psql".<br>
><br>
> It keeps reproducing in our production system while I cannot find a similar<br>
> report anywhere else. I'm still trying to reproduce this issue individually.<br>
><br>
> My setup is like this:<br>
><br>
> * 1 pgpool in parallel mode<br>
> * 2 PostgreSQL 9.1.9 backends<br>
> * 1 PostgreSQL 9.1.9 system db<br>
><br>
> The table "my_table" is partitioned with "character_id" key column, where<br>
> even number characters are on node #0, and odd number characters are on<br>
> node #1. This query hangs:<br>
><br>
> statement: SELECT <a href="http://my_table.id" target="_blank">my_table.id</a>, my_table.character_id, my_table.my_col_one,<br>
> my_table.my_col_two, my_table.quality, my_table.properties FROM my_table<br>
> WHERE my_table.character_id = 100;<br>
><br>
> I found that:<br>
><br>
> * All other hanging queries (the same table) are on node #0 - even numbers<br>
> * If I reduce any column from the SELECT list, the query works<br>
> * Not all of the queries are hanging, e.g. character_id = 102 works fine<br>
><br>
> Then I diff-ed the pgpool log querying row 100(-) and 102(+):<br>
><br>
> 1 : ProcessFrontendResponse: kind from frontend Q(51)<br>
> 2 : pool_unset_doing_extended_query_message: done<br>
> 3 -: statement: SELECT <a href="http://my_table.id" target="_blank">my_table.id</a>, my_table.character_id,<br>
> my_table.my_col_one, my_table.my_col_two, my_table.quality,<br>
> my_table.properties FROM my_table WHERE my_table.character_id = 100;<br>
> 4 : pool_set_query_in_progress: done<br>
> 5 : initSelectStmt: ANALYZE now(0)<br>
> 6 : inside build_range_info num= 1 current_select=0<br>
> 7 : inside build_range_info dist 0<br>
> 8 : inside build_virtual_info dist state=P my_table<br>
> 9 : append_virtual_table select=0,<br>
> no=0,col=id,type=bigint,table=my_table,state=P,valid=-1<br>
> 10 : append_virtual_table select=0,<br>
> no=1,col=character_id,type=bigint,table=my_table,state=P,valid=-1<br>
> 11 : append_virtual_table select=0,<br>
> no=2,col=my_col_one,type=boolean,table=my_table,state=P,valid=-1<br>
> 12 : append_virtual_table select=0,<br>
> no=3,col=my_col_two,type=integer,table=my_table,state=P,valid=-1<br>
> 13 : append_virtual_table select=0,<br>
> no=4,col=quality,type=integer,table=my_table,state=P,valid=-1<br>
> 14 : append_virtual_table select=0,<br>
> no=5,col=properties,type=bytea,table=my_table,state=P,valid=-1<br>
> ...<br>
> 17 : pool_parallel_query: 0 th FD_SET: 19<br>
> 18 : pool_parallel_query: 1 th FD_SET: 21<br>
> 19 : pool_parallel_query: num_fds: 22<br>
> 20 : read_kind_from_one_backend: read kind from 0 th backend T<br>
> 21 : pool_parallel_exec: kind from backend: T<br>
> 22 : pool_parallel_exec: dummy from backend: C<br>
> 23 +: pool_parallel_query: 1 th FD_SET: 21<br>
> 24 +: pool_parallel_query: num_fds: 22<br>
> 25 : read_kind_from_one_backend: read kind from 1 th backend T<br>
> 26 : pool_parallel_exec: dummy kind from backend: T<br>
> 27 -: pool_parallel_exec: kind from backend: D 3<br>
> 28 +: pool_parallel_exec: kind from backend: D 4<br>
> 29 : pool_parallel_exec: kind from backend: C<br>
> 30 : pool_unset_query_in_progress: done<br>
> 31 : pool_unset_query_in_progress: done<br>
</div></div>> * 32 -: pool_process_query: discard Z packet from backend 1*<br>
<div><div>> 33 -: detect_error: kind: Z<br>
> 34 -: detect_error: kind: Z<br>
> 35 -: detect_error: kind: Z<br>
> 36 : read_kind_from_backend: kind: Z from 0 th backend<br>
> 37 : read_kind_from_backend: read kind from 0 th backend Z NUM_BACKENDS: 2<br>
> 38 +: read_kind_from_backend: kind: Z from 1 th backend<br>
> 39 +: read_kind_from_backend: read kind from 1 th backend Z NUM_BACKENDS: 2<br>
> 40 +: ProcessBackendResponse: kind from backend: Z<br>
> 41 +: pool_read_message_length: slot: 0 length: 5<br>
> 42 +: pool_read_message_length: slot: 1 length: 5<br>
> 43 +: ReadyForQuery: transaction state:I<br>
> 44 +: ReadyForQuery: transaction state:I<br>
> 45 +: ProcessBackendResponse: Ready For Query<br>
><br>
> FYI some of the pgpool.conf:<br>
><br>
> backend_weight0 = 1<br>
> backend_weight1 = 1<br>
> load_balance_mode = on<br>
> replication_mode = on<br>
> parallel_mode = on<br>
><br>
> Please advice or let me know if I'm missing any info, thank you!<br>
><br>
><br>
> BR,<br>
> Fantix<br>
<br>
<br>
</div></div><span><font color="#888888">--<br>
Yugo Nagata <<a href="mailto:nagata@sraoss.co.jp" target="_blank">nagata@sraoss.co.jp</a>><br>
</font></span></blockquote></div><br></div></div>