[pgpool-general: 5198] Re: Error cannot execute nextval while had configure black_function_list on pgpool.conf
David Sisk -X (dsisk - TEKSYSTEMS INC at Cisco)
dsisk at cisco.com
Fri Dec 23 04:57:43 JST 2016
I don’t believe you need to explicitly blacklist the nextval() function…PGPool will automatically blacklist it already (right folks?). Remove that from the blacklist and see if it works correctly.
Also, be aware that there are some bugs in 3.5.4 around load-balancing that might render it ineffective. Test carefully with your specific application.
Cheers,
[banner2]
David Sisk
Engineer - Software
dsisk at cisco.com<mailto:dsisk at cisco.com>
Tel:
Cisco Systems, Inc.
7025-6 Kit Creek Road PO Box 14987
RESEARCH TRIANGLE PARK
27709-4987
United States
cisco.com
[http://www.cisco.com/assets/swa/img/thinkbeforeyouprint.gif]Think before you print.
This email may contain confidential and privileged material for the sole use of the intended recipient. Any review, use, distribution or disclosure by others is strictly prohibited. If you are not the intended recipient (or authorized to receive for the recipient), please contact the sender by reply email and delete all copies of this message.
Please click here<http://www.cisco.com/web/about/doing_business/legal/cri/index.html> for Company Registration Information.
From: pgpool-general-bounces at pgpool.net [mailto:pgpool-general-bounces at pgpool.net] On Behalf Of Agus Syafaat
Sent: Thursday, December 22, 2016 11:32 AM
To: pgpool-general at pgpool.net
Subject: [pgpool-general: 5196] Error cannot execute nextval while had configure black_function_list on pgpool.conf
Hello,
I'm new member in pgpool..:)
I had problem when setting up load balancing on pgpool (i use version 3.5.4) connected to postgresql 9.5 with stream replication ( using repmgr). I always getting error "ERROR: cannot execute nextval() in a read-only transaction" while i had configure black_function_list for sequences. Kindly advice whats going wrong..
Below is my pgpool configuration :
# - pgpool Connection Settings -
listen_addresses = 'localhost'
# Host name or IP address to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
port = 15982
# Port number
# (change requires restart)
socket_dir = '/var/run/postgresql'
# Unix domain socket path
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
listen_backlog_multiplier = 2
# Set the backlog parameter of listen(2) to
# num_init_children * listen_backlog_multiplier.
# (change requires restart)
serialize_accept = off
# whether to serialize accept() call to avoid thundering herd problem
# (change requires restart)
# - pgpool Communication Manager Connection Settings -
pcp_listen_addresses = '*'
# Host name or IP address for pcp process to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
pcp_port = 9898
# Port number for pcp
# (change requires restart)
pcp_socket_dir = '/var/run/postgresql'
# Unix domain socket path for pcp
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
# - Backend Connection Settings -
backend_hostname0 = 'pg-prod-01a'
# Host name or IP address to connect to for backend 0
backend_port0 = 5432
# Port number for backend 0
backend_weight0 = 0
# Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/opt/ebs/postgresql/9.5/main'
# Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
# Controls various backend behavior
# ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVER
backend_hostname1 = 'pg-prod-01b'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/opt/ebs/postgresql/9.5/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
# - Authentication -
enable_pool_hba = on
# Use pool_hba.conf for client authentication
pool_passwd = 'pool_passwd'
# File name of pool_passwd for md5 authentication.
# "" disables pool_passwd.
# (change requires restart)
authentication_timeout = 60
# Delay in seconds to complete client authentication
# 0 means no timeout.
#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
# Activate load balancing mode
# (change requires restart)
ignore_leading_white_space = on
# Ignore leading white spaces of each query
white_function_list = ''
# Comma separated list of function names
# that don't write to database
# Regexp are accepted
black_function_list = 'nextval,setval'
# Comma separated list of function names
# that write to database
# Regexp are accepted
database_redirect_preference_list = ''
# comma separated list of pairs of database and node id.
# example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'
# valid for streaming replicaton mode only.
app_name_redirect_preference_list = ''
# comma separated list of pairs of app name and node id.
# example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
# valid for streaming replicaton mode only.
allow_sql_comments = off
# if on, ignore SQL comments when judging if load balance or
# query cache is possible.
# If off, SQL comments effectively prevent the judgment
# (pre 3.4 behavior).
Thanks in advance,
Regards
Agus
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20161222/b860b869/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 121724 bytes
Desc: image002.png
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20161222/b860b869/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 1469 bytes
Desc: image004.png
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20161222/b860b869/attachment-0003.png>
More information about the pgpool-general
mailing list