[pgpool-general: 632] Re: exclude EXPLAIN from the pgpool.

Nicolas Amato nicolas.amato at autoxy.it
Thu Jun 14 01:00:22 JST 2012


I have add to black_function_list = 'nextval,setval,to_tsquery,to_tsvector'

but there is an error again. (ERROR:  kind mismatch among backends)




2012/6/13 Tatsuo Ishii <ishii at postgresql.org>

> 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.45rows=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
> >> >>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120613/64eb2b3c/attachment.html>


More information about the pgpool-general mailing list