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

Nicolas Amato nicolas.amato at autoxy.it
Wed Jun 13 21:35:08 JST 2012


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


More information about the pgpool-general mailing list