def main(reactor):
connections = []
for x in range(25):
conn = txpostgres.Connection()
db = yield conn.connect('dbname=postgres')
connections.append(db)
yield connections[0].runOperation(_create_table)
# a 'real' generator, round-robin all connections
def connection_generator():
while True:
for c in connections:
yield c
connect = connection_generator()
inserts = []
for item in range(1000):
db = next(connect)
d = db.runOperation(
'INSERT INTO todo (todo, created_at) '
'VALUES (%s, NOW());',
[item],
)
dl.append(d)
start = reactor.seconds()
yield defer.DeferredList(dl)
diff = reactor.seconds() - start
print("Took {}s".format(diff))
def main(reactor):
conn = txpostgres.Connection()
db = yield conn.connect('dbname=postgres')
print("Databse connected {}".format(db))
yield db.runOperation(_create_table)
dl = []
# for item in ['teach PyYYC', 'find beers', '???', 'profit']:
for item in range(1000):
d = db.runOperation(
'INSERT INTO todo (todo, created_at) VALUES (%s, NOW());',
[item],
)
dl.append(d)
print("All inserts queued")
s = reactor.seconds()
yield defer.DeferredList(dl)
diff = reactor.seconds() - s
print("done creating database")
print("Querying all TODOs:")
res = yield db.runQuery('SELECT * from todo;')
expected = 1
last = None
for _id, todo, created_at in res:
print(" {}: '{}' at '{}'".format(_id, todo, created_at))
if last and created_at < last:
print("out of order")
sys.exit(3)
last = created_at
if _id != expected:
print("DING")
sys.exit(4)
expected += 1
print("done {}".format(diff))
def test_connectionRemovingReader(self):
"""
The connection is not reading from the socket while a cursor is
running.
"""
class ExclusiveCursor(txpostgres.Cursor):
testcase = self
def doRead(self):
for reader in self.reactor.getReaders():
if isinstance(reader, txpostgres.Connection):
self.testcase.fail(
"doRead called on Cursor "
"while a Connection is among readers: %r" %
self.reactor.getReaders())
return txpostgres.Cursor.doRead(self)
conn = txpostgres.Connection()
conn.cursorFactory = ExclusiveCursor
d = conn.connect(user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DB_NAME)
# use something more complex than select 1 or otherwise the query might
# complete in a single reactor cycle
d.addCallback(lambda _: conn.cursor().execute("select pg_sleep(0.1)"))
return d.addCallback(lambda _: conn.close())
def _connect(self, **kwargs):
from txpostgres import txpostgres, reconnection
from txpostgres.reconnection import DeadConnectionDetector
class LoggingDetector(DeadConnectionDetector):
def startReconnecting(self, f):
print("ERROR: database connection is down (error: {0})"
.format(f.value))
return DeadConnectionDetector.startReconnecting(self, f)
def reconnect(self):
print("INFO: Reconnecting...")
return DeadConnectionDetector.reconnect(self)
def connectionRecovered(self):
print("INFO: connection recovered")
return DeadConnectionDetector.connectionRecovered(self)
self.connection = txpostgres.Connection(detector=LoggingDetector())
d = self.connection.connect(host=kwargs['host'],
database=self.name,
user=kwargs['user'],
password=kwargs['password'])
d.addErrback(self.connection.detector.checkForDeadConnection)
d.addErrback(self.connectionError)
yield d
print("INFO: Database connected -- %s" %self.name)
def test_errors(self):
"""
Errors from psycopg2's poll() make connect() return failures. Errors on
creating the psycopg2 connection too. Unexpected results from poll()
also make connect() return a failure.
"""
conn = txpostgres.Connection()
def setFactory(conn, factory):
conn.connectionFactory = factory
class BadPollable(object):
closed = 1
def __init__(*args, **kwars):
pass
def poll(self):
raise RuntimeError("booga")
def close(self):
pass
setFactory(conn, BadPollable)
d = conn.connect()
d = self.assertFailure(d, RuntimeError)
d.addCallback(lambda _: conn.close())
class BadThing(object):
closed = 1
def __init__(*args, **kwargs):
raise RuntimeError("wooga")
def close(self):
pass
d.addCallback(lambda _: setFactory(conn, BadThing))
d.addCallback(lambda _: conn.connect())
d = self.assertFailure(d, RuntimeError)
class BrokenPollable(object):
closed = 1
def __init__(*args, **kwars):
pass
def poll(self):
return "tee hee hee"
def close(self):
pass
d.addCallback(lambda _: setFactory(conn, BrokenPollable))
d.addCallback(lambda _: conn.connect())
return self.assertFailure(d, txpostgres.UnexpectedPollResult)
def onJoin(self, details):
print(
"PostgreSQL database adapter [publisher role] connected to router")
dbconfig = self.config.extra['database']
## check if the config contains environment variables instead of
## straight strings (e.g. $DBNAME), and if so, try to fill in the actual
## value from environment
##
pat = re.compile("^\$([A-Z0-9_]+)$")
for k in ['host', 'port', 'database', 'user', 'password']:
if k in dbconfig:
if type(dbconfig[k]) in (str, unicode):
match = pat.match(dbconfig[k])
if match and match.groups():
envvar = match.groups()[0]
if envvar in os.environ:
dbconfig[k] = os.environ[envvar]
if k != 'password':
val = dbconfig[k]
else:
val = len(dbconfig[k]) * '*'
print(
"database configuration parameter '{}' set to '{}' from environment variable {}"
.format(k, val, envvar))
else:
print(
"warning: database configuration parameter '{}' should have been read from enviroment variable {}, but the latter is not set"
.format(k, envvar))
conn = txpostgres.Connection()
try:
yield conn.connect(**dbconfig)
except Exception as e:
print("could not connect to database: {0}".format(e))
self.leave()
return
else:
print(
"PostgreSQL database adapter [publisher role] connected to database"
)
conn.addNotifyObserver(self._on_notify)
try:
yield conn.runOperation("LISTEN {0}".format(
self.CHANNEL_PUBSUB_EVENT))
except Exception as e:
print("failed to listen on channel '{0}': {1}".format(
self.CHANNEL_PUBSUB_EVENT, e))
self.leave()
else:
print(
"ok, pusher is listening on PostgreSQL NOTIFY channel '{0}'' ..."
.format(self.CHANNEL_PUBSUB_EVENT))
def setUp(self):
self.notifyconn = txpostgres.Connection()
self.notifies = []
d = self.notifyconn.connect(user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DB_NAME)
return d.addCallback(lambda _: _SimpleDBSetupMixin.setUp(self))
def restoreConnection(self, res):
"""
Restore the connection to the database and return whatever argument has
been passed through. Useful as an addBoth handler for tests that
disconnect from the database.
"""
self.conn = txpostgres.Connection()
d = self.conn.connect(database='webber')
d.addErrback(log.err)
return d.addCallback(lambda _: res)
def connect_and_observe(self, db_config, channel, fun):
# connect to database
#
conn = txpostgres.Connection()
db_conn_params = {
u'user': db_config['user'],
u'password': db_config['password'],
u'host': db_config['host'],
u'port': db_config['port'],
u'database': db_config['database'],
}
try:
yield conn.connect(**db_conn_params)
except Exception as e:
raise Exception("database connection failed: {}".format(e))
else:
self.log.debug("Connected to database")
# acquire exclusive run lock
#
res = yield conn.runQuery("SELECT pg_try_advisory_lock(%s, %s)", (self.PG_LOCK_GROUP, db_config['adapter_xlock']))
if not res[0][0]:
locker_pid, locker_user_id, locker_user_name, locker_app_name = None, None, None, None
res = yield conn.runQuery("SELECT pid FROM pg_locks WHERE locktype = 'advisory' AND classid = %s AND objid = %s", (self.PG_LOCK_GROUP, db_config['adapter_xlock']))
if res:
locker_pid = res[0][0]
if locker_pid:
res = yield conn.runQuery("SELECT usesysid, usename, application_name FROM pg_stat_activity WHERE pid = %s", (locker_pid,))
if res:
locker_user_id, locker_user_name, locker_app_name = res[0]
self.log.error('A database session already holds the run lock for us (pid={pid}, userid={userid}, username={username}, appname="{appname}")', pid=locker_pid, userid=locker_user_id, username=locker_user_name, appname=locker_app_name)
raise Exception("Only one instance of this adapter can be connected to a given database")
else:
self.log.debug("Obtained exclusive run lock on ({key1}, {key2})", key1=self.PG_LOCK_GROUP, key2=db_config['adapter_xlock'])
# upgrade database schema if needed
#
schema_version = yield self._check_and_upgrade_schema(db_config, conn)
self.log.info("Running on schema version {schema_version}", schema_version=schema_version)
# add channel listener
#
conn.addNotifyObserver(fun)
try:
yield conn.runOperation("LISTEN {0}".format(self._db_config['adapter_channel']))
except Exception as e:
self.log.error("Failed to listen on channel '{0}': {1}".format(self._db_config['adapter_channel'], e))
self.leave()
else:
self.log.debug("Listening on PostgreSQL NOTIFY channel '{0}'' ...".format(self._db_config['adapter_channel']))
def __init__(self):
self.pw_check = PasswordHash().check_password
self._forums_conn = txpostgres.Connection()
self._forums_conn.connect(host=settings.phpbb_host,
database=settings.phpbb_db,
user=settings.phpbb_user,
password=settings.phpbb_pass)
self._lobby_conn = txpostgres.Connection()
self._lobby_conn.connect(host=settings.lobby_host,
database=settings.lobby_db,
user=settings.lobby_user,
password=settings.lobby_pass)
# Start the cleanup interval.
self._cleanup_call = LoopingCall(self._cleanup)
self._cleanup_call.start(settings.cleanup_interval, now=False)
# and run it once in 2 seconds.
reactor.callLater(2, self._cleanup)
def test_simpleConnection(self):
"""
Just connecting and disconnecting works.
"""
conn = txpostgres.Connection()
d = conn.connect(user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DB_NAME)
d.addCallback(lambda c: c.close())
return d
def restoreConnection(self, res):
"""
Restore the connection to the database and return whatever argument has
been passed through. Useful as an addBoth handler for tests that
disconnect from the database.
"""
self.conn = txpostgres.Connection()
d = self.conn.connect(user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DB_NAME)
return d.addCallback(lambda _: res)
def test_openRunCloseOpen(self):
conn = txpostgres.Connection()
connargs = dict(user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DB_NAME)
d = conn.connect(**connargs)
d.addCallback(lambda _: conn.runQuery("select 1"))
# make sure the txpostgres.Cursor created by runQuery got closed,
# otherwise it will still be polled and will result in an error
d.addCallback(lambda _: conn.close())
d.addCallback(lambda _: conn.connect(**connargs))
return d.addCallback(lambda _: conn.close())
def main(reactor):
conn = txpostgres.Connection()
db = yield conn.connect('dbname=postgres')
yield db.runOperation(_create_table)
#!
to_insert = ['teach PyYYC', 'find beers', '???', 'profit']
#!
for item in to_insert:
yield db.runOperation(
'INSERT INTO todo (todo, created_at) '
'VALUES (%s, NOW());',
[item],
)
def main(reactor, server='tcp:8000'):
pool = ConnectionPool(dbname='asdf')
conn = txpostgres.Connection()
yield gatherResults([
pool.start(),
conn.connect(dbname='asdf', cursor_factory=NamedTupleCursor)
])
res = yield makeEntryPoint(pool, conn)
site = Site(res)
ep = serverFromString(reactor, server)
ep.listen(site)
yield Deferred() # wait forever
def connect_raw(db_name, db_user, db_pass):
""" Connect to the database bypassing the connection pool (e.g., for adding a notify observer). """
try:
conn_str = ("dbname='{0}' user='******' password='******' host='{3}' port='{4}'".format(db_name or POSTGRES_DB, db_user, db_pass, HOST, PORT))
conn = txpostgres.Connection()
connection = conn.connect(conn_str)
return connection
except Exception as e:
logging.error("DB: Error connecting to {0} as {1} ({2})".format(db_name, db_user, e))
d = Deferred()
failure = Failure(e)
d.errback(failure)
return d
def test_sameObserverAddedTwice(self):
"""
Adding the same observer twice results in just one registration.
"""
c = txpostgres.Connection()
def observer(notify):
pass
self.assertEquals(len(c.getNotifyObservers()), 0)
c.addNotifyObserver(observer)
c.addNotifyObserver(observer)
self.assertEquals(len(c.getNotifyObservers()), 1)
def _newConnection(self, conn_str):
""" Makes a new connection to the DB
and then puts it in the 'free' pool of this conn_str.
"""
logging.debug("IndxConnectionPool _newConnection")
# lock with the semaphore before calling this
return_d = Deferred()
def close_old_cb(failure):
failure.trap(psycopg2.OperationalError, Exception)
# couldn't connect, so close an old connection first
logging.error(
"IndxConnectionPool error close_old_cb: {0} - state of conns is: {1}"
.format(failure.value, self.connections))
logging.error("IndxConnectionPool connections: {0}".format(
"\n".join(
map(lambda name: self.connections[name].__str__(),
self.connections))))
def closed_cb(empty):
# closed, so try connecting again
self._newConnection(conn_str).addCallbacks(
return_d.callback, return_d.errback)
closed_d = self._closeOldConnection()
closed_d.addCallbacks(closed_cb, return_d.errback)
try:
# try to connect
def connected_cb(connection):
logging.debug(
"IndxConnectionPool _newConnection connected_cb, connection: {0}"
.format(connection))
self.connections[conn_str].getFree().append(connection)
return_d.callback(connection)
conn = txpostgres.Connection()
connection_d = conn.connect(conn_str)
connection_d.addCallbacks(connected_cb, close_old_cb)
except Exception as e:
# close an old connection first
logging.debug(
"IndxConnectionPool Exception, going to call close_old_cb: ({0})"
.format(e))
close_old_cb(Failure(e))
return return_d
def test_closeTwice(self):
"""
Calling close() on the connection twice does not result in an error.
"""
conn = txpostgres.Connection()
d = conn.connect(user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DB_NAME)
def closeTwice(_):
conn.close()
conn.close()
d.addCallback(closeTwice)
return d.addCallback(lambda _: self.assertTrue(conn.closed))
def main(reactor):
conn = txpostgres.Connection()
db = yield conn.connect('dbname=postgres')
yield db.runOperation(_create_table)
to_insert = ['teach PyYYC', 'find beers', '???', 'profit']
for item in to_insert:
yield db.runOperation(
'INSERT INTO todo (todo, created_at) '
'VALUES (%s, NOW());',
[item],
)
#!
answer = yield db.runQuery('SELECT * from todo')
print("TODOs:")
for id_, text, created in answer:
print(" {}: '{}' at '{}'".format(id_, text, created))
def checkErrorAndHotswap(f):
f.trap(txpostgres.RollbackFailed)
e = f.value
self.assertIdentical(e.connection.cursorFactory,
NotRollingBackCursor)
errors = self.flushLoggedErrors()
self.assertEquals(len(errors), 1)
self.assertEquals(errors[0].value.args[0], "boom")
pool.remove(e.connection)
e.connection.close()
c = txpostgres.Connection()
self.assertNotIdentical(c.cursorFactory, NotRollingBackCursor)
d = c.connect(user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DB_NAME)
return d.addCallback(lambda c: pool.add(c))
def test_multipleConnections(self):
"""
Trying to connect twice raises an exception, but after closing you can
connect again.
"""
conn = txpostgres.Connection()
d = conn.connect(user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DB_NAME)
d.addCallback(lambda c: conn.connect(
user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME))
d = self.failUnlessFailure(d, txpostgres.AlreadyConnected)
d.addCallback(lambda _: conn.close())
d.addCallback(lambda _: conn.connect(
user=DB_USER, password=DB_PASS, host=DB_HOST, database=DB_NAME))
return d.addCallback(lambda c: c.close())
def test_connectionSetup(self):
"""
The created connection should be asynchronous and in autocommit mode
and the C{Deferred} returned from connect() should fire with the
connection itself.
"""
conn = txpostgres.Connection()
d = conn.connect(user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DB_NAME)
def doChecks(c):
self.assertIdentical(c, conn)
self.assertTrue(c. async)
self.assertEquals(c.isolation_level, 0)
return c
d.addCallback(doChecks)
return d.addCallback(lambda c: c.close())
def onJoin(self, details):
print("session joined")
config = self.config.extra
conn = txpostgres.Connection()
try:
yield conn.connect(**config['database'])
except Exception as e:
print("could not connect to database: {0}".format(e))
self.leave()
return
else:
print("connected to database")
conn.addNotifyObserver(self._on_notify)
try:
yield conn.runOperation("LISTEN {0}".format(self.CHANNEL_PUBSUB_EVENT))
except Exception as e:
print("failed to listen on channel '{0}': {1}".format(self.CHANNEL_PUBSUB_EVENT, e))
self.leave()
else:
print("ok, pusher is listening on PostgreSQL NOTIFY channel '{0}'' ...".format(self.CHANNEL_PUBSUB_EVENT))
def test_removeNonexistentObserver(self):
"""
Removing an observer twice is valid and results in the observer being
removed. Removing one that does not exist at all is valid as well.
"""
c = txpostgres.Connection()
def observer1(notify):
pass
def observer2(notify):
pass
c.addNotifyObserver(observer1)
c.addNotifyObserver(observer2)
self.assertEquals(len(c.getNotifyObservers()), 2)
c.removeNotifyObserver(observer1)
c.removeNotifyObserver(observer1)
c.removeNotifyObserver(lambda _: _)
self.assertEquals(len(c.getNotifyObservers()), 1)
self.assertIn(observer2, c.getNotifyObservers())
def main(reactor):
conn = txpostgres.Connection()
db = yield conn.connect('dbname=postgres')
def get_db_connection():
conn = txpostgres.Connection()
d = conn.connect('host=localhost port=5432 user=postgres dbname=jsonstore')
return conn, d
from txpostgres import txpostgres
from twisted.internet import reactor
from twisted.python import log, util
# connect to the database
conn = txpostgres.Connection()
d = conn.connect('dbname=postgres')
# run the query and print the result
d.addCallback(lambda _: conn.runQuery('select tablename from pg_tables'))
d.addCallback(lambda result: util.println('All tables:', result))
# close the connection, log any errors and stop the reactor
d.addCallback(lambda _: conn.close())
d.addErrback(log.err)
d.addBoth(lambda _: reactor.stop())
# start the reactor to kick off connection estabilishing
reactor.run()
def error(f):
print '-> query failed with %r' % f.value
def connectionError(f):
print '-> connecting failed with %r' % f.value
def runLoopingQuery(conn):
d = conn.runQuery('select 1')
d.addCallbacks(result, error)
def connected(_, conn):
print '-> connected, running a query periodically'
lc = task.LoopingCall(runLoopingQuery, conn)
return lc.start(2)
# connect to the database using reconnection
conn = txpostgres.Connection(detector=LoggingDetector())
d = conn.connect('dbname=postgres')
# if the connection failed, log the error and start reconnecting
d.addErrback(conn.detector.checkForDeadConnection)
d.addErrback(connectionError)
d.addCallback(connected, conn)
# process events until killed
reactor.run()
