View Issue Details

IDProjectCategoryView StatusLast Update
0000038Pgpool-IIBugpublic2012-11-23 20:12
ReporterawallandAssigned Tot-ishii 
PrioritynormalSeveritymajorReproducibilityalways
Status assignedResolutionopen 
Platformx86_64OSlinux 2.6.32-279.14.1.el6.x86_64OS Versioncentos 6
Product Version 
Target VersionFixed in Version 
Summary0000038: pgpool causes erroneous commit when DROP FUNCTION is sent
Descriptionwe have a pgpool 3.2.0 cluster in replication & load balance mode with 3 nodes.

we execute sql scripts with psql against the pgpool node and we have a problem as soon as a "DROP FUNCTION" statement appears in the script, because what happens is, that 1 of the three nodes continues the open transaction, whereas the other 2 nodes are issued a commit which leads to inconsistent databases.

Steps To Reproduce
psql -h pgpoolserver
BEGIN;
drop function if exists a(a text);



statement log of the nodes:

node1:
LOG: statement: begin;
LOG: statement: drop function if exists a(a text);

node2:
LOG: statement: begin;
LOG: statement: drop function if exists a(a text);
LOG: statement: COMMIT

node3:
LOG: statement: begin;
LOG: statement: drop function if exists a(a text);
LOG: statement: COMMIT

Additional Informationthis does for example not happen with drop table.

for us this is quite a big problem, since it leads to inconsistent databases.
TagsNo tags attached.

Activities

t-ishii

2012-11-21 08:16

developer   ~0000160

Couldn't reproduce here(pgpool-II 3.2.0 on Linux x86_64). Can you show pgpool.conf?

awalland

2012-11-21 08:29

reporter   ~0000161

hi!

thank you very much for your fast reply!

here is my pgpool.conf

[root@dbbalancer ~]# egrep -v '^\s*#' /etc/pgpool-II/pgpool.conf |sed '/^$/d'
listen_addresses = '*'
port = 5432
socket_dir = '/tmp'
pcp_port = 9898
pcp_socket_dir = '/tmp'
backend_hostname0 = 'db01'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.2/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'db02'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.2/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = 'db03'
backend_port2 = 5432
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/9.2/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = off
authentication_timeout = 60
ssl = off
num_init_children = 32
max_pool = 400
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 = off
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 = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'
master_slave_mode = off
master_slave_sub_mode = 'slony'
sr_check_period = 0
sr_check_user = 'nobody'
sr_check_password = ''
delay_threshold = 0
follow_master_command = ''
parallel_mode = off
pgpool2_hostname = ''
health_check_period = 0
health_check_timeout = 20
health_check_user = 'nobody'
health_check_password = ''
health_check_max_retries = 0
health_check_retry_delay = 1
failover_command = ''
failback_command = ''
fail_over_on_backend_error = on
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = off
trusted_servers = ''
delegate_IP = ''
wd_hostname = ''
wd_port = 9000
wd_interval = 10
ping_path = '/bin'
ifconfig_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = 'ifconfig eth0:0 down'
arping_path = '/usr/sbin' # arping command path
arping_cmd = 'arping -U $_IP_$ -w 1'
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
relcache_expire = 0
relcache_size = 256
check_temp_table = on
memory_cache_enabled = on
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 10000000
memqcache_expire = 28800 ## 8h
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''



did i overlook some configuration issue?

t-ishii

2012-11-21 11:15

developer   ~0000163

I recreated pgpool.conf based on yours (actual pgpool.conf attached), but still not reproduced. Can you start pgpool with debug option (-d) enabled and show us log?

t-ishii

2012-11-21 11:16

developer  

pgpool.conf (3,090 bytes)

awalland

2012-11-21 18:32

reporter  

debug.log (36,082 bytes)

awalland

2012-11-21 18:34

reporter   ~0000164

hi!

i added the debug.log

information to the timestamps in the log:

2012-11-21 10:30:33 <- hit enter after 'begin;'
2012-11-21 10:30:42 <- hit enter after 'drop function if exists a(a text);'
2012-11-21 10:30:55 <- hit ctrl+d to log out

awalland

2012-11-22 19:07

reporter   ~0000165

Last edited: 2012-11-23 01:38

View 2 revisions

just some additional information:

i just cloned the git repo and used a freshly compiled pgpool to solve the jdbc connection issue and the erroneous commit issue is still there.

currently we are working around it by using "create or replace function" instead of "drop function" and "create function".

t-ishii

2012-11-23 12:19

developer   ~0000166

Still cannot reproduce the problem. Can you attach gdb to pgpool right after you hit "drop function if exists..." and take backtrace?

awalland

2012-11-23 20:11

reporter   ~0000167

Last edited: 2012-11-23 20:13

View 2 revisions

hi!

i am not 100% sure if i did it right, but here's what i did:


1.) psql
2.) begin;
3.) drop function if exists a(a text);
4.) find pgpool child pid that i am connected to
5.) gdb /usr/bin/pgpool <pid>
6.) bt

see attached bt.txt for the backtrace.

awalland

2012-11-23 20:12

reporter  

bt.txt (813 bytes)
1.) psql
2.) begin;
3.) drop function if exists a(a text);
4.) find pgpool child pid that i am connected to
5.) gdb /usr/bin/pgpool <pid>
6.) bt
(gdb) bt
#0  0x00000039958e0ce3 in __select_nocancel () from /lib64/libc.so.6
#1  0x0000000000422dd8 in read_packets_and_process (frontend=0x273c970, backend=0x7fc23be9b010, reset_request=0, state=0x7fff8783d400, num_fields=0x7fff8783d406, cont=0x7fff8783d3ff "\001")
    at pool_process_query.c:4658
#2  0x000000000041a0e9 in pool_process_query (frontend=0x273c970, backend=0x7fc23be9b010, reset_request=0) at pool_process_query.c:196
#3  0x0000000000409bae in do_child (unix_fd=3, inet_fd=4) at child.c:371
#4  0x0000000000405f15 in fork_a_child (unix_fd=3, inet_fd=4, id=28) at main.c:1243
#5  0x00000000004048d5 in main (argc=3, argv=0x7fff87841b98) at main.c:661
bt.txt (813 bytes)

Issue History

Date Modified Username Field Change
2012-11-21 01:29 awalland New Issue
2012-11-21 08:16 t-ishii Note Added: 0000160
2012-11-21 08:16 t-ishii Assigned To => t-ishii
2012-11-21 08:16 t-ishii Status new => assigned
2012-11-21 08:16 t-ishii Steps to Reproduce Updated View Revisions
2012-11-21 08:29 awalland Note Added: 0000161
2012-11-21 11:15 t-ishii Note Added: 0000163
2012-11-21 11:16 t-ishii File Added: pgpool.conf
2012-11-21 18:32 awalland File Added: debug.log
2012-11-21 18:34 awalland Note Added: 0000164
2012-11-22 19:07 awalland Note Added: 0000165
2012-11-23 01:38 awalland Note Edited: 0000165 View Revisions
2012-11-23 12:19 t-ishii Note Added: 0000166
2012-11-23 20:11 awalland Note Added: 0000167
2012-11-23 20:12 awalland File Added: bt.txt
2012-11-23 20:13 awalland Note Edited: 0000167 View Revisions