[pgpool-general: 5196] Error cannot execute nextval while had configure black_function_list on pgpool.conf

[Agus Syafaat]

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.pgpool.net/pipermail/pgpool-general/attachments/20161222/a9d1cac0/attachment.htm>