[pgpool-general: 631] Re: exclude EXPLAIN from the pgpool.
Tatsuo Ishii
ishii at postgresql.org
Wed Jun 13 23:13:14 JST 2012
Don't you use to_tsquery()?
But you have:
>> >> > white_function_list = ''
>> >> > black_function_list = 'nextval,setval'
So the function should not prevent load balance the EXPLAIN.
I will look into this...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> No, the explains don't use function.
>
> This is the explain:
>
>
> 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
> FROM insertion a
> 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;
>
>
> Result ---- Server A -------
>
>
> Limit (cost=713.46..713.46 rows=1 width=952)
> -> Sort (cost=713.46..713.46 rows=1 width=952)
> Sort Key: published
> -> Bitmap Heap Scan on insertion a (cost=669.45..713.45 rows=1
> width=952)
> Recheck Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND
> (i_fulltext @@ '''met'''::tsquery) AND (i_location @@
> '''puglia'''::tsquery))
> Filter: (((adid)::text <> '20120423.041004.362964'::text)
> AND (published >= '2012-06-11 00:00:00'::timestamp without time zone) AND
> ((make)::text = 'fiat'::text))
> -> BitmapAnd (cost=669.45..669.45 rows=11 width=0)
> -> Bitmap Index Scan on insertion_i_fulltext_idx
> (cost=0.00..37.84 rows=184 width=0)
> Index Cond: ((i_fulltext @@ '''dobl'''::tsquery)
> AND (i_fulltext @@ '''met'''::tsquery))
> -> Bitmap Index Scan on insertion_i_location_idx
> (cost=0.00..631.36 rows=59648 width=0)
> Index Cond: (i_location @@ '''puglia'''::tsquery)
> (11 rows)
>
>
>
> Result ---- Server B -------
>
>
> Limit (cost=678.00..678.00 rows=1 width=957)
> -> Sort (cost=678.00..678.00 rows=1 width=957)
> Sort Key: published
> -> Bitmap Heap Scan on insertion a (cost=37.63..677.99 rows=1
> width=957)
> Recheck Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND
> (i_fulltext @@ '''met'''::tsquery))
> 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))
> -> Bitmap Index Scan on insertion_i_fulltext_idx
> (cost=0.00..37.63 rows=163 width=0)
> Index Cond: ((i_fulltext @@ '''dobl'''::tsquery) AND
> (i_fulltext @@ '''met'''::tsquery))
> (8 righe)
>
>
> I will resolve this problem (different result) soon. It's possible execute
> explain only server A or only server B ?
>
> Regards
>
>
> 2012/6/13 Tatsuo Ishii <ishii at postgresql.org>
>
>> Do those failed "complex" EXPLAINs contain funcation calls, especially
>> nextval or setval? If so, I think it's the case where pgpool has a
>> logical error I have just found. Pgpool first checks if the query is
>> EXPLAIN (and not EXPLAIN ANALYZE). If so, it's ok to load
>> balance. Problem is the next step. Pgpool looks into the EXPLAIN and
>> if it finds a function call, it sends to all PostgreSQL nodes because
>> the function might modify database. Of course this is nonsense, since
>> plain EXPLAIN does nothing to database...
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese: http://www.sraoss.co.jp
>>
>> > No, a few Explain cause errors.
>> > The trivial explain is ok. The complex explain causes this error :(
>> > In fact, as I have seen, even if the table has the same row/index, the
>> > explain gives differen results. I will resolve this soon.
>> > I want that the explain gets only done on the master node. Only one node.
>> > Does the configuration “replicate_explain = off” exist ???
>> >
>> > Thanks for your answer.
>> >
>> > Nicolas
>> >
>> > 2012/6/13 Tatsuo Ishii <ishii at postgresql.org>
>> >
>> >> Even trivial explain like "EXPLAIN SELECT 1" causes errors?
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese: http://www.sraoss.co.jp
>> >>
>> >> > Hi,
>> >> > I would like to know if I can exclude the command EXPLAIN from the
>> >> pgpool.
>> >> > It generates a lot of errors:
>> >> >
>> >> > 2012-06-13 09:35:11 ERROR: pid 15204: read_kind_from_backend: 1 th
>> kind C
>> >> > does not match with master or majority connection kind D
>> >> > 2012-06-13 09:35:11 ERROR: pid 15204: kind mismatch among backends.
>> >> > Possible last query was: "EXPLAIN SELECT ...... " kind details are:
>> 0[D]
>> >> > 1[C]
>> >> > 2012-06-13 09:35:11 LOG: pid 15204: do_child: exits with status 1
>> due
>> >> to
>> >> > error
>> >> >
>> >> > pool-II-3.1.3
>> >> > PostgreSQL 9.1.2
>> >> > both Server same Hardware.
>> >> >
>> >> > Pgpool configuration:
>> >> >
>> >> > listen_addresses = '*'
>> >> > port = 5432
>> >> > socket_dir = '/var/run/postgresql'
>> >> > pcp_port = 9898
>> >> > pcp_socket_dir = '/tmp'
>> >> > backend_hostname0 = 'localhost'
>> >> > backend_port0 = 5434
>> >> > backend_weight0 = 1
>> >> > backend_data_directory0 = '/var/lib/postgresql/9.1/main'
>> >> > backend_flag0 = 'ALLOW_TO_FAILOVER'
>> >> > backend_hostname1 = '172.16.0.1'
>> >> > backend_port1 = 5434 #doveva essere a 5434
>> >> > backend_weight1 = 1
>> >> > backend_data_directory1 = '/var/lib/postgresql/9.1/main'
>> >> > backend_flag1 = 'ALLOW_TO_FAILOVER'
>> >> > enable_pool_hba = off
>> >> > authentication_timeout = 60
>> >> > # - SSL Connections -
>> >> > num_init_children = 32
>> >> > max_pool = 10
>> >> > child_life_time = 300
>> >> > child_max_connections = 0
>> >> > connection_life_time = 0
>> >> > client_idle_limit = 0
>> >> > log_destination = 'stderr'
>> >> > print_timestamp = on
>> >> > log_connections = off
>> >> > log_hostname = on
>> >> > log_statement = off
>> >> > log_per_node_statement = off
>> >> > log_standby_delay = 'none'
>> >> > syslog_facility = 'LOCAL0'
>> >> > syslog_ident = 'pgpool'
>> >> > debug_level = 0
>> >> > pid_file_name = '/var/run/pgpool/pgpool.pid'
>> >> > logdir = '/tmp'
>> >> > connection_cache = on
>> >> > reset_query_list = 'ABORT; DISCARD ALL'
>> >> > replication_mode = on
>> >> > replicate_select = off
>> >> > insert_lock = on
>> >> > lobj_lock_table = ''
>> >> > replication_stop_on_mismatch = off
>> >> > failover_if_affected_tuples_mismatch = off
>> >> > load_balance_mode = off
>> >> > ignore_leading_white_space = on
>> >> > white_function_list = ''
>> >> > black_function_list = 'nextval,setval'
>> >> > master_slave_mode = off
>> >> > sr_check_period = 0
>> >> > sr_check_user = 'nobody'
>> >> > sr_check_password = ''
>> >> > delay_threshold = 0
>> >> > follow_master_command = ''
>> >> > parallel_mode = off
>> >> > enable_query_cache = off
>> >> > pgpool2_hostname = 'localhost'
>> >> > system_db_hostname = 'localhost'
>> >> > system_db_port = 5434
>> >> > system_db_dbname = 'pgpool'
>> >> > system_db_schema = 'pgpool_catalog'
>> >> > system_db_user = 'pgpool'
>> >> > system_db_password = ''
>> >> > health_check_period = 0
>> >> > health_check_timeout = 20
>> >> > health_check_user = 'nobody'
>> >> > health_check_password = ''
>> >> >
>> >> > ------------------------------------
>> >> >
>> >> > The answers about these questions are welcome.
>> >> >
>> >> > Best Regards
>> >> >
>> >> > Nicolas
>> >>
>>
More information about the pgpool-general
mailing list