On Mon, 2011-11-14 at 13:41 +0100, Vlad K. wrote:
> Here:
>
> https://gist.github.com/1363860
>
> This simple test case works just fine, meaning the problem is somewhere
> in my application. As you can see from the code, I tried both a
> "standalone" test and through Pyramid WSGI chain checking if pyramid_tm
> middleware possibly borks something somewhere, but both cases worked fine.
I think the issue here is that (for better or worse), calling
transaction.commit() causes the session to be closed, which detaches the
objects obtained from that session. The following iteration on your
script actually completes (note that I requery for "tm" after "second
pass")....
import transaction
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import Unicode
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
from zope.sqlalchemy import ZopeTransactionExtension
DBSession =
scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()
class TestModel(Base):
__tablename__ = "test"
id = Column(Integer, primary_key=True, autoincrement=False)
name = Column(Unicode)
value = Column(Integer)
def __init__(self, id):
self.id = id
self.name = u"Hello World!"
self.value = 123
def initialize_sql(engine):
DBSession.configure(bind=engine)
Base.metadata.bind = engine
Base.metadata.create_all(engine)
def insert_or_update():
session = DBSession()
savepoint = transaction.savepoint()
try:
# Try insert as new
tm = TestModel(1)
session.add(tm)
transaction.commit()
print "INSERTED"
except IntegrityError:
transaction.abort()
# Update instead (part of outer transaction)
session.query(TestModel)\
.filter_by(id=1)\
.update({"name" : u"Hello!", "value" : 123})
print "UPDATED"
def my_view(request):
session = DBSession()
print "Outer transaction..."
tm = session.query(TestModel).get(999) or TestModel(999)
tm = session.merge(tm)
print "First pass..."
insert_or_update()
print "Second pass..."
insert_or_update()
tm = session.query(TestModel).get(999) or TestModel(999)
print "tm.name:", tm.name
print "Committing..."
transaction.commit()
print "Testing..."
tm = session.query(TestModel).get(1)
print "1:", "ok" if tm else "not found"
tm = session.query(TestModel).get(999)
print "999:", "ok" if tm else "not found"
tm = session.query(TestModel).get(100)
print "100:", "error" if tm else "ok"
return "Check console output..."
def main(global_config, **settings):
engine = create_engine("postgresql://test:test@localhost:5432/test")
initialize_sql(engine)
my_view(None)
"""
config = Configurator(settings=settings)
config.add_route('home', '/')
config.add_view(my_view, route_name='home', renderer='string')
return config.make_wsgi_app()
"""
if __name__ == "__main__":
main(None)
>
> However, I can't really replicate a test scenario fully because the
> problematic part of my application involves several models with
> relationships, multiple updates. If I use EXACTLY the same flow for the
> savepoint, ie:
>
> sp = transaction.savepoint()
> try:
> ... # Try insert
> transaction.commit()
> except IntegrityError:
> transaction.abort()
> ... # Try Update
>
> Then the result is:
>
>
>
> 2011-11-14 12:58:29,689 INFO [sqlalchemy.engine.base.Engine][worker 0]
> BEGIN (implicit)
>
> ... SELECTs and UPDATEs
>
> 2011-11-14 12:58:29,726 INFO [sqlalchemy.engine.base.Engine][worker 0]
> SAVEPOINT sa_savepoint_1
> 2011-11-14 12:58:29,726 INFO [sqlalchemy.engine.base.Engine][worker 0] {}
> 2011-11-14 12:58:29,727 INFO [sqlalchemy.engine.base.Engine][worker 0]
> INSERT INTO user_stats (user_id, tstamp, portal, ads_new, ads_modified,
> ads_deleted) VALUES (%(user_id)s, %(tstamp)s, %(portal)s, %(ads_new)s,
> %(ads_modified)s, %(ads_deleted)s)
> 2011-11-14 12:58:29,727 INFO [sqlalchemy.engine.base.Engine][worker 0]
> {'user_id': 1, 'ads_new': 0, 'ads_deleted': 0, 'tstamp':
> datetime.date(2011, 11, 14), 'ads_modified': 0, 'portal': 'test'}
> 2011-11-14 12:58:29,728 INFO [sqlalchemy.engine.base.Engine][worker 0]
> ROLLBACK TO SAVEPOINT sa_savepoint_1
> 2011-11-14 12:58:29,728 INFO [sqlalchemy.engine.base.Engine][worker 0] {}
> 2011-11-14 12:58:29,754 INFO [sqlalchemy.engine.base.Engine][worker 0]
> BEGIN (implicit)
> 2011-11-14 12:58:29,756 INFO [sqlalchemy.engine.base.Engine][worker 0]
> UPDATE user_stats SET ads_new=(user_stats.ads_new + %(ads_new_1)s),
> ads_modified=(user_stats.ads_modified + %(ads_modified_1)s),
> ads_deleted=(user_stats.ads_deleted + %(ads_deleted_1)s) WHERE
> user_stats.user_id = %(user_id_1)s AND user_stats.portal = %(portal_1)s
> AND user_stats.tstamp = %(tstamp_1)s
> 2011-11-14 12:58:29,756 INFO [sqlalchemy.engine.base.Engine][worker 0]
> {'ads_deleted_1': 0, 'portal_1': 'test', 'ads_new_1': 0, 'tstamp_1':
> datetime.date(2011, 11, 14), 'user_id_1': 1, 'ads_modified_1': 1}
> 2011-11-14 12:58:29,956 ERROR [pyramid_debugtoolbar][worker 0] Exception
> at http://localhost:6543/ads/view/28328?category=
> ...
> DetachedInstanceError: Parent instance <AdLand at 0x7ffb900faa90> is not
> bound to a Session; lazy load operation of attribute 'ad_base' cannot
> proceed
>
>
>
>
> The exception being thrown when I, after the savepoint fail, try to READ
> a value from a model read/updated within the outer transaction. The process:
>
> 0. enter view
> 1. session = DBSession()
> 2. load some models
> 3. change them with POSTed data (including adding/removing/updating
> models through relationship class members)
> 4. session.flush() changes (possibly redundant because savepoint
> unconditionally flushes)
> 5. make savepoint, try insert or update some statistical models (which
> are not used or in any way related with those from the outer
> transaction), commit or abort the savepoint
> 6. read some data from models loaded in step 2 (reading again here
> because flush() in step 4 returns new IDs etc... so another phase of
> update* is required)
> 7. session.flush() everything again
> 8. exit view
>
> *update involving external resources like processing related uploaded
> files which names depend on IDs and other data obtained after flush in
> step 4
>
>
> The DetachedInstanceError is thrown at step 6, more precisely:
>
>
>
>
> if not ad.ad_base.main_image_id and len(ad.ad_base.images):
> ...
>
>
>
>
> where ad is a "main" model, ad.ad_base is a relationship() with parent
> (of another class), main_image_id is integer and ad.ad_base.images is a
> relationship to yet another model. The DetachedInstance means the 'ad'
> being detached, so ad.ad_base is void.
>
>
> So somehow the transaction of the savepoint messes up with the external
> session, even though there is no relationship between those and the
> models inserted/updated within the inner transaction. No foreignkey or
> relationship() connection.
>
>
>
>
> .oO V Oo.
>
>
> On 11/14/2011 12:04 PM, Vlad K. wrote:
> >
> > Sure, I'll whip something up.
> >
> >
> > Thanks.
> >
> >
> > .oO V Oo.
> >
> >
> > On 11/14/2011 06:01 AM, Chris McDonough wrote:
> >> I hate to ask, but would it be possible to concoct a short Python script
> >> that creates a SQLA DBSession (with the ZopeTransactionExtension
> >> enabled) that issues transaction.commit/savepoint/abort commands that
> >> demonstrates the problem being encountered? I'd be guessing if I tried
> >> to help with this without that.
> >>
> >> - C
> >>
> >>
> >
>
--
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en.
