Open fw-aaron opened 2 months ago
dvarrazzo
commented
2 months ago If you are using greenlets then you should use psycopg in green mode. You should also make sure that you are not sharing connections across processes, which I don't know if it's happening or not in flask.
No, psycopg will not reuse a connection across threads.
Looking better, I see that you are using eventlet. Did you call the appropriate functions to make eventlet compatible with psycopg?
fw-aaron
commented
1 month ago Hey, your name is on that page 🚀!
The first two lines of my main py file are
import eventlet
eventlet.monkey_patch()Thank you for drawing my attention to this. I wasn't aware that this project uses that facility, nor that I use greenthreads by default because of this.
I start a new thread using
thread = threading.Thread(target=thread_runner); thread.start()
Since my project uses monkey_patch, this thread is actually a green thread (right?).
conn = psycopg2.connect(database=..., user=..., password=..., host=..., port=...)
Since my project uses monkey_patch, this is using a green-thread-enabled psycopg connect (right?).
The problem is still unexplained, but I am understanding aspects of the problem much better now! I'll look into "Migrating off of Eventlet" https://eventlet.readthedocs.io/en/latest/asyncio/migration.html#migration-guide, but this might be quite difficult since any system function call could be affected by removing that monkey_patch()...
dvarrazzo
commented
1 month ago Does psycopg2.extensions.get_wait_callback return you None or anything?
fw-aaron
commented
1 month ago logging.info(" wait callback is " + str(psycopg2.extensions.get_wait_callback)) shows
wait callback is <built-in function get_wait_callback>
dvarrazzo
commented
1 month ago It's a function, you have to call it, with psycopg2.extensions.get_wait_callback().
fw-aaron
commented
1 month ago Oh, I thought the presence of get_wait_callback would be enough. When I do logging.info(" wait callback is " + str(psycopg2.extensions.get_wait_callback())), I get
wait callback is <function eventlet_wait_callback at 0x779b36c05840>which also appears to be a function provided by eventlet. logging.info(" wait callback is " + str(psycopg2.extensions.get_wait_callback()())) yields
ERROR:root:m4tp: connect_db()
Traceback (most recent call last):
File "app/db_connection.py", line 17, in connect_db
logging.info(" wait callback is " + str(psycopg2.extensions.get_wait_callback()()))
TypeError: eventlet_wait_callback() missing 1 required positional argument: 'conn'obviously because I'm calling the wait callback incorrectly.
dvarrazzo
commented
1 month ago Ok, so that means that psycopg is monkeypatched well by eventlet.
I have no idea what is going wrong there: you should hear from the eventlet folks.
fw-aaron
commented
1 month ago Thank you for your feedback! In the above logging just after:
Logging output from the logging statement above looks like this
What do you make of those file numbers? I guess it looks like psycopg opened a connection using file descriptor 14, then the connection was closed, then a new connection was opened which reused file number 14, then it was closed. This happened a dozen times or so, then a new connection was made while file number 14 was open, so file descriptor 15 was used. While that connection was open, a new connection was made resulting in file descriptor 16.
Does that sound like a reasonable interpretation of this logging output?
Describe the bug
Please let us know:
1: what you did
I start a new thread using
thread = threading.Thread(target=thread_runner); thread.start(). Both threads (the new one and the existing one) use the following function to create a connection to the database:Then the cursor is retrieved (
cursor=conn.cursor()) and used to execute queries (db.execute('update ftp_settings set last_submission = %s', [date]); db.connection.commit()).2: what you expected to happen
Both thread should be able to access the database and run queries without trouble.
3: what happened instead
Logging output from the logging statement above looks like this:
After a while, I get the following error:
Is it possible that
psycopg2.connectis returning a reused connection? Based on the error, it sounds like one greenthread used the connection, then later the second greenthread picked up the used connection. My impression is thatpsycopg2.connectshould return a new connection. Here is my python dependency list:I don't think any of these packages would provide connection pooling, but I'm not familiar with many of these packages.
If possible, provide a script reproducing the issue.
The issue isn't reliably reproducible.