I have add to black_function_list = 'nextval,setval,to_tsquery,to_tsvector'<div><br></div><div>but there is an error again. (ERROR: kind mismatch among backends)</div><div><br></div><div><br></div><div><br></div><div>
<br><div class="gmail_quote">2012/6/13 Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Don't you use to_tsquery()?<br>
<br>
But you have:<br>
<div class="im">>> >> > white_function_list = ''<br>
>> >> > black_function_list = 'nextval,setval'<br>
<br>
</div>So the function should not prevent load balance the EXPLAIN.<br>
I will look into this...<br>
<div class="HOEnZb"><div class="h5">--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> No, the explains don't use function.<br>
><br>
> This is the explain:<br>
><br>
><br>
> EXPLAIN SELECT a.adid AS a__adid, a.md5 AS a__md5, a.site AS a__site,<br>
> a.published AS a__published, a.imported AS a__imported, a.year AS a__year,<br>
> a.mileage AS a__mileage, a.price AS a__price, a.make AS a__make, a.model AS<br>
> a__model, a.location AS a__location, a.title AS a__title, a.description AS<br>
> a__description, a.specials AS a__specials, a.source_url AS a__source_url,<br>
> a.image_url AS a__image_url, a.num_images AS a__num_images, a.version AS<br>
> a__version, a.fuel AS a__fuel, a.colour AS a__colour, a.transmission AS<br>
> a__transmission, a.body AS a__body, a.category AS a__category<br>
> FROM insertion a<br>
> WHERE (a.make = 'fiat' AND a.i_fulltext @@ to_tsquery('italian', 'doblo')<br>
> AND a.adid NOT IN ('20120423.041004.362964') AND a.i_fulltext @@<br>
> to_tsquery('italian', '(metano)') AND a.i_location @@ 'puglia'::tsquery AND<br>
> a.published >= '2012/06/11') ORDER BY a.published DESC NULLS LAST LIMIT<br>
> 1000;<br>
><br>
><br>
> Result ---- Server A -------<br>
><br>
><br>
> Limit (cost=<a href="tel:713.46..713.46" value="+17134671346">713.46..713.46</a> rows=1 width=952)<br>
> -> Sort (cost=<a href="tel:713.46..713.46" value="+17134671346">713.46..713.46</a> rows=1 width=952)<br>
> Sort Key: published<br>
> -> Bitmap Heap Scan on insertion a (cost=<a href="tel:669.45..713.45" value="+16694571345">669.45..713.45</a> rows=1<br>
> width=952)<br>
> Recheck Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND<br>
> (i_fulltext @@ '''met'''::tsquery) AND (i_location @@<br>
> '''puglia'''::tsquery))<br>
> Filter: (((adid)::text <> '20120423.041004.362964'::text)<br>
> AND (published >= '2012-06-11 00:00:00'::timestamp without time zone) AND<br>
> ((make)::text = 'fiat'::text))<br>
> -> BitmapAnd (cost=<a href="tel:669.45..669.45" value="+16694566945">669.45..669.45</a> rows=11 width=0)<br>
> -> Bitmap Index Scan on insertion_i_fulltext_idx<br>
> (cost=0.00..37.84 rows=184 width=0)<br>
> Index Cond: ((i_fulltext @@ '''dobl'''::tsquery)<br>
> AND (i_fulltext @@ '''met'''::tsquery))<br>
> -> Bitmap Index Scan on insertion_i_location_idx<br>
> (cost=0.00..631.36 rows=59648 width=0)<br>
> Index Cond: (i_location @@ '''puglia'''::tsquery)<br>
> (11 rows)<br>
><br>
><br>
><br>
> Result ---- Server B -------<br>
><br>
><br>
> Limit (cost=678.00..678.00 rows=1 width=957)<br>
> -> Sort (cost=678.00..678.00 rows=1 width=957)<br>
> Sort Key: published<br>
> -> Bitmap Heap Scan on insertion a (cost=37.63..677.99 rows=1<br>
> width=957)<br>
> Recheck Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND<br>
> (i_fulltext @@ '''met'''::tsquery))<br>
> Filter: (((adid)::text <> '20120423.041004.362964'::text)<br>
> AND (i_location @@ '''puglia'''::tsquery) AND (published >= '2012-06-11<br>
> 00:00:00'::timestamp without time zone) AND ((make)::text = 'fiat'::text))<br>
> -> Bitmap Index Scan on insertion_i_fulltext_idx<br>
> (cost=0.00..37.63 rows=163 width=0)<br>
> Index Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND<br>
> (i_fulltext @@ '''met'''::tsquery))<br>
> (8 righe)<br>
><br>
><br>
> I will resolve this problem (different result) soon. It's possible execute<br>
> explain only server A or only server B ?<br>
><br>
> Regards<br>
><br>
><br>
> 2012/6/13 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
><br>
>> Do those failed "complex" EXPLAINs contain funcation calls, especially<br>
>> nextval or setval? If so, I think it's the case where pgpool has a<br>
>> logical error I have just found. Pgpool first checks if the query is<br>
>> EXPLAIN (and not EXPLAIN ANALYZE). If so, it's ok to load<br>
>> balance. Problem is the next step. Pgpool looks into the EXPLAIN and<br>
>> if it finds a function call, it sends to all PostgreSQL nodes because<br>
>> the function might modify database. Of course this is nonsense, since<br>
>> plain EXPLAIN does nothing to database...<br>
>> --<br>
>> Tatsuo Ishii<br>
>> SRA OSS, Inc. Japan<br>
>> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>><br>
>> > No, a few Explain cause errors.<br>
>> > The trivial explain is ok. The complex explain causes this error :(<br>
>> > In fact, as I have seen, even if the table has the same row/index, the<br>
>> > explain gives differen results. I will resolve this soon.<br>
>> > I want that the explain gets only done on the master node. Only one node.<br>
>> > Does the configuration “replicate_explain = off” exist ???<br>
>> ><br>
>> > Thanks for your answer.<br>
>> ><br>
>> > Nicolas<br>
>> ><br>
>> > 2012/6/13 Tatsuo Ishii <<a href="mailto:ishii@postgresql.org">ishii@postgresql.org</a>><br>
>> ><br>
>> >> Even trivial explain like "EXPLAIN SELECT 1" causes errors?<br>
>> >> --<br>
>> >> Tatsuo Ishii<br>
>> >> SRA OSS, Inc. Japan<br>
>> >> English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
>> >> Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
>> >><br>
>> >> > Hi,<br>
>> >> > I would like to know if I can exclude the command EXPLAIN from the<br>
>> >> pgpool.<br>
>> >> > It generates a lot of errors:<br>
>> >> ><br>
>> >> > 2012-06-13 09:35:11 ERROR: pid 15204: read_kind_from_backend: 1 th<br>
>> kind C<br>
>> >> > does not match with master or majority connection kind D<br>
>> >> > 2012-06-13 09:35:11 ERROR: pid 15204: kind mismatch among backends.<br>
>> >> > Possible last query was: "EXPLAIN SELECT ...... " kind details are:<br>
>> 0[D]<br>
>> >> > 1[C]<br>
>> >> > 2012-06-13 09:35:11 LOG: pid 15204: do_child: exits with status 1<br>
>> due<br>
>> >> to<br>
>> >> > error<br>
>> >> ><br>
>> >> > pool-II-3.1.3<br>
>> >> > PostgreSQL 9.1.2<br>
>> >> > both Server same Hardware.<br>
>> >> ><br>
>> >> > Pgpool configuration:<br>
>> >> ><br>
>> >> > listen_addresses = '*'<br>
>> >> > port = 5432<br>
>> >> > socket_dir = '/var/run/postgresql'<br>
>> >> > pcp_port = 9898<br>
>> >> > pcp_socket_dir = '/tmp'<br>
>> >> > backend_hostname0 = 'localhost'<br>
>> >> > backend_port0 = 5434<br>
>> >> > backend_weight0 = 1<br>
>> >> > backend_data_directory0 = '/var/lib/postgresql/9.1/main'<br>
>> >> > backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
>> >> > backend_hostname1 = '172.16.0.1'<br>
>> >> > backend_port1 = 5434 #doveva essere a 5434<br>
>> >> > backend_weight1 = 1<br>
>> >> > backend_data_directory1 = '/var/lib/postgresql/9.1/main'<br>
>> >> > backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
>> >> > enable_pool_hba = off<br>
>> >> > authentication_timeout = 60<br>
>> >> > # - SSL Connections -<br>
>> >> > num_init_children = 32<br>
>> >> > max_pool = 10<br>
>> >> > child_life_time = 300<br>
>> >> > child_max_connections = 0<br>
>> >> > connection_life_time = 0<br>
>> >> > client_idle_limit = 0<br>
>> >> > log_destination = 'stderr'<br>
>> >> > print_timestamp = on<br>
>> >> > log_connections = off<br>
>> >> > log_hostname = on<br>
>> >> > log_statement = off<br>
>> >> > log_per_node_statement = off<br>
>> >> > log_standby_delay = 'none'<br>
>> >> > syslog_facility = 'LOCAL0'<br>
>> >> > syslog_ident = 'pgpool'<br>
>> >> > debug_level = 0<br>
>> >> > pid_file_name = '/var/run/pgpool/pgpool.pid'<br>
>> >> > logdir = '/tmp'<br>
>> >> > connection_cache = on<br>
>> >> > reset_query_list = 'ABORT; DISCARD ALL'<br>
>> >> > replication_mode = on<br>
>> >> > replicate_select = off<br>
>> >> > insert_lock = on<br>
>> >> > lobj_lock_table = ''<br>
>> >> > replication_stop_on_mismatch = off<br>
>> >> > failover_if_affected_tuples_mismatch = off<br>
>> >> > load_balance_mode = off<br>
>> >> > ignore_leading_white_space = on<br>
>> >> > white_function_list = ''<br>
>> >> > black_function_list = 'nextval,setval'<br>
>> >> > master_slave_mode = off<br>
>> >> > sr_check_period = 0<br>
>> >> > sr_check_user = 'nobody'<br>
>> >> > sr_check_password = ''<br>
>> >> > delay_threshold = 0<br>
>> >> > follow_master_command = ''<br>
>> >> > parallel_mode = off<br>
>> >> > enable_query_cache = off<br>
>> >> > pgpool2_hostname = 'localhost'<br>
>> >> > system_db_hostname = 'localhost'<br>
>> >> > system_db_port = 5434<br>
>> >> > system_db_dbname = 'pgpool'<br>
>> >> > system_db_schema = 'pgpool_catalog'<br>
>> >> > system_db_user = 'pgpool'<br>
>> >> > system_db_password = ''<br>
>> >> > health_check_period = 0<br>
>> >> > health_check_timeout = 20<br>
>> >> > health_check_user = 'nobody'<br>
>> >> > health_check_password = ''<br>
>> >> ><br>
>> >> > ------------------------------------<br>
>> >> ><br>
>> >> > The answers about these questions are welcome.<br>
>> >> ><br>
>> >> > Best Regards<br>
>> >> ><br>
>> >> > Nicolas<br>
>> >><br>
>><br>
</div></div></blockquote></div><br></div>