[Twisted-Python] adbapi / Postgres : LISTEN/NOTIFY and RealDictCursor
I am using Twisted adbapi, psycopg2 and PostgreSQL which works really great. Now, there are 2 features I'd like to use in this context, which I've been using before without Twisted. I'm missing those. Would be glad for any hints .. If there is work to do to make those features happen, and it's doable for non-Twisted-gods, then I'd also be willing to invest time/effort ... Thanks, Tobias LISTEN/NOTIFY ============ Is a Postgres>=9 feature which allows one to asynchronously notify a database client from _within_ the database (i.e. from pgPLSQL). http://www.postgresql.org/docs/9.1/static/sql-notify.html http://initd.org/psycopg/docs/advanced.html#asynchronous-notifications On the Python side, it'll do a select() on the TCP underlying the connection to Postgres. adbapi assumes DBAPI, and above is a non-standard extension. Now, what would be a sensible approach to use that with Twisted? Own thread? How do I integrate that into a standard Twisted application service hierarchy? Or somehow extend adbapi? psycopg2.extras.RealDictCursor ======================== We have a convention that all database access must be exclusively via stored procedures with all IN and all OUT parameters named. Outside the context of Twisted, we use that in conjunction with # Real Dict Cursor (returns a Dict which can be referenced via named bracket access, or offset) dict_cursor = db.cursor(cursor_factory = psycopg2.extras.RealDictCursor) dict_cursor.execute("SELECT first_name, last_name, email FROM user.get_user(%s)", (user_id,)) my_row = dict_cursor.fetchone() => {'first_name': 'John', 'last_name': 'Doe', 'email': 'john@doe.com'} Is there a way to do something like this when using self.dbpool.runInteraction or otherwise? Setting the "cursor factory" to be used? On the other hand, the txn given to the fun provided with runInteraction already is somehow dressed up, right?
after actually looking at the adbapi.py code, it's quite accessible, so I guess doing
dict_cursor = db.cursor(cursor_factory = psycopg2.extras.RealDictCursor)
would entail forwarding the desired cursor factory to here https://github.com/powdahound/twisted/blob/master/twisted/enterprise/adbapi.... I'll get that done. btw: are there plans to get Twisted master repo from svn to git? == so whats left is the LISTEN/NOTIFY stuff .. any ideas?
On Nov 3, 2011, at 2:58 PM, Tobias Oberstein wrote:
btw: are there plans to get Twisted master repo from svn to git?
No; the eventual plan is to move to bzr. This is happening very slowly though :). By the way, the official Twisted git mirror _should_ be at https://github.com/twisted/twisted. It looks like nobody's updating that, though. Since people seem to keep using powdahound's mirror, perhaps we should just ask powdahound to do the updating?
On 07:21 pm, glyph@twistedmatrix.com wrote:
On Nov 3, 2011, at 2:58 PM, Tobias Oberstein wrote:
btw: are there plans to get Twisted master repo from svn to git?
No; the eventual plan is to move to bzr. This is happening very slowly though :).
By the way, the official Twisted git mirror _should_ be at https://github.com/twisted/twisted. It looks like nobody's updating that, though. Since people seem to keep using powdahound's mirror, perhaps we should just ask powdahound to do the updating?
I wonder why it's so much harder to update one repository on github than it is to update another. Jean-Paul
btw: are there plans to get Twisted master repo from svn to git?
No; the eventual plan is to move to bzr. This is happening very slowly though :).
By the way, the official Twisted git mirror _should_ be at https://github.com/twisted/twisted. It looks like nobody's updating that, though. Since people seem to keep using powdahound's mirror, perhaps we should just ask powdahound to do the updating?
the readme at https://github.com/powdahound/twisted says: "Automatically updated every 15 minutes". so that could be some script pulling svn and pushing to github. @Garret: is that right? are you hosting and cron'jobbing that?
I should point out that git-svn isn't happy about Twisted's repo, so the
git mirror is non-trivial to maintain. Last time I tried, I got quite a few
curious errors.
Sending from a mobile, pardon my terseness. ~ C.
On Nov 3, 2011 12:56 PM, "Tobias Oberstein"
btw: are there plans to get Twisted master repo from svn to git?
No; the eventual plan is to move to bzr. This is happening very slowly though :).
By the way, the official Twisted git mirror _should_ be at https://github.com/twisted/twisted. It looks like nobody's updating that, though. Since people seem to keep using powdahound's mirror, perhaps we should just ask powdahound to do the updating?
the readme at
https://github.com/powdahound/twisted
says: "Automatically updated every 15 minutes".
so that could be some script pulling svn and pushing to github.
@Garret: is that right? are you hosting and cron'jobbing that?
_______________________________________________ Twisted-Python mailing list Twisted-Python@twistedmatrix.com http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python
Ok, should anyone else need this, here is working code: class PostgresListenService(twisted.application.service.Service): """ PostgreSQL LISTEN/NOTIFY as Twisted service. http://initd.org/psycopg/docs/advanced.html#asynchronous-notifications """ def __init__(self, dsn, channel, timeout = 1): self.dsn = dsn self.channel = channel self.timeout = 1 def notify(self, channel, payload): log.msg("NOTIFY on channel %s with payload %s, delivered on thread %s" % (channel, payload, thread.get_ident())) def run(self): log.msg("LISTEN on channel %s, running on thread %s" % (self.channel, thread.get_ident())) conn = psycopg2.connect(self.dsn) conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) curs = conn.cursor() curs.execute("LISTEN %s;" % self.channel) while not self.stopped: if select.select([conn], [], [], self.timeout) == ([], [], []): pass else: conn.poll() while conn.notifies: notify = conn.notifies.pop() reactor.callFromThread(self.notify, notify.channel, notify.payload) def startService(self): self.stopped = False reactor.callInThread(self.run) Service.startService(self) def stopService(self): self.stopped = True Service.stopService(self)
On Nov 3, 2011, at 6:18 PM, Tobias Oberstein wrote:
while not self.stopped: if select.select([conn], [], [], self.timeout) == ([], [], []): pass
Please note that on high-volume servers, this is problematic, as 'conn' may be higher than the maximum file descriptor pollable by select(). So if you've tuned everything to be smart, use a high-volume reactor like epoll, and set all your ulimits correctly, you will still get tracebacks out of this code depending on random accidents of ordering. This part of the logic might therefore be better implemented as a IReadDescriptor/IWriteDescriptor provider (i.e. a subclass of FileDescriptor), assuming you can ask your postgres binding to give you non-blocking reads and writes. You're certainly not the first person to make this mistake though, http://divillo.com/ gives the same advice :). Also this would probably make a better patch for txpostgres https://launchpad.net/txpostgres than for Twisted itself, since support depends on specific database bindings. -glyph
On Thu, Nov 3, 2011 at 20:54, Corbin Simpson
I should point out that git-svn isn't happy about Twisted's repo, so the git mirror is non-trivial to maintain. Last time I tried, I got quite a few curious errors.
I keep a tarball of a git-svn clone of Twisted at https://ludios.org/mirror/ and update it several times a year. Anyone can grab it and update their copy with: git svn fetch git checkout trunk OR git svn rebase On Windows, first edit .git/config and set `filemode = false` to avoid problems with executable bits. Ivan
Hello Glyph, thank's a lot for this advice! I thought I would have done right, and Twisted-style .. but the select FD trap wasn't on my radar. The https://github.com/wulczer/txpostgres is very interesting. Guess I need to learn about interfaces.IReadWriteDescriptor first to grasp it. Regarding the broader picture: I cannot say I understand what you say completely. " maximum file descriptor pollable by select()" Is this *nix in general? Does that only apply to select.select? Or also: select.poll(), select.epoll(), select.kqueue() ? How would implementing an IReadDescriptor circumvent the problem? By not using select(), but poll/epoll/kqeue? In any case: we're running FreeBSD, so epoll() is out. Is there even a kqueue-based Twisted reactor? Sorry if helping me spawns new questions, Thanks! Tobias for reference (don't know if it applies here): psycopg since v2.2 has http://initd.org/psycopg/docs/advanced.html#asynchronous-support Von: twisted-python-bounces@twistedmatrix.com [mailto:twisted-python-bounces@twistedmatrix.com] Im Auftrag von Glyph Gesendet: Donnerstag, 3. November 2011 23:54 An: Twisted general discussion Betreff: Re: [Twisted-Python] adbapi / Postgres : LISTEN/NOTIFY and RealDictCursor On Nov 3, 2011, at 6:18 PM, Tobias Oberstein wrote: while not self.stopped: if select.select([conn], [], [], self.timeout) == ([], [], []): pass Please note that on high-volume servers, this is problematic, as 'conn' may be higher than the maximum file descriptor pollable by select(). So if you've tuned everything to be smart, use a high-volume reactor like epoll, and set all your ulimits correctly, you will still get tracebacks out of this code depending on random accidents of ordering. This part of the logic might therefore be better implemented as a IReadDescriptor/IWriteDescriptor provider (i.e. a subclass of FileDescriptor), assuming you can ask your postgres binding to give you non-blocking reads and writes. You're certainly not the first person to make this mistake though, http://divillo.com/ gives the same advice :). Also this would probably make a better patch for txpostgres https://launchpad.net/txpostgres than for Twisted itself, since support depends on specific database bindings. -glyph
On Nov 3, 2011, at 7:40 PM, Tobias Oberstein wrote:
Regarding the broader picture: I cannot say I understand what you say completely.
"maximum file descriptor pollable by select()"
Is this *nix in general?
Does that only apply to select.select?
Yep, only to select.select. Search around for FD_SETSIZE for more information.
Or also: select.poll(), select.epoll(), select.kqueue() ?
But those may not be available consistently, so you shouldn't hard-code it to use one of those either :).
How would implementing an IReadDescriptor circumvent the problem? By not using select(), but poll/epoll/kqeue?
It circumvents the problem by allowing whatever mechanism Twisted is using for multiplexing, rather than always using select(). So you still have to select a reactor that actually supports more than FD_SETSIZE sockets.
In any case: we're running FreeBSD, so epoll() is out.
Is there even a kqueue-based Twisted reactor?
Yes... ish. More detail here: http://twistedmatrix.com/trac/ticket/1918. -glyph
participants (6)
-
Corbin Simpson -
exarkun＠twistedmatrix.com -
Glyph -
Glyph Lefkowitz
-
Ivan Kozik -
Tobias Oberstein