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

[Tatsuo Ishii]

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
>>