No, the explains don't use function. <div><br></div><div>This is the explain: </div><div><br></div><div><div><br></div><div><div>EXPLAIN SELECT a.adid AS a__adid, a.md5 AS a__md5, a.site AS a__site, a.published AS a__published, a.imported AS a__imported, a.year AS a__year, a.mileage AS a__mileage, a.price AS a__price, a.make AS a__make, a.model AS a__model, a.location AS a__location, a.title AS a__title, a.description AS a__description, a.specials AS a__specials, a.source_url AS a__source_url, a.image_url AS a__image_url, a.num_images AS a__num_images, a.version AS a__version, a.fuel AS a__fuel, a.colour AS a__colour, a.transmission AS a__transmission, a.body AS a__body, a.category AS a__category </div>
<div>FROM insertion a </div><div>WHERE (a.make = 'fiat' AND a.i_fulltext @@ to_tsquery('italian', 'doblo') AND a.adid NOT IN ('20120423.041004.362964') AND a.i_fulltext @@ to_tsquery('italian', '(metano)') AND a.i_location @@ 'puglia'::tsquery AND a.published >= '2012/06/11') ORDER BY a.published DESC NULLS LAST LIMIT 1000;</div>
<div><br></div><div><br></div><div>Result ---- Server A -------</div><div><br></div><div><br></div><div>Limit (cost=713.46..713.46 rows=1 width=952)</div><div> -> Sort (cost=713.46..713.46 rows=1 width=952)</div><div>
Sort Key: published</div><div> -> Bitmap Heap Scan on insertion a (cost=669.45..713.45 rows=1 width=952)</div><div> Recheck Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND (i_fulltext @@ '''met'''::tsquery) AND (i_location @@ '''puglia'''::tsquery))</div>
<div> Filter: (((adid)::text <> '20120423.041004.362964'::text) AND (published >= '2012-06-11 00:00:00'::timestamp without time zone) AND ((make)::text = 'fiat'::text))</div>
<div> -> BitmapAnd (cost=669.45..669.45 rows=11 width=0)</div><div> -> Bitmap Index Scan on insertion_i_fulltext_idx (cost=0.00..37.84 rows=184 width=0)</div><div> Index Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND (i_fulltext @@ '''met'''::tsquery))</div>
<div> -> Bitmap Index Scan on insertion_i_location_idx (cost=0.00..631.36 rows=59648 width=0)</div><div> Index Cond: (i_location @@ '''puglia'''::tsquery)</div>
<div>(11 rows)</div><div><br></div><div><br></div><div><br></div><div>Result ---- Server B -------</div><div><br></div><div><br></div><div> Limit (cost=678.00..678.00 rows=1 width=957)</div><div> -> Sort (cost=678.00..678.00 rows=1 width=957)</div>
<div> Sort Key: published</div><div> -> Bitmap Heap Scan on insertion a (cost=37.63..677.99 rows=1 width=957)</div><div> Recheck Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND (i_fulltext @@ '''met'''::tsquery))</div>
<div> Filter: (((adid)::text <> '20120423.041004.362964'::text) AND (i_location @@ '''puglia'''::tsquery) AND (published >= '2012-06-11 00:00:00'::timestamp without time zone) AND ((make)::text = 'fiat'::text))</div>
<div> -> Bitmap Index Scan on insertion_i_fulltext_idx (cost=0.00..37.63 rows=163 width=0)</div><div> Index Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND (i_fulltext @@ '''met'''::tsquery))</div>
<div>(8 righe)</div></div></div><div><br></div><div><br></div><div> I will resolve this problem (different result) soon. It's possible execute explain only server A or only server B ? </div><div><br></div><div>Regards</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">
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>
<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, 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 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: 0[D]<br>
>> > 1[C]<br>
>> > 2012-06-13 09:35:11 LOG: pid 15204: do_child: exits with status 1 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>
</div></div></blockquote></div><br></div>