zopefoundation / zope.sqlalchemy Goto Github PK

A small bug with the new psycopg 3 backend has reared its head in production, apparently sqlstate can be None for certain rare client side exceptions (e.g. server closed connection unexpectedly), so the following check needs to be modified in order to be completely safe:

The lambda should be changed to: lambda e: e.sqlstate and e.sqlstate.startswith('40'))

@icemac Considering this is such a small change I hope you can just quickly do it yourself, thanks!

I know a release was just made a few days ago to deal with issues of nested sqlalchemy transactions, but I think there are still a few cases that need to be addressed (though I'm not sure if they should be done here or in sqlalchemy).

One issue is with creating the save point through the transaction manager instead of with sqlalchemy's begin_nested() function. I'm not really familiar with how the two differ except for this particular issue:

If I use transaction.savepoint() and then any subsequent sqlalchemy DB calls which result in an IntegrityError cause an immediate ROLLBACK without referring to the savepoint. If I use the session.begin_nested instead, sqlalchemy recognizes it's in the middle of a sub-transaction and rolls back to the appropriate savepoint.

The most recent release now makes it so after a session.begin_nested() you can actually call session.commit() (I think, I haven't tested it yet), but there still exists an issue when using transaction.savepoint.

Hi there,
I found a very disturbing problem in multi-threaded application. What happens:

1. Threads 1 and 2 both read record A.
2. Thread 1 updates record A and commits
3. Thread 2 calls session.merge() - to read an updated version of record A
4. BUT! when transaction.commit() is called in Thread 2, record A is updated back to the previous state, essentially reverting all changes made by Thread 1.

This doesn't happen when not using ZopeTransactionExtension (i.e. using only SQLAlchemy and it's session.commit()). Also using ZopeTransactionExtension('changed') fixes the problem.
Sample code below demonstrates the problem (see comments in function thread_2). Only dependencies are sqlalchemy and zope.sqlalchemy, it uses in-memory slqite.
The problem was observed on SQLAlchemy 8.1 and also the latest 9.4 (haven't tested any other versions), Zope version 0.7.2 and latest 0.7.4.
SQLite is used in the example, but the same problem happens on postgresql, too.

import transaction
from time import sleep
from sqlalchemy.engine import engine_from_config
from sqlalchemy.orm.scoping import scoped_session
from zope.sqlalchemy.datamanager import ZopeTransactionExtension
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.ext.declarative.api import declarative_base
from threading import Thread
from sqlalchemy.schema import Column, Sequence
from sqlalchemy.types import Integer, String


settings = {
    # works also on postgresql (and probably any other db server)
    #'sqlalchemy.url': "postgresql://user:pass@localhost/db-dev",
    'sqlalchemy.url': "sqlite:///memory:",
    'sqlalchemy.echo': True}

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
# using the below definition fixes the problem (with some side-effects though)
#DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension('changed')))
Base = declarative_base()


class TestTable(Base):
    __tablename__ = "test_table"
    test_id = Column(Integer, Sequence('test_table_id_seq'), primary_key=True)
    value = Column(String)
    unrelated = Column(String)


def thread_1(test_id):
    dbs = DBSession()
    o = dbs.query(TestTable).get(test_id)
    sleep(0.5)  # update after 0.5s -> enough time for the second thread to read from database
    o.value = '123'
    transaction.commit()


def thread_2(test_id):
    dbs = DBSession()
    o = dbs.query(TestTable).get(test_id)
    sleep(1)  # by this time thread 1 should be finished and changes committed in the db

    # if anything (unrelated) gets updated, the problem doesn't happen
    # try uncommenting the below line and see the different output
    #o.unrelated = '456'
    transaction.commit()
    dbs = DBSession()
    o = dbs.merge(o)
    print "order num after merge " + o.value  # this should print "123", but prints "initial" <- that's wrong!
    transaction.commit()  # and then it updates value "123" back to "initial" (!!!)


if __name__ == '__main__':
    # setup sqlalchemy
    engine = engine_from_config(settings)
    Base.metadata.create_all(engine)
    DBSession.configure(bind=engine)
    Base.metadata.bind = engine

    # create one record in our test table
    dbs = DBSession()
    rec = TestTable(value='initial', unrelated='initial')
    dbs.add(rec)
    dbs.flush()
    test_id = rec.test_id
    transaction.commit()

    # now run two threads parallelly
    t1 = Thread(target=thread_1, args=(test_id,))
    t2 = Thread(target=thread_2, args=(test_id,))
    t1.start()
    t2.start()

    # wait for both of them to finish
    t1.join()
    t2.join()

Just upgraded zope.sqlalchemy from pip and it broke the world.

    from zope.sqlalchemy import ZopeTransactionExtension
ImportError: cannot import name 'ZopeTransactionExtension' from 'zope.sqlalchemy'

See https://github.com/zopefoundation/zope.sqlalchemy/actions/runs/992650946
We probably have to pin psycopg2 to an older version for the Python versions having problems.

Newer versions fail with some errors, see #17

I get this error rarely, but it happens. I have no idea how to debug it.

databaselayer/blog.py", line 449, in set_notifications_as_read
).limit(limit).update({'is_read': True})
local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2735, in update
update_op.exec_()
local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 868, in exec_
self._do_post()
local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 968, in _do_post
session.dispatch.after_bulk_update(self)
local/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 257, in __call__
fn(*args, **kw)
local/lib/python2.7/site-packages/sqlalchemy/event/legacy.py", line 36, in wrap_leg
return fn(*conv(*args))
local/lib/python2.7/site-packages/zope.sqlalchemy-0.7.4-py2.7.egg/zope/sqlalchemy/datamanager.py", line 237, in after_bulk_update
mark_changed(session, self.transaction_manager, self.keep_session)
local/lib/python2.7/site-packages/zope.sqlalchemy-0.7.4-py2.7.egg/zope/sqlalchemy/datamanager.py", line 210, in mark_changed
join_transaction(session, STATUS_CHANGED, transaction_manager, keep_session)
local/lib/python2.7/site-packages/zope.sqlalchemy-0.7.4-py2.7.egg/zope/sqlalchemy/datamanager.py", line 202, in join_transaction
DataManager(session, initial_state, transaction_manager, keep_session=keep_session)
local/lib/python2.7/site-packages/zope.sqlalch
local/lib/python2.7/site-packages/zope.sqlalchemy-0.7.4-   py2.7.egg/zope/sqlalchemy/datamanager.py", line 65, in __init__
self.tx = session.transaction._iterate_parents()[-1]
AttributeError: 'NoneType' object has no attribute '_iterate_parents'

Currently only 3.3 is officially supported and tested.

After #19 is merged new versions can be added.

If you do not have your software classified with an ECCN, please kindly answer the following questions so that we may self-assess if there may be any prohibition or restriction for export or re-export of this product.
 Does the Software perform any encryption or utilize any encryption processes? Y/N
I. If the answer is YES to question (i), please indicate if the encryption is coded into the application or separately called (such as using SSL)
II. If the answer is YES to question (i), please indicate what function(s) the cryptography/encryption serves
A. Copyright protection purposes (Includes using a license key/code)
B. User authentication purposes
C. A core part of the functionality such as to encrypt databases
D. To encrypt communications between the software and a host system.

• run tests on travis-ci
• fix failing test (using sqlalchemy 0.7.x):

======================================================================
FAIL: /tmp/nix-build-pypy2.3-zope.sqlalchemy-0.7.5.drv-0/zope.sqlalchemy-0.7.5/src/zope/sqlalchemy/README.txt
Doctest: README.txt
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/nix/store/p4rfgk7an0x6vb04v48vzb4r0z3b84cw-pypy-2.3.1/pypy-c/lib-python/2.7/doctest.py", line 2201, in runTest
    raise self.failureException(self.format_failure(new.getvalue()))
AssertionError: Failed doctest test for README.txt
  File "/tmp/nix-build-pypy2.3-zope.sqlalchemy-0.7.5.drv-0/zope.sqlalchemy-0.7.5/src/zope/sqlalchemy/README.txt", line 0

----------------------------------------------------------------------
File "/tmp/nix-build-pypy2.3-zope.sqlalchemy-0.7.5.drv-0/zope.sqlalchemy-0.7.5/src/zope/sqlalchemy/README.txt", line 177, in README.txt
Failed example:
    str(session.query(User).all()[0].name)
Expected:
    'bob'
Got:
    'ben'

When the default setting of keep_session=False is used this isn't an issue, because the connection pool will reset the connection before it is used again. It seems like the session would need to be rolled back when keep_session=True is used.

I see that the two phase data manager issues a rollback in tpc_abort().

Is this (keep_session=True, twophase=True) just a configuration that nobody uses (perhaps for some reason I've overlooked)?

I'm trying to run a python file for a lecture, but I keep getting an error:

AirvanGruiker18:src3 gebruiker$ python list.py
Traceback (most recent call last):
  File "list.py", line 6, in <module>
    engine = create_engine(os.getenv("DATABASE_URL"))
  File "/Users/gebruiker/Library/Python/2.7/lib/python/site-packages/sqlalchemy/engine/__init__.py", line 488, in create_engine
    return strategy.create(*args, **kwargs)
  File "/Users/gebruiker/Library/Python/2.7/lib/python/site-packages/sqlalchemy/engine/strategies.py", line 56, in create
    plugins = u._instantiate_plugins(kwargs)
AttributeError: 'NoneType' object has no attribute '_instantiate_plugins'

Does anybody know what the problem is and how I can fix it?

Currently tests fail when running against 2.x, so we have to pin SQLAlchemy to < 2 for now.

See #64 as preparation for this issue.

Version 1.2 was released with a number of breaking changes, breaking projects (eg. Kinto/kinto#2296)

(Drop support for Python 3.4, renamed the ZopeTransactionExtension class to ZopeTransactionEvents)

Would you mind following SemVer for following releases please?

Maybe it's just me, but I really find it a regression when I have code linting fail on silly little details. IMHO it is much better to just have black take care of of all that and never think about it again. So is there an opinion on adding black to the toolcahin, and adjusting flake8 and isort config to be compaitbale with default black settings?

I just had a very stressfull weekend because an update to zope.sqlalchemy slipped our quality control and horribly broke our production application.

Turns out the reason was that #8 introduced a new 'feature' that seems like it's very wrong.

Here's a testcase:

from sqlalchemy.engine import engine_from_config
from sqlalchemy.orm import scoped_session
from zope.sqlalchemy import ZopeTransactionExtension
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column
from sqlalchemy import ForeignKey, Integer, String
from sqlalchemy.orm import relationship, backref
import transaction

import sys

demonstrate_error_with_zope_sqlalchemy = len(sys.argv) == 1 and sys.argv[1] == '--demonstrate-error'

settings = {
    # works also on postgresql (and probably any other db server)
    #'sqlalchemy.url': "postgresql://user:pass@localhost/db-dev",
    'sqlalchemy.url': "sqlite:///:memory:",
    'sqlalchemy.echo': True}

if demonstrate_error_with_zope_sqlalchemy:
    DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
else:
    DBSession = scoped_session(sessionmaker())

Base = declarative_base()


class Tree(Base):
    __tablename__ = "tree"
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(id))
    children = relationship('Tree', backref=backref('parent', remote_side=[id]))

# setup sqlalchemy
engine = engine_from_config(settings)
Base.metadata.create_all(engine)
DBSession.configure(bind=engine)
Base.metadata.bind = engine

# create one record in our test table
session = DBSession()
root = Tree(children=[Tree(), Tree()])
session.add(root)
session.flush()
if demonstrate_error_with_zope_sqlalchemy:
    transaction.commit()
else:
    session.commit()

# now access the children
print root.children[0]

If you call this script with --demonstrate-error, it will throw on the last line a
sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Tree at 0x10430e7d0> is not bound to a Session; lazy load operation of attribute 'children' cannot proceed

The problem, as far as I understand it for now, is that I transaction.commit() behaves differently than session.commit() does.

Now maybe there is a rationale behind this why it should behave differently, but then I don't get it. Why should I not be able to use the object I have already loaded to change / get something from it again after the first commit? Maybe read some data out from it to display it on the website that is displayed after the successful commit?

If there is no overarching reason for this, then the fix for #8 is probably not the right fix and a new one needs to be found. (Sorry, I'm not so deep in sqlalchemy that I know what that would be).

Hello,

I'm upgrading an application which is using Pyramid and thus zope.sqlalchemy for the transaction management. It looks like there is an issue with ORM-enabled updates, which is a SQLAlchemy 1.4 thing (https://docs.sqlalchemy.org/en/14/orm/session_basics.html#update-and-delete-with-arbitrary-where-clause see 2.0 style, Session.execute()).

What I did:

The underlying engine and session is configured as the following: https://github.com/silenius/amnesia/blob/sqlalchemy_1_4/amnesia/db/__init__.py

My view statement is https://github.com/silenius/amnesia/blob/sqlalchemy_1_4/amnesia/modules/content/views/move.py#L25-L41

which call https://github.com/silenius/amnesia/blob/sqlalchemy_1_4/amnesia/modules/content/resources.py#L122-L166 under the hood.

What I expect to happen:

transaction gets commited

What actually happened:

It looks like doing an UPDATE at the ORM layer does not make the transaction dirty and thus it's always rolled back

What version of Python and Zope/Addons I am using:

This is with Python 3.7.9 running on FreeBSD 13 with a PostgreSQL database and:

pyramid-retry==2.0
pyramid-tm==2.4
pyramid==2.0
psycopg2==2.8.5
zope.sqlalchemy==1.4
SQLAlchemy==1.4.20
transaction==3.0.1

I also tried with #66 but it doesn't make any difference

src/zope/sqlalchemy/README.rst should link to http://www.zodb.org/en/latest/#transactions rather than http://www.zodb.org/zodbbook/transactions.html

Zope.sqlalchemy is calling session.transaction._iterate_parents() in datamanger.py. Unfortunately, this method has been renamed to _iterate_self_and_parents() in SQLAlchemy 1.1 (see here).

So this should be fixed somehow or things will break when SQLAlchemy 1.1 will be release (soon).

BUG/PROBLEM REPORT (OR OTHER COMMON ISSUE)

What I did:

I tried to use transaction.savepoint() with sqlite through sqlalchemy.

What I expect to happen:

that it works, since sqlite seems to support savepoint just fine: https://sqlite.org/lang_savepoint.html

What actually happened:

it raised
TypeError: ('Savepoints unsupported', <zope.sqlalchemy.datamanager.SessionDataManager object at 0x11dde5f50>)

What version of Python and Zope/Addons I am using:

• python Python 2.7.16 (yes I know)
• transaction 3.0.0

See https://travis-ci.org/zopefoundation/zope.sqlalchemy/builds/139672322:

Failure in test testTransactionJoining (zope.sqlalchemy.tests.ZopeSQLAlchemyTests)
Traceback (most recent call last):
  File "/opt/python/3.3.6/lib/python3.3/unittest/case.py", line 384, in _executeTestPart
    function()
  File "/home/travis/build/zopefoundation/zope.sqlalchemy/src/zope/sqlalchemy/tests.py", line 344, in testTransactionJoining
    "Not joined transaction")
  File "/opt/python/3.3.6/lib/python3.3/unittest/case.py", line 520, in assertTrue
    raise self.failureException(msg)
AssertionError: [] is not true : Not joined transaction

(and a few other test failures with the same assertion error)

session.transaction is deprecated in sqlalchemy 2.0, see here: https://docs.sqlalchemy.org/en/14/orm/session_api.html?highlight=get_transaction#sqlalchemy.orm.Session.get_transaction

So we can use session.get_transaction() instead (see #59), but unfortunately this in only available from sqlalchemy 1.4. The minimum supported version is now 0.9, so that's quite a big jump. There probably will be more breaking changes with the transition to 2.0, just haven't run into them yet. Is there a strategy to deal with that?

My suggestion would be to just update the minimum required version to 1.4 and perhaps fork the latest version with support from 0.9 into some kind of legacy LTS branch.

Following SQLAchemy's documentation, savepoint with SQLite is supported (excluding some minro issues with the pysqlite driver where a workaround is avaible).

When trying to use SAVEPOINT with SQLite, the following error is raised:

Traceback (most recent call last):
  File "/lib/python3.7/site-packages/transaction/_transaction.py", line 631, in __init__
    savepoint = datamanager.savepoint
  File "/lib/python3.7/site-packages/zope/sqlalchemy/datamanager.py", line 155, in savepoint
    raise AttributeError("savepoint")
AttributeError: savepoint

This is due because SQLite is explicitly declared as not supported:

However, if I remove sqlite from the set, my transaction is acting as expected:

[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:711] BEGIN (implicit)
[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:1205] SELECT xxx
[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:1210] ()
[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:1205] SAVEPOINT sa_savepoint_1
[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:1210] ()
[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:1205] INSERT INTO xxx
[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:1210] ()
[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:1205] ROLLBACK TO SAVEPOINT sa_savepoint_1
[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:1210] ()
[2020-03-30 21:01:12 - INFO/sqlalchemy.engine.base.Engine:1205] SELECT xxx

My environment:

SQLAlchemy==1.3.15
transaction==3.0.0
zope.sqlalchemy==1.3

The environment for coverage currently only is run on a specific Python/SQLAlchemy version and without the two Postgres specific test-runs on the CI, as such it does not accurately represent the coverage.

I have found that the best way to do coverage reports with tox and multiple environments is to set the environment variable that changes the name of the .coverage file, so each environment calculates the coverage and creates a unique file. Then at the end you run your report environment to stitch them together.

On the CI you would either have to create Artifacts and use them in a final dependent runner that stitches them together, or possibly coveralls supports stitching them together for you, if you upload multiple coverage reports from the same commit. (I know Codecov supports uploading multiple reports and stitches them together for you, but I have never used coveralls)

After several hours of debugging, I finally solved a SqlAlchemy issue:

1. mark_changed has an undocumented keep_session kwarg, which defaults to False
2. mark_changed is unaware of how the session was registered (i.e. what the user intended keep_session to be)

My first suggestion is to update the docs/readme to point this out.

My second suggestion is to extend the ZopeTransactionEvents object to have it's own join_transaction and mark_changed functions, which invoke the global ones with the configured keep_session or anything else.

Since register returns the configured extension, this would allow a developer to invoke ext.mark_changed(dbSession) and preserve the value of keep_session. I'd be happy to generate a PR for this.

We still keep an open-source project backwards compatible to Python2.7, and noticed this on a recent test failure:

https://github.com/zopefoundation/zope.sqlalchemy/blob/master/setup.py#L54

This library is still fully functional on Python2.7 with SQLAlchemy 1.4.7+

IMHO, it is worth supporting this combination, as a goal of the 1.4 branch of SQLAlchemy is to help legacy Python2 projects upgrade to Python3 and SQLAlchemy 2.0.

We currently span a wide range of SQLALchemy versions including compatibility code to support the older ones.
Given the last 1.0.x release is from August 2017, and we only test it for Python <= 3.6; I think we can drop support for 0.9.x and 1.0.x releases in the next release and clean up our code.

BUG/PROBLEM REPORT (OR OTHER COMMON ISSUE)

While upgrading a project from sqlalchemy 1.3 to 1.4, I ran into this problem that only occurs when using a sqlite database with this workaround for the pysqlite lazy transaction behavior.

Following a failed commit, the database connection needs to be rollback before it can be used again (keep_session is not being used).

When using a TransactionManager in explicit mode, attempts to start a new transaction generate an AlreadyInTransaction exception (even though we are using context managers to scope transactions). This happens with sqlite even without the lazy transaction workaround, but it does not happen with postgres.

With SQLAlchemy 1.4

With SQLAlchemy 1.3

What I did:

Minimal test case at https://gist.github.com/8bee5b88dea060eaafb0402208771e65

try:
  with transaction.manager:
    db.execute(DEFERRED_ERROR_QUERY)
except sa.exc.IntegrityError:
  pass

with transaction.manager:
  db.execute(BENIGN_QUERY)

What I expect to happen:

The second transaction should succeed.

What actually happened:

The second transaction fails with

(sqlite3.OperationalError) cannot start a transaction within a transaction

or (when using an explicit mode TransactionManager)

AlreadyInTransaction

What version of Python and Zope/Addons I am using:

CPython 3.6 (but also verified with 3.10)
SQLAlchemy==1.4.41
transaction==3.0.1
zope.sqlalchemy==1.6

There should be a deregister() counterpart to the new zope.sqlalchemy.register() function. It will be useful in contexts where you have to use the same Session but with different transactional behaviours.
For instance, depending on the situation, you may need to (dynamically) set keep_session=False or keep_session=True. This is very easy to accomplish using the old SQLAlchemy extension system. You just use Session.configure. On the other hand, using the new register() function, there is no way to do it.
I think that sqlalchemy.event.remove should do the trick.

http://docs.sqlalchemy.org/en/rel_0_9/core/event.html#sqlalchemy.event.remove

A simple script to reproduce

import transaction
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from zope.sqlalchemy import ZopeTransactionExtension

engine = create_engine('sqlite:///')

session = scoped_session(sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine,
    extension=ZopeTransactionExtension(keep_session=True)
))

Base = declarative_base()


class Foobar(Base):
    __tablename__ = 'foobar'

    id = Column(Integer, primary_key=True)
    name = Column(String)

Base.metadata.bind = engine
Base.metadata.create_all()


with transaction.manager:
    foobar = Foobar(id=1, name='john')
    session.add(foobar)

foobar = session.query(Foobar).get(1)

with transaction.manager:
    foobar.name = 'jackson'
    session.flush()

# boom! sqlalchemy.orm.exc.DetachedInstanceError will be raised
print foobar.name

After looking into the code, this is caused by join_transaction in mark_changed method doesn't take keep_session and pass along properly.

In SQLAlchemy 1.4 a some new DeprecationWarnings were issued.

Most prominent, the old-style method signature of after_bulk_update and after_bulk_delete changed to a take a [update|delete]_context similar to after_flush https://github.com/zopefoundation/zope.sqlalchemy/blob/master/src/zope/sqlalchemy/datamanager.py#L280-L284

This style was introduced in version 0.9 and would also require to bump the minimal required version of SQLAlchemy to this version. https://docs.sqlalchemy.org/en/14/orm/events.html?highlight=after_bulk_update#sqlalchemy.orm.SessionEvents.after_bulk_delete

I'm getting following error. Already upgraded zope.sqlalchemy...

2017-03-02 15:18:30,463  ERROR    1e5d84d7146d44d7  indico.wsgi               'SessionTransaction' object has no attribute '_iterate_parents'
Traceback (most recent call last):
  File "/var/www/indicoenv/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/var/www/indicoenv/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/var/www/indicoenv/lib/python2.7/site-packages/indico-1.9.9-py2.7.egg/indico/web/assets/blueprint.py", line 47, in js_vars_global
    data = generate_global_file(config)
  File "/var/www/indicoenv/lib/python2.7/site-packages/indico-1.9.9-py2.7.egg/indico/web/assets/vars_js.py", line 33, in generate_global_file
    locations = Location.find_all() if config.getIsRoomBookingActive() else []
  File "/var/www/indicoenv/lib/python2.7/site-packages/indico-1.9.9-py2.7.egg/indico/core/db/sqlalchemy/util/models.py", line 99, in find_all
    return cls.find(*args, **kwargs).all()
  File "/var/www/indicoenv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2679, in all
    return list(self)
  File "/var/www/indicoenv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2831, in __iter__
    return self._execute_and_instances(context)
  File "/var/www/indicoenv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2852, in _execute_and_instances
    close_with_result=True)
  File "/var/www/indicoenv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2861, in _get_bind_args
    **kw
  File "/var/www/indicoenv/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2843, in _connection_from_session
    conn = self.session.connection(**kw)
  File "/var/www/indicoenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 966, in connection
    execution_options=execution_options)
  File "/var/www/indicoenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 971, in _connection_for_bind
    engine, execution_options)
  File "/var/www/indicoenv/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 417, in _connection_for_bind
    self.session.dispatch.after_begin(self.session, self, conn)
  File "/var/www/indicoenv/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
    fn(*args, **kw)
  File "/usr/lib/python2.7/site-packages/zope/sqlalchemy/datamanager.py", line 231, in after_begin
    join_transaction(session, self.initial_state, self.transaction_manager, self.keep_session)
  File "/usr/lib/python2.7/site-packages/zope/sqlalchemy/datamanager.py", line 205, in join_transaction
    DataManager(session, initial_state, transaction_manager, keep_session=keep_session)
  File "/usr/lib/python2.7/site-packages/zope/sqlalchemy/datamanager.py", line 65, in __init__
    self.tx = session.transaction._iterate_parents()[-1]
