Re: psycopg2 (async) socket timeout

Hi,

> Well something like that ;) I'd try doing it on the per-query level,
> actually. Since you can't have more than one outstanding query, your
> keepalive won't be sent until the current query finishes.

Hm, what would be the difference to how I did it? It's per query, isn't it?

I kinda know what you mean... PostgreSQL thinks that
connection=transaction and so you have only one outstanding operation
on the connection(=transaction), otherwise it won't be able to
serialize. But how would the implementation in the txpostgresapi
Connection code change? Would it?

> psycopg2 needs to support PQPing, but that should be easy.

>> However, doing a connection.close() then doesn't seem to help much,

After adding debugging messages to the reactor, found out why:

Both the connection and the cursor are still in the select() list of
the reactor.

So my current try is the following one and this works (far) - no
exceptions raised by the reactor anymore... (note that I pass the
cursor instance too, now)

def handleTimeout(self, d, cursor):
""" handles the timeout since we DID time out """
log.err("timed out")
#cursor.close() # cannot be used while async query is
in progress :-(
self.reactor.removeReader(cursor)
self.reactor.removeWriter(cursor)
self.close() # close the connection (maybe it was a
conn problem)
if not d.called:
d.errback(failure.Failure(RuntimeError()))
def close(self):
self.reactor.removeReader(self)
self.reactor.removeWriter(self)
return txpostgres.Connection.close(self)

I don't really understand why I need to remove the cursor from the
reactor as well. Shouldn't the cursor and the connection use the same
socket? Or does it mean that there are two different python objects in
the reactor which are actually using the same socket, but the reactor
doesn't know that? I feel that I'm so close to it, but unfortunately
the next time I try to create a new cursor, it will nag about async
operation still in progress...

I can't call cursor.close() since it is guarded by
EXC_IF_ASYNC_IN_PROGRESS in psycopg2 - and so cannot be called while
my query is (technically) still in progress. There's a PQcancel thing
on the connection (not the cursor!) which cancels the query by sending
a request to the server - that's not exactly what I want, though. I
planned to set statement_timeout for the server to automatically
abandon the query after a timeout and set my client timer to the same
timeout - so both would do the right thing without explicit
synchronization.

> Ha, it always comes back to the ticket I filed when writing txpostgres:
> http://twistedmatrix.com/trac/ticket/4539

Interesting... however, in my simple case, it seems to be fixed by
calling both "removeReader" and "removeWriter" to remove both the
cursor instance and the connection instance from the watchlist.

> Believe it or not, this problem seems to also prevent proper
> LISTEN/NOTIFY implementation...

If it helps, in my case I changed functions in
/usr/lib/python2.6/site-packages/twisted/internet/selectreactor.py
(addReader, addWriter, removeReader, removeWriter) to print what's
going on and found out about the cursor that way. What the ValueError
means is that some of the file-like objects closed but are still in
the reactor - so the reactor doesn't know what it's supposed to do
with them: when is a closed FD ready? So one only has to make sure to
remove the file-like object early enough, before the reactor gets
control again.

Danny