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

[Tatsuo Ishii]

Your complain is correct and we have to do something for this.
Actually you could reproduce the problem by using plain psql.

However I have no time to make a fix right now. Please be patient for
a while.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> 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
> _______________________________________________
> Pgpool-hackers mailing list
> Pgpool-hackers at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-hackers