[Pgpool-hackers] Problems with serializable connections and pgpool-II

[Fabio Tranchitella]

Hello,

I'm Fabio Tranchitella from Debian; I'm hitting some problems with pgpool-II
version 2.1 and serializable connections, using the replication and load
balancing features.

I can easily reproduce the problem with the following python code:

"""
import thread
import psycopg2
import time

def do_it(value):
    o1 = psycopg2.connect('user=demo dbname=demo host=demo port=5434')
    o1.set_isolation_level(2)
    c1 = o1.cursor()
    print "UPDATE " + value
    c1.execute("UPDATE users SET mobile = '" + value + "' WHERE id = 1;")
    time.sleep(5)
    print "COMMIT " + value
    o1.commit()
    print "COMMITTED " + value

thread.start_new_thread(do_it, ('A',))
time.sleep(1)
thread.start_new_thread(do_it, ('B',))
time.sleep(20)
"""

What I get running this code is the following output:

UPDATE A
UPDATE B
COMMIT A
COMMITTED A
Unhandled exception in thread started by <function do_it at 0xb7ac4f44>
Traceback (most recent call last):
  File "test.py", line 12, in do_it
    c1.execute("UPDATE users SET mobile = '" + value + "' WHERE id = 1;")
psycopg2.ProgrammingError: kind mismatch between backends
HINT:  check data consistency between master and other db node
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

The daemon logs the following message:

2008-11-22 19:33:59 ERROR: pid 20362: pool_process_query: 1 th kind C does not match with master connection kind E
2008-11-22 19:33:59 LOG:   pid 20362: do_child: exits with status 1 due to error

It seems to me that pgpool-II doesn't understand the serialization exception
generated by one of the two PostgreSQL servers, as reported in the PostgreSQL
log:

2008-11-22 19:33:59 CET ERROR:  could not serialize access due to concurrent update

I suppose pgpool-II should handle in a special way the serialization errors
from PostgreSQL, rollback the transactions and report back to the client the
PostgreSQL error.

Any idea about how to solve this issue?

Thanks in advance,

-- 
Fabio Tranchitella <kobold at debian.org>                        .''`.
Proud Debian GNU/Linux developer, admin and user.            : :'  :
                                                             `. `'`
   http://people.debian.org/~kobold/                           `-
_____________________________________________________________________
1024D/7F961564, fpr 5465 6E69 E559 6466 BF3D 9F01 2BF8 EE2B 7F96 1564