This project is archived and is in readonly mode.
Bug in 2.3.2 when connecting via pgpool?
Reported by Kevin | December 21st, 2010 @ 11:16 PM
Hi guys, thanks for fixing the error with connecting to a pooler. It fixed pgpool as well. I meant to submit a bug on that before. My issue seems to be that there are intermittent times when I'm unable to write via cursor.execute when connecting via pgpool. (I thought it was working earlier today when I made this bare code..?) I can't replicate the issue at all when connecting directly. I can see that it is matching the code to quit but it seems like nothing is getting sent to the server, as verified in the postgres logs. I can create a new connection via pgpool and that works fine. Is there something I'm doing wrong? Also at this point, I'm unable to control-c out of the program or kill it. Here's my simplified code:
!/usr/bin/python26
import select
import psycopg2
import psycopg2.extensions
conn = psycopg2.connect("dbname=jobs user=postgres
host=localhost port=9999")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
curs = conn.cursor()
curs.execute("LISTEN test; LISTEN test_quit;")
while 1:
if select.select([conn],[],[],5) == ([],[],[]):
print ""
else:
try:
conn.poll()
while conn.notifies:
noti = conn.notifies.pop(0)
print "Got NOTIFY:", noti
if noti[1] == "test":
print "test"
elif noti[1] == "test_quit":
print "QUIT"
curs.execute("UNLISTEN test; UNLISTEN test_quit;")
curs.close();
conn.close();
except Exception, data:
print data
pass
Comments and changes to this ticket
-
Daniele Varrazzo December 22nd, 2010 @ 02:03 AM
Hello Kevin,
I've honestly done little with pgPool/pgBouncer, so I don't know if everything we expect from the behaviour of a connection is still true when getting them from a connection pool. I've tried to run the test suite with a stock pgBouncer configuration and many tests fail: there is probably something to study in psycopg/external pool interaction, and maybe something we can expect it won't work.
I don't know, for instance, if notify works at all with pooling, as it seems ypu need to be served always from the same session, assumption I don't give as granted with a pooler in the middle.
It's hard to tell what an intermittent problem may be caused from. It may be helpful if you could compile psycopg in debug mode (see setup.cfg) and run it with the debug messages on (setting the env var PSYCOPG_DEBUG=1 when running): if the program gets stuck we may then know where.
I think we need a chat with the guys in the pooling department: Marko Kreen comes me to mind. As soon as we have the ML back again (should be soon) I'd really want to see if we are doing anything wrong and to improve the psycopg/pools interaction.
-
Daniele Varrazzo December 22nd, 2010 @ 02:08 AM
- Tag set to “notify, pgpool”
-
Daniele Varrazzo January 27th, 2011 @ 11:32 AM
Hi Kevin,
Have you done any progress studying this problem? I've not been able to play with pgpool yet.
My understanding is that your listen command will go to a random connection so the notification will be missed by any client attached to the pool. I think the connection that has to receive the notification should be handled outside the pool.
Can you confirm that? thanks.
-
Daniele Varrazzo February 6th, 2011 @ 01:53 PM
- State changed from “new” to “invalid”
I assume there is not much to do and close the ticket.
I think we can have a chat about how psycopg should behave with middleware on the mailing list.
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป
<b>WARNING:</b> the informations in this tracker are archived. Please submit new tickets or comments to <a href="https://github.com/psycopg/psycopg2/issues">the new tracker</a>.
<br/>
Psycopg is the most used PostgreSQL adapter for the Python programming language. At the core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.
Daniele Varrazzo
Kevin