AttributeError: 'SessionTransaction' object has no attribute '_iterate_parents'

Any suggestions or workaround here?

BUG/PROBLEM REPORT (OR OTHER COMMON ISSUE)

Under SQLAlchemy 1.4, keep_session=True is broken.

A SQLAlchemy Session is registered to transaction via zope.sqlalchemy, with keep_session set to True.

Editing an object in the session and calling .flush will cause SQLAlchemy to invoke a commit on the actual session - not the unit of work - and trigger the before_commit hook in zope.sqlalchemy, which will then fail.

Relavent LOC from stack trace:

• https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/session.py#L3360
• https://github.com/zopefoundation/zope.sqlalchemy/blob/master/src/zope/sqlalchemy/datamanager.py#L300-L304

What I did:

reproducible test case below

What I expect to happen:

The session should flush as in SQLAlchemy 1.3, not commit.

What actually happened:

SQLAlchemy invoked a commit on the transaction.

What version of Python and Zope/Addons I am using:

• Python3.8
• zope.sqlalchemy==1.3
• sqlalchemy==1.4

from zope.sqlalchemy import register
import transaction

from sqlalchemy import (
	create_engine,
	Column,
	Integer,
	String,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker


engine = create_engine("sqlite:///:memory:")
Base = declarative_base()


class User(Base):
	__tablename__ = "user"
	id = Column(Integer, primary_key=True)
	user_name = Column(String(50))

	def __repr__(self):
		return f"<User(id={self.id}, user_name='{self.user_name}')>"


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

# this breaks in 1.4
# session/transaction setup
session = Session()
register(session, keep_session=True)

# add a user
user = User(user_name="User")
session.add(user)
session.flush(objects=[user])
transaction.commit()
transaction.begin()

# edit them
user.user_name = "User2"

# this raises an AssertionError under SQLAlchemy 1.4
session.flush(objects=[user])

Hi there -

Figured I'd take a look at this to see what the story is. So, part of the blame is on me since apparently I haven't had SessionExtension / MapperExtension / etc. emit deprecation warnings, I'm going to see if I can get that in for 1.3. However the Extension classes have been documented as deprecated since 2012.

It looks like, and I think I probably helped with this, that you have built up the register system that uses the (not really new anymore) event API. So, that has to be where this goes, because SessionExtension as well as the extension parameter on Session will go away in a future relase, (I don't want to say what release, but there is a rumor that it may not include the digit "1" inside of it).

So I think the methods of ZopeTransactionExtension need to be separated off of SessionExtension so you can still use them in register, then the actual ZopeTransactionExtension can be isolated from it. Then you will want to update your README to document the register() version of things as how people should be doing it (which has been for six years now :) ). Again I hope to get some deprecation warnings in 1.3 to help this all along.

Blame is on me for not emitting deprecation warnings for the extension parameter as well as use of SessionExtension etc., I'm going to add that to my list of todos.

Motivation: Currently zope.sqlalchemy is included in Pyramid scaffold if you generate a new SQLAlchemy-based project.

The current README is written from Zope/SQLAlchemy experts to Zope/SQLAlchemy experts, referring to the history of Zope, SQLAlchemy and various web frameworks. I appreciate the fact that the authors make it upfront clear in README that you need all this information in order to understand the README.

However, most of web developers are not privileged to know the decade of history of various Python-based transaction management solutions. zope.sqlalchemy is obviously solving some problem for them, because it is included in the default scaffolds, but it still leaves a lot of questions open and this might result building misbehaving applications due to misunderstanding how transaction management works. Also if one is writing an application based on plain SQLAlchemy, there should a pitch what zope.sqlalchemy could do for them.

So I hope if the authors could clarify the points below and I could update the README on this input, so that the new developers can get aboard faster.

1. What is a transaction manager

2. Why web applications need transactions (ok, this part I can do myself)

3. How Zope transaction manager is related to SQLAlchemy transactions, and thus underlying database transactions? What is two-phase commit? Is it different from a databse commit?

4. When to use Zope transaction manager and why?

5. When not to use Zope transaction manager and e.g. use plain SQLAlchemy sessions

6. What database considerations there are (PostgreSQL, SQLite)

7. What are retries and how to use them

Thanks for a great package!

Hi, can we have a 0.7.3 release please. Last change is very important.
Thanks

Thomas

Running the tests locally with SQLAlchemy 1.4 leads to the following failures:

Failure in test .../zope.sqlalchemy/src/zope/sqlalchemy/README.rst
Failed doctest test for README.rst
  File ".../zope.sqlalchemy/src/zope/sqlalchemy/README.rst", line 0

----------------------------------------------------------------------
File ".../zope.sqlalchemy/src/zope/sqlalchemy/README.rst", line 190, in README.rst
Failed example:
    conn.execute(users.update(users.c.name=='bob'), name='ben')
Expected:
    <sqlalchemy.engine...ResultProxy object at ...>
Got:
    <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x1087c8ad0>
----------------------------------------------------------------------
File ".../zope.sqlalchemy/src/zope/sqlalchemy/README.rst", line 208, in README.rst
Failed example:
    conn.execute(users.update(users.c.name=='ben'), name='bob')
Expected:
    <sqlalchemy.engine...ResultProxy object at ...>
Got:
    <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x1087caed0>


  Ran 24 tests with 1 failures, 0 errors, 0 skipped in 0.485 seconds.

GHA shows the same problems, see https://github.com/zopefoundation/zope.sqlalchemy/runs/2128734743?check_suite_focus=true

I am not entirely sure if this is a bug, but the current behavior could at least be considered suboptimal:

When the ZopeTransactionExtension triggers a join, while the state of the zope transaction is COMMITFAILED a TransactionFailedError will be raised and the _SESSION_STATE entry of the sqlalchemy session will be left behind.

If the sqlalchemy session is reused in the future (ie because it is threadscoped) it won't be joined to the zope transaction at all, since it already has a corresponding entry in _SESSION_STATE. This causes the sqlalchemy transaction to remain open, which can lead to a database transaction being "wedged" until it is closed due to a database timeout or a restart of the zope instance.

I am not sure if a join should even be attempted if the transaction is in COMMITFAILED. Doing operations on the sqlalchemy session while the zope transaction is in this state could probably be considerer a bug on the user's side. But I believe this should be addressed in some way.