Unable to recover connection to remote host after SSL SYSCALL error

[svnv]

I'm not really sure if this is an error in my application, momoko or psycopg2, but I have an application that is unable recover the database connection to a remote host, if the machine running the application loses network connectivity.

The application:

from tornado.ioloop import IOLoop
import momoko
from tornado.gen import engine

if __name__ == '__main__':

    ioloop = IOLoop.instance()
    db = momoko.Pool(
        dsn='dbname=your_db user=your_user password=very_secret_password '
            'host=remote_host port=5432',
        size=1,
        ioloop=ioloop,
    )

    future = db.connect()
    ioloop.add_future(future, lambda f: ioloop.stop())
    ioloop.start()
    future.result()

    @engine
    def never_ending_db_query_loop(callback):
        while True:
            try:
                cursor = yield db.execute('SELECT 1')
                row  = cursor.fetchone()
                print 'db returned: {0}'.format(row[0])
            except Exception as e:
                print e
        callback()

    never_ending_db_query_loop(ioloop.stop)
    ioloop.start()

If I disconnect my computer from the network while the application is running, the application will eventually throw this exception: psycopg2.OperationalError with message SSL SYSCALL error: Network is down\n.

And after throwing the first exception, I start getting psycopg2.ProgrammingError exceptions with message execute cannot be used while an asynchronous query is underway. The application will continue to throw these exceptions forever, even if I reconnect my computer to the network.

Is this an error in my application, momoko or psycopg2? I googled a bit around I found this https://github.com/psycopg/psycopg2/issues/263 in the github page for psycopg2, but I'm not sure if is related to my problem.

[haizaar]

This issue should have been fixed in Momoko 2.2.0. Can you please try with the latest 2.2.1? If it still does not work, please provide more details about your environment - python version, psycopg2 version, Linux flavour and postgresql server version.

P.S. You code looks correct from the first glance. I suggest you double-check against official tutorials.

[svnv]

Hmm, here is an overview of my system, I run the application using a virtualenv in osx:

env/bin/python --version
Python 2.7.10

$ env/bin/pip freeze | grep 'Momoko\|psycopg2\|tornado'
Momoko==2.2.1
psycopg2==2.6.1
tornado==4.2.1

$ uname -a
Darwin snvBook.local 14.5.0 Darwin Kernel Version 14.5.0: Wed Jul 29 02:26:53 PDT 2015; root:xnu-2782.40.9~1/RELEASE_X86_64 x86_64

The database is on another host, and is the following version:

$ uname -a
Linux multiweb-vpc 3.2.0-4-amd64 #1 SMP Debian 3.2.51-1 x86_64 GNU/Linux

...
SELECT version();
                                           version
----------------------------------------------------------------------------------------------
 PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

[haizaar]

Ahh, It's mac... I don't have an access to mac. I've tried it on Linux.

Your code as it is reveals another bug, but if I add time.sleep(1) in the loop it works OK.

Can you please try with time.sleep(1) and if it still does not work, can you please check whether https://github.com/psycopg/psycopg2/issues/263 still happens on mac?

[haizaar]

Also I've fixed #127. You are welcome to try with the latest master as well.

[svnv]

EDIT, I did this with the pre #127 verison.

---

Adding a time.sleep(1) inside the while loop does not change behaviour on my mac, i still get the psycopg2.ProgrammingError execute cannot be used while an asynchronous query is underway after the initial psycopg2.OperationalError error.

I also tested the script in a docker container on my machine, and was not able to reproduce the bug there.

[haizaar]

Then I would guess it's something related to your mac. Can you verify that https://github.com/psycopg/psycopg2/issues/263 does not happen on mac?

[friedcell]

I've tested on the Mac before and IIRC established that it's a psycopg issue - you don't get the right connection status from it...

[svnv]

Using the the python 2 docker image i got the following in my shell when I disconnected and then reconnected the machine to the network:

db returned: 1
db returned: 1
could not translate host name "hostname" to address: Name or service not known

No database connection available
No database connection available
db returned: 1
db returned: 1 

Looks fine to me.