[Twisted-Python] adbapi / Postgres : LISTEN/NOTIFY and RealDictCursor

[Tobias Oberstein]

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 at 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?