creating the postgres engine with the separate QueuePool, like youre doing, is a good idea to get a better result here. the "threadlocal" strategy uses this silly "TLEngine" subclass which is only trying to do that old "engine.begin()/engine.commit()" thing, which id rather get rid of, as soon as people tell me they can use the Transaction object that whole thing is gone. if youre having weird transactions hanging open types of problems i would suspect that might be where its happening, as it does try to maintain state (any chance I can get a fully working test program ?). The regular ComposedSQLEngine does not maintain any state (the underlying pool of course does).

anyway your problem using the pool is just the "pool" argument not being propigated, and also there needed to be an exception raise for a bad URL, which if you SVN up to changeset 1533 youll get. the program below illustrates how to connect to postgres with your own pool:

from sqlalchemy import *

import psycopg2
def getconn():
return psycopg2.connect(database='test', user='scott', password='tiger', host='127.0.0.1')
import sqlalchemy.pool as pool
p = pool.QueuePool(getconn, pool_size=20, max_overflow=40)
e = create_engine('postgres://', pool=p, convert_unicode=True)
c = e.connect()
print c.scalar("select now()", {})

please try it out and let me know how it goes. the QueuePool in version 0.2 had to change slightly from its design in 0.1 to support "checkin/checkout" counters, as a result of the new requirement that people can explicitly call "close()" on a returned ConnectionFairy as opposed to waiting for __del__() to take care of things cleanly...other than that it is the same code. The regular ComposedSQLEngine in turn doesnt have anything to say about those connections, its totally stateless. anything producing a deadlock is something i need to know about and be able to reproduce ASAP....thanks.


On May 28, 2006, at 7:24 AM, Valentino Volonghi aka Dialtone wrote:



On Sat, 27 May 2006 20:36:56 -0400, Michael Bayer <[EMAIL PROTECTED]> wrote:
sure, right here:

http://www.sqlalchemy.org/docs/ dbengine.myt#dbengine_connections_context

just switch on 'create_engine('foo', strategy="threadlocal")' and youve got identical 0.1 behavior.

Not really since I tried that and it deadlocks inside postgres because of idle transactions.

def _transact(engine):
   """
   return an high level function bind to a particulary
   sqlalchemy engine

   this function is suitable to use as a decorator

   a decorated function returns a Deferred while its body
   is running in a separate thread
   """
   def _transaction(f):
       # _transaction *is* the decorator
       def _wrap(*args, **kwargs):
           def _1():
               conn = engine.contextual_connect()
               trans = conn.begin()
               try:
                   r = f(*args, **kwargs)
               except:
                   trans.rollback()
                   conn.close()
                   raise
               else:
                   trans.commit()
                   conn.close()
                   return r
           return threads.deferToThread(_1)
       _wrap.__name__ = f.__name__
       return _wrap
   return _transaction

I thought this would work in threadlocal mode but it doesn't.

Not only that but:

def getconn():
... return psycopg2.connect(database='blabla2', user='blalbla1', password='blabla')
p = sq.pool.QueuePool(getconn, pool_size=20, max_overflow=40)
sq.create_engine('postgres', pool=p, convert_unicode=True)
Traceback (most recent call last):
 File "<stdin>", line 1, in ?
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/engine/ __init__.py", line 92, in create_engine
   return strategy.create(*args, **kwargs)
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/engine/ strategies.py", line 30, in create
   module = u.get_module()
AttributeError: 'NoneType' object has no attribute 'get_module'

Seems to be caused by the regexp in url.make_url()



Using 'postgres:///' in create_engine causes...

e.connect()
Traceback (most recent call last):
 File "<stdin>", line 1, in ?
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/engine/ base.py", line 450, in connect
   return Connection(self, **kwargs)
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/engine/ base.py", line 177, in __init__
   self.__connection = connection or engine.raw_connection()
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/engine/ base.py", line 473, in raw_connection
   return self.connection_provider.get_connection()
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/engine/ default.py", line 34, in get_connection
   return self._pool.connect()
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/pool.py", line 84, in connect
   return ConnectionFairy(self).checkout()
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/pool.py", line 130, in __init__
   self.connection = pool.get()
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/pool.py", line 102, in get
   return self.do_get()
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/pool.py", line 227, in do_get
   return self._creator()
File "/Volumes/dati/Sviluppo/sqlalchemy/lib/sqlalchemy/engine/ default.py", line 27, in <lambda> self._pool = poolclass(lambda: dbapi.connect(*cargs, **cparams), **kwargs)
psycopg2.OperationalError: FATAL:  database "host=" does not exist


To close up, don't take this personally please because sqlalchemy is anyway wonderful, but as things are currently going with 0.1-- >0.2 migration the pain in the butt is sqlalchemy and not twisted.


-------------------------------------------------------
All the advantages of Linux Managed Hosting--Without the Cost and Risk! Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel? cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users



-------------------------------------------------------
All the advantages of Linux Managed Hosting--Without the Cost and Risk!
Fully trained technicians. The highest number of Red Hat certifications in
the hosting industry. Fanatical Support. Click to learn more
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=107521&bid=248729&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users

Reply via email to