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

[Tatsuo Ishii]

Fabio,

I think I have fixed the problem. The way I fixed is the same one as
you suggested:

>> 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.

Thanks for your suggestion. Please grab the CVS Head and test it if
you like.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> 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.