python - 与 SQLAlchemy-Continuum 的多对多关系插入具有重复 transaction_id 的记录

Question

我正在使用:

SQLAlchemy==1.0.8
sqlalchemy-continuum===1.2.1
sqlalchemy-utils==0.31.0

后端是 PG 9.3。对于以下定义，Continuum 似乎仍然无法正确处理多对多处理:

assoc_ver_shallow_lic_table = Table('assoc_ver_shallow_lic', Base.metadata,
                      Column('shallow_version_id', ForeignKey('base_version.id'), index=True),
                      Column('shallow_license_id', ForeignKey('license.id'), index=True)
                      )




class BaseVersion(Base):
    __tablename__ = 'base_version'
    __versioned__ = {}
    id = Column(Integer, primary_key=True, info={'label': 'DB ID'})
    deep_licenses = relationship('License', secondary=assoc_ver_deep_lic_table,
                                  primaryjoin='base_version.c.id==assoc_ver_deep_lic.c.version_id',
                                  secondaryjoin='assoc_ver_deep_lic.c.deep_license_id==license.c.id',
                                  backref='version_from_deep_license', cascade='save-update')
    licenses = relationship('License', secondary=assoc_ver_shallow_lic_table,
                                      primaryjoin='base_version.c.id==assoc_ver_shallow_lic.c.shallow_version_id',
                                      secondaryjoin='assoc_ver_shallow_lic.c.shallow_license_id==license.c.id',
                                      info={'label': 'Licenses'}, cascade='save-update')
    preferred_license_id = Column(Integer, ForeignKey('license.id'), index=True)
    preferred_license = relationship('License', uselist=False, primaryjoin='OSSVersion.preferred_license_id==License.id',
                                               info={'label': 'Preferred License'}, lazy='joined',
                                               cascade='save-update')
    __mapper_args__ = {
        'polymorphic_identity':'base_version',
        'polymorphic_on':type,
    }


class OSSVersion(BaseVersion):
    __tablename__ = 'oss_version'
    __versioned__ = {}
    id = Column(Integer, ForeignKey('base_version.id'), primary_key=True, info={'label': 'DB ID'})
    __mapper_args__ = {
        'polymorphic_identity':'version',
    }


class UnspecifiedVersion(BaseVersion):
    __tablename__ = 'unspecified_version'
    __versioned__ = {}
    id = Column(Integer, ForeignKey('base_version.id'), primary_key=True)
    __mapper_args__ = {
        'polymorphic_identity':'unspecified_version',
    }

在测试中，我向一些 OSSVersion 添加了一个 License:

    vers = DBSession.query(OSSVersion).filter(OSSVersion.id <= 100).order_by(OSSVersion.id).all()
    lic = DBSession.query(License).order_by(License.id).first()
    for v in vers:
        v.licenses.append(lic)
        v.preferred_license = lic

失败如下:

INFO:sqlalchemy.engine.base.Engine:INSERT INTO oss_version_version (id, transaction_id, end_transaction_id, operation_type) VALUES (%(id)s, %(transaction_id)s, %(end_transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 1, 'end_transaction_id': None, 'id': 101, 'transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE oss_version_version SET end_transaction_id=%(end_transaction_id)s WHERE oss_version_version.transaction_id = (SELECT max(oss_version_version_1.transaction_id) AS max_1 
FROM oss_version_version AS oss_version_version_1 
WHERE oss_version_version_1.transaction_id < %(transaction_id_1)s AND oss_version_version_1.id = %(id_1)s) AND oss_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 101, 'transaction_id_1': 19L, 'id_1': 101, 'end_transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE base_version_version SET end_transaction_id=%(end_transaction_id)s WHERE base_version_version.transaction_id = (SELECT max(base_version_version_1.transaction_id) AS max_1 
FROM base_version_version AS base_version_version_1 
WHERE base_version_version_1.transaction_id < %(transaction_id_1)s AND base_version_version_1.id = %(id_1)s) AND base_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 101, 'transaction_id_1': 19L, 'id_1': 101, 'end_transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO base_version_version (id, component_id, major, minor, patch, minor_patch, freetext_version, unparsed_bd_version, bd_component_name, bd_component_kbid, bd_protex_componentversionid, bd_releasedate, minimum_filled, preferred_license_id, identification_type, editor, reviewer, type, temp_note, commercial_sw, manual_restrictions, transaction_id, end_transaction_id, operation_type) VALUES (%(id)s, %(component_id)s, %(major)s, %(minor)s, %(patch)s, %(minor_patch)s, %(freetext_version)s, %(unparsed_bd_version)s, %(bd_component_name)s, %(bd_component_kbid)s, %(bd_protex_componentversionid)s, %(bd_releasedate)s, %(minimum_filled)s, %(preferred_license_id)s, %(identification_type)s, %(editor)s, %(reviewer)s, %(type)s, %(temp_note)s, %(commercial_sw)s, %(manual_restrictions)s, %(transaction_id)s, %(end_transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'major': 2L, 'identification_type': u'Email_Request', 'temp_note': u'Version originally imported from XYZ, row ID: 340, URL: https://int.net1/DispForm.aspx?ID=340', 'bd_component_name': None, 'end_transaction_id': None, 'preferred_license_id': 26, 'reviewer': None, 'id': 102, 'freetext_version': None, 'manual_restrictions': True, 'minimum_filled': True, 'editor': 'test_run_default_on_versions@ACME.com', 'bd_protex_componentversionid': u'1154740', 'type': u'version', 'minor': 60L, 'commercial_sw': False, 'unparsed_bd_version': u'2.60', 'minor_patch': None, 'component_id': 5, 'bd_component_kbid': u'gnuautoconf259', 'patch': None, 'transaction_id': 19L, 'operation_type': 1, 'bd_releasedate': datetime.datetime(2006, 6, 26, 0, 0)}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO oss_version_version (id, transaction_id, end_transaction_id, operation_type) VALUES (%(id)s, %(transaction_id)s, %(end_transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 1, 'end_transaction_id': None, 'id': 102, 'transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE oss_version_version SET end_transaction_id=%(end_transaction_id)s WHERE oss_version_version.transaction_id = (SELECT max(oss_version_version_1.transaction_id) AS max_1 
FROM oss_version_version AS oss_version_version_1 
WHERE oss_version_version_1.transaction_id < %(transaction_id_1)s AND oss_version_version_1.id = %(id_1)s) AND oss_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 102, 'transaction_id_1': 19L, 'id_1': 102, 'end_transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE base_version_version SET end_transaction_id=%(end_transaction_id)s WHERE base_version_version.transaction_id = (SELECT max(base_version_version_1.transaction_id) AS max_1 
FROM base_version_version AS base_version_version_1 
WHERE base_version_version_1.transaction_id < %(transaction_id_1)s AND base_version_version_1.id = %(id_1)s) AND base_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 102, 'end_transaction_id': 19L, 'id_1': 102, 'transaction_id_1': 19L}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO base_version_version (id, component_id, major, minor, patch, minor_patch, freetext_version, unparsed_bd_version, bd_component_name, bd_component_kbid, bd_protex_componentversionid, bd_releasedate, minimum_filled, preferred_license_id, identification_type, editor, reviewer, type, temp_note, commercial_sw, manual_restrictions, transaction_id, end_transaction_id, operation_type) VALUES (%(id)s, %(component_id)s, %(major)s, %(minor)s, %(patch)s, %(minor_patch)s, %(freetext_version)s, %(unparsed_bd_version)s, %(bd_component_name)s, %(bd_component_kbid)s, %(bd_protex_componentversionid)s, %(bd_releasedate)s, %(minimum_filled)s, %(preferred_license_id)s, %(identification_type)s, %(editor)s, %(reviewer)s, %(type)s, %(temp_note)s, %(commercial_sw)s, %(manual_restrictions)s, %(transaction_id)s, %(end_transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'major': 2L, 'identification_type': u'Email_Request', 'temp_note': u'Version originally imported from XYZ, row ID: 340, URL: https://int.net1/DispForm.aspx?ID=340', 'bd_component_name': None, 'end_transaction_id': None, 'preferred_license_id': 26, 'reviewer': None, 'id': 103, 'freetext_version': None, 'manual_restrictions': True, 'minimum_filled': True, 'editor': None, 'bd_protex_componentversionid': u'1154743', 'type': u'version', 'minor': 63L, 'commercial_sw': False, 'unparsed_bd_version': u'2.63', 'minor_patch': None, 'component_id': 5, 'bd_component_kbid': u'gnuautoconf259', 'patch': None, 'transaction_id': 19L, 'operation_type': 1, 'bd_releasedate': datetime.datetime(2008, 9, 9, 0, 0)}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO oss_version_version (id, transaction_id, end_transaction_id, operation_type) VALUES (%(id)s, %(transaction_id)s, %(end_transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 1, 'end_transaction_id': None, 'id': 103, 'transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE oss_version_version SET end_transaction_id=%(end_transaction_id)s WHERE oss_version_version.transaction_id = (SELECT max(oss_version_version_1.transaction_id) AS max_1 
FROM oss_version_version AS oss_version_version_1 
WHERE oss_version_version_1.transaction_id < %(transaction_id_1)s AND oss_version_version_1.id = %(id_1)s) AND oss_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 103, 'end_transaction_id': 19L, 'id_1': 103, 'transaction_id_1': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE base_version_version SET end_transaction_id=%(end_transaction_id)s WHERE base_version_version.transaction_id = (SELECT max(base_version_version_1.transaction_id) AS max_1 
FROM base_version_version AS base_version_version_1 
WHERE base_version_version_1.transaction_id < %(transaction_id_1)s AND base_version_version_1.id = %(id_1)s) AND base_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 103, 'end_transaction_id': 19L, 'id_1': 103, 'transaction_id_1': 19L}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO base_version_version (id, component_id, major, minor, patch, minor_patch, freetext_version, unparsed_bd_version, bd_component_name, bd_component_kbid, bd_protex_componentversionid, bd_releasedate, minimum_filled, preferred_license_id, identification_type, editor, reviewer, type, temp_note, commercial_sw, manual_restrictions, transaction_id, end_transaction_id, operation_type) VALUES (%(id)s, %(component_id)s, %(major)s, %(minor)s, %(patch)s, %(minor_patch)s, %(freetext_version)s, %(unparsed_bd_version)s, %(bd_component_name)s, %(bd_component_kbid)s, %(bd_protex_componentversionid)s, %(bd_releasedate)s, %(minimum_filled)s, %(preferred_license_id)s, %(identification_type)s, %(editor)s, %(reviewer)s, %(type)s, %(temp_note)s, %(commercial_sw)s, %(manual_restrictions)s, %(transaction_id)s, %(end_transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'major': 2L, 'identification_type': u'Email_Request', 'temp_note': u'Version originally imported from XYZ, row ID: 340, URL: https://int.net1/DispForm.aspx?ID=340', 'bd_component_name': None, 'end_transaction_id': None, 'preferred_license_id': 26, 'reviewer': None, 'id': 104, 'freetext_version': None, 'manual_restrictions': True, 'minimum_filled': True, 'editor': 'test_run_default_on_versions@ACME.com', 'bd_protex_componentversionid': u'1212226', 'type': u'version', 'minor': 67L, 'commercial_sw': False, 'unparsed_bd_version': u'2.67', 'minor_patch': None, 'component_id': 5, 'bd_component_kbid': u'gnuautoconf259', 'patch': None, 'transaction_id': 19L, 'operation_type': 1, 'bd_releasedate': datetime.datetime(2010, 8, 2, 0, 0)}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO oss_version_version (id, transaction_id, end_transaction_id, operation_type) VALUES (%(id)s, %(transaction_id)s, %(end_transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 1, 'end_transaction_id': None, 'id': 104, 'transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE oss_version_version SET end_transaction_id=%(end_transaction_id)s WHERE oss_version_version.transaction_id = (SELECT max(oss_version_version_1.transaction_id) AS max_1 
FROM oss_version_version AS oss_version_version_1 
WHERE oss_version_version_1.transaction_id < %(transaction_id_1)s AND oss_version_version_1.id = %(id_1)s) AND oss_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 104, 'end_transaction_id': 19L, 'id_1': 104, 'transaction_id_1': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE base_version_version SET end_transaction_id=%(end_transaction_id)s WHERE base_version_version.transaction_id = (SELECT max(base_version_version_1.transaction_id) AS max_1 
FROM base_version_version AS base_version_version_1 
WHERE base_version_version_1.transaction_id < %(transaction_id_1)s AND base_version_version_1.id = %(id_1)s) AND base_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 104, 'end_transaction_id': 19L, 'id_1': 104, 'transaction_id_1': 19L}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO base_version_version (id, component_id, major, minor, patch, minor_patch, freetext_version, unparsed_bd_version, bd_component_name, bd_component_kbid, bd_protex_componentversionid, bd_releasedate, minimum_filled, preferred_license_id, identification_type, editor, reviewer, type, temp_note, commercial_sw, manual_restrictions, transaction_id, end_transaction_id, operation_type) VALUES (%(id)s, %(component_id)s, %(major)s, %(minor)s, %(patch)s, %(minor_patch)s, %(freetext_version)s, %(unparsed_bd_version)s, %(bd_component_name)s, %(bd_component_kbid)s, %(bd_protex_componentversionid)s, %(bd_releasedate)s, %(minimum_filled)s, %(preferred_license_id)s, %(identification_type)s, %(editor)s, %(reviewer)s, %(type)s, %(temp_note)s, %(commercial_sw)s, %(manual_restrictions)s, %(transaction_id)s, %(end_transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'major': 2L, 'identification_type': u'Email_Request', 'temp_note': u'Version originally imported from XYZ, row ID: 340, URL: https://int.net1/DispForm.aspx?ID=340', 'bd_component_name': None, 'end_transaction_id': None, 'preferred_license_id': 26, 'reviewer': None, 'id': 105, 'freetext_version': None, 'manual_restrictions': True, 'minimum_filled': True, 'editor': None, 'bd_protex_componentversionid': u'1154730', 'type': u'version', 'minor': 12L, 'commercial_sw': False, 'unparsed_bd_version': u'2.12', 'minor_patch': None, 'component_id': 5, 'bd_component_kbid': u'gnuautoconf259', 'patch': None, 'transaction_id': 19L, 'operation_type': 1, 'bd_releasedate': datetime.datetime(1996, 11, 26, 0, 0)}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO oss_version_version (id, transaction_id, end_transaction_id, operation_type) VALUES (%(id)s, %(transaction_id)s, %(end_transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 1, 'end_transaction_id': None, 'id': 105, 'transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE oss_version_version SET end_transaction_id=%(end_transaction_id)s WHERE oss_version_version.transaction_id = (SELECT max(oss_version_version_1.transaction_id) AS max_1 
FROM oss_version_version AS oss_version_version_1 
WHERE oss_version_version_1.transaction_id < %(transaction_id_1)s AND oss_version_version_1.id = %(id_1)s) AND oss_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 105, 'transaction_id_1': 19L, 'id_1': 105, 'end_transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:UPDATE base_version_version SET end_transaction_id=%(end_transaction_id)s WHERE base_version_version.transaction_id = (SELECT max(base_version_version_1.transaction_id) AS max_1 
FROM base_version_version AS base_version_version_1 
WHERE base_version_version_1.transaction_id < %(transaction_id_1)s AND base_version_version_1.id = %(id_1)s) AND base_version_version.id = %(id_2)s
INFO:sqlalchemy.engine.base.Engine:{'id_2': 105, 'end_transaction_id': 19L, 'id_1': 105, 'transaction_id_1': 19L}
INFO:sqlalchemy.engine.base.Engine:SELECT oss_version.id AS oss_version_id, base_version.id AS base_version_id, base_version.component_id AS base_version_component_id, base_version.major AS base_version_major, base_version.minor AS base_version_minor, base_version.patch AS base_version_patch, base_version.minor_patch AS base_version_minor_patch, base_version.freetext_version AS base_version_freetext_version, base_version.unparsed_bd_version AS base_version_unparsed_bd_version, base_version.bd_component_name AS base_version_bd_component_name, base_version.bd_component_kbid AS base_version_bd_component_kbid, base_version.bd_protex_componentversionid AS base_version_bd_protex_componentversionid, base_version.bd_releasedate AS base_version_bd_releasedate, base_version.minimum_filled AS base_version_minimum_filled, base_version.preferred_license_id AS base_version_preferred_license_id, base_version.identification_type AS base_version_identification_type, base_version.editor AS base_version_editor, base_version.reviewer AS base_version_reviewer, base_version.type AS base_version_type, base_version.temp_note AS base_version_temp_note, base_version.commercial_sw AS base_version_commercial_sw, base_version.manual_restrictions AS base_version_manual_restrictions, license_1.id AS license_1_id, license_1.name AS license_1_name, license_1.full_name AS license_1_full_name, license_1.urls AS license_1_urls, license_1.notes AS license_1_notes, license_1.editor AS license_1_editor, license_1.reviewer AS license_1_reviewer, usecase_1.id AS usecase_1_id, usecase_1.name AS usecase_1_name, usecase_1.description AS usecase_1_description, effective_restriction_1.id AS effective_restriction_1_id, effective_restriction_1.version_id AS effective_restriction_1_version_id, effective_restriction_1.license_restriction_id AS effective_restriction_1_license_restriction_id, effective_restriction_1.requirement_type_id AS effective_restriction_1_requirement_type_id, effective_restriction_1.requirement_data AS effective_restriction_1_requirement_data, effective_restriction_1.usecase_id AS effective_restriction_1_usecase_id, effective_restriction_1.server_side AS effective_restriction_1_server_side, effective_restriction_1.req_overrides_license_id AS effective_restriction_1_req_overrides_license_id 
FROM base_version JOIN oss_version ON base_version.id = oss_version.id LEFT OUTER JOIN license AS license_1 ON base_version.preferred_license_id = license_1.id LEFT OUTER JOIN effective_restriction AS effective_restriction_1 ON base_version.id = effective_restriction_1.version_id LEFT OUTER JOIN usecase AS usecase_1 ON usecase_1.id = effective_restriction_1.usecase_id 
WHERE oss_version.id <= %(id_1)s ORDER BY oss_version.id
INFO:sqlalchemy.engine.base.Engine:{'id_1': 100}
INFO:sqlalchemy.engine.base.Engine:SELECT license.id AS license_id, license.name AS license_name, license.full_name AS license_full_name, license.urls AS license_urls, license.notes AS license_notes, license.editor AS license_editor, license.reviewer AS license_reviewer 
FROM license ORDER BY license.id 
 LIMIT %(param_1)s
INFO:sqlalchemy.engine.base.Engine:{'param_1': 1}
INFO:sqlalchemy.engine.base.Engine:SELECT license.id AS license_id, license.name AS license_name, license.full_name AS license_full_name, license.urls AS license_urls, license.notes AS license_notes, license.editor AS license_editor, license.reviewer AS license_reviewer 
FROM license, assoc_ver_shallow_lic 
WHERE %(param_1)s = assoc_ver_shallow_lic.shallow_version_id AND license.id = assoc_ver_shallow_lic.shallow_license_id
INFO:sqlalchemy.engine.base.Engine:{'param_1': 1}
INFO:sqlalchemy.engine.base.Engine:UPDATE base_version SET preferred_license_id=%(preferred_license_id)s WHERE base_version.id = %(base_version_id)s
INFO:sqlalchemy.engine.base.Engine:{'base_version_id': 1, 'preferred_license_id': 1}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO assoc_ver_shallow_lic (shallow_version_id, shallow_license_id) VALUES (%(shallow_version_id)s, %(shallow_license_id)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 0, 'shallow_version_id': 1, 'shallow_license_id': 1}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO assoc_ver_shallow_lic_version (shallow_version_id, shallow_license_id, transaction_id, operation_type) VALUES (%(shallow_version_id)s, %(shallow_license_id)s, %(transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 0, 'shallow_version_id': 1, 'shallow_license_id': 1, 'transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO assoc_ver_shallow_lic_version (shallow_version_id, shallow_license_id, transaction_id, operation_type) VALUES (%(shallow_version_id)s, %(shallow_license_id)s, %(transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 0, 'shallow_version_id': 1, 'shallow_license_id': 1, 'transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:ROLLBACK

E

Error
Traceback (most recent call last):
  File "C:\Program Files (x86)\Python271\Lib\unittest\case.py", line 329, in run
    testMethod()
  File "C:\ACME\Dev\projects\mysite\tests\model\task_dispatcher_time_consuming_test.py", line 209, in test_preflicmissing_check_groupkey_valid
    v.licenses.append(lic)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\attributes.py", line 237, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\attributes.py", line 578, in get
    value = self.callable_(state, passive)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\strategies.py", line 529, in _load_for_state
    return self._emit_lazyload(session, state, ident_key, passive)
  File "<string>", line 1, in <lambda>
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\strategies.py", line 599, in _emit_lazyload
    result = q.all()
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\query.py", line 2399, in all
    return list(self)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\query.py", line 2515, in __iter__
    self.session._autoflush()
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\session.py", line 1292, in _autoflush
    util.raise_from_cause(e)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\util\compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\session.py", line 1282, in _autoflush
    self.flush()
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\session.py", line 2004, in flush
    self._flush(objects)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\session.py", line 2122, in _flush
    transaction.rollback(_capture_exception=True)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\util\langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\session.py", line 2090, in _flush
    self.dispatch.after_flush(self, flush_context)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\event\attr.py", line 254, in __call__
    fn(*args, **kw)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy_continuum\manager.py", line 341, in after_flush
    uow.process_after_flush(session)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy_continuum\unit_of_work.py", line 95, in process_after_flush
    self.make_versions(session)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy_continuum\unit_of_work.py", line 308, in make_versions
    self.create_association_versions(session)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy_continuum\unit_of_work.py", line 295, in create_association_versions
    session.execute(stmt)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\orm\session.py", line 1023, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\engine\base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\sql\elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\engine\base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\engine\base.py", line 1146, in _execute_context
    context)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\engine\base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\util\compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\engine\base.py", line 1139, in _execute_context
    context)
  File "C:\ACME\Dev\projects\mysite\ve\lib\site-packages\sqlalchemy\engine\default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.IntegrityError) duplicate key value violates unique constraint "assoc_ver_shallow_lic_version_pkey"
DETAIL:  Key (transaction_id)=(19) already exists.
 [SQL: 'INSERT INTO assoc_ver_shallow_lic_version (shallow_version_id, shallow_license_id, transaction_id, operation_type) VALUES (%(shallow_version_id)s, %(shallow_license_id)s, %(transaction_id)s, %(operation_type)s)'] [parameters: {'operation_type': 0, 'shallow_version_id': 1, 'shallow_license_id': 1, 'transaction_id': 19L}]

基本原因很明显:历史表 assoc_ver_shallow_lic_version 有主键约束 assoc_ver_shallow_lic_version_pkey 要求是唯一的，而 Continuum 使用相同的 transaction_id 向其中添加了几条记录:

INFO:sqlalchemy.engine.base.Engine:INSERT INTO assoc_ver_shallow_lic_version (shallow_version_id, shallow_license_id, transaction_id, operation_type) VALUES (%(shallow_version_id)s, %(shallow_license_id)s, %(transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 0, 'shallow_version_id': 1, 'shallow_license_id': 1, 'transaction_id': 19L}
INFO:sqlalchemy.engine.base.Engine:INSERT INTO assoc_ver_shallow_lic_version (shallow_version_id, shallow_license_id, transaction_id, operation_type) VALUES (%(shallow_version_id)s, %(shallow_license_id)s, %(transaction_id)s, %(operation_type)s)
INFO:sqlalchemy.engine.base.Engine:{'operation_type': 0, 'shallow_version_id': 1, 'shallow_license_id': 1, 'transaction_id': 19L}

我将它与 Pyramid 一起使用，它生成了一个用于初始化数据库的脚本，尽管它主要由 Base.metadata.create_all(engine) 组成，这导致关联表的以下 DDL :

CREATE TABLE "public"."assoc_ver_shallow_lic_version"
(
   shallow_version_id int,
   shallow_license_id int,
   transaction_id bigint PRIMARY KEY NOT NULL,
   end_transaction_id bigint,
   operation_type smallint NOT NULL
)
;
CREATE UNIQUE INDEX assoc_ver_shallow_lic_version_pkey ON "public"."assoc_ver_shallow_lic_version"(transaction_id)
;
CREATE INDEX ix_assoc_ver_shallow_lic_version_operation_type ON "public"."assoc_ver_shallow_lic_version"(operation_type)
;
CREATE INDEX ix_assoc_ver_shallow_lic_version_shallow_version_id ON "public"."assoc_ver_shallow_lic_version"(shallow_version_id)
;
CREATE INDEX ix_assoc_ver_shallow_lic_version_shallow_license_id ON "public"."assoc_ver_shallow_lic_version"(shallow_license_id)
;
CREATE INDEX ix_assoc_ver_shallow_lic_version_transaction_id ON "public"."assoc_ver_shallow_lic_version"(transaction_id)
;
CREATE INDEX ix_assoc_ver_shallow_lic_version_end_transaction_id ON "public"."assoc_ver_shallow_lic_version"(end_transaction_id)
;

我注意到的令人担忧的方面是，当我运行 is_versioned(assoc_ver_shallow_lic_table) 时，我得到了 False。

是否有一些特殊的方法可以为 Continuum 配置多对多关联表，或者这是一个错误？

Answer 1

SQLAlchemy-Continuum 添加表的主键+transaction_id作为版本表的主键。

如果你的多对多关联表没有设置主键，它只会添加 transaction_id 作为主键，导致插入失败，因为每次更改到 many -对多表将被添加到具有相同主键的版本表中，导致 DETAIL: Key (transaction_id)=(19) already exists. 错误，因为一切都在同一个内完成交易。

这可以通过添加 primary_key=True 在关联表上设置主键来解决:

assoc_ver_shallow_lic_table = Table('assoc_ver_shallow_lic', Base.metadata,
                  Column('shallow_version_id', ForeignKey('base_version.id'), index=True, primary_key=True),
                  Column('shallow_license_id', ForeignKey('license.id'), index=True, primary_key=True)
                  )