Issue 1:
Migrated issue, originally created by somedood (@somedood)

Where there are cyclical relations in objects, and a new object is created, sometimes the session will try to save the same object twice which results in an IntegrityError exception since the same id is used on both INSERTS. The test uses activemapper to make the table declarations smaller, though as far as I can tell it is unrelated to the extension.

from sqlalchemy import *
from sqlalchemy.ext.activemapper import *
import sys

_engine = create_engine("postgres://****:****@localhost/issuetracker")
metadata.connect(_engine)

class Answer(ActiveMapper):
class mapping:
__table__ = 'answer'
id = column(Integer, primary_key=True)
label = column(String(80), default=None)
fromquestion_id = column(Integer, foreign_key='question.id', default=None)
fromquestion = relationship('Question', primaryjoin=['answer.fromquestion_id','question.id']('answer.fromquestion_id','question.id'), uselist=False)
toquestion_id = column(Integer, foreign_key='question.id', default=None)
toquestion = relationship('Question', primaryjoin=['answer.toquestion_id','question.id']('answer.toquestion_id','question.id'), uselist=False)

class Issue(ActiveMapper):
class mapping:
__table__ = 'issue'
id = column(Integer, primary_key=True)
name = column(String, default='')
description = column(String, default='')
provider_service_id = column(Integer, foreign_key='provider_service.id')
providerservice = relationship('ProviderService', uselist=False)
firstquestion_id = column(Integer, foreign_key='question.id', default=None)
firstquestion = relationship('Question', primaryjoin=['issue.firstquestion_id','question.id']('issue.firstquestion_id','question.id'), uselist=False)
questions = one_to_many('Question', primaryjoin=['issue.id','question.issue_id']('issue.id','question.issue_id'))

class Provider(ActiveMapper):
class mapping:
__table__ = 'provider'
id = column(Integer, primary_key=True)
firstquestion_id = column(Integer, foreign_key='question.id', default=None)
firstquestion = relationship('Question', primaryjoin=['provider.firstquestion_id','question.id']('provider.firstquestion_id','question.id'), uselist=False)
questions = one_to_many('Question', primaryjoin=['provider.id','question.provider_id']('provider.id','question.provider_id'))
providerservices = one_to_many('ProviderService')

class ProviderService(ActiveMapper):
class mapping:
__table__ = 'provider_service'
id = column(Integer, primary_key=True)
name = column(String(80), default='')
provider_id = column(Integer, foreign_key='provider.id')
provider = relationship('Provider', uselist=False)
issues = one_to_many('Issue')

class Question(ActiveMapper):
class mapping:
__table__ = 'question'
id = column(Integer, primary_key=True)
issue_id = column(Integer, foreign_key='issue.id', default=None)
issue = relationship('Issue', primaryjoin=['question.issue_id','issue.id']('question.issue_id','issue.id'), uselist=False)
provider_id = column(Integer, foreign_key='provider.id', default=None)
provider = relationship('Provider', primaryjoin=['question.provider_id','provider.id']('question.provider_id','provider.id'), uselist=False)
provider_service_id = column(Integer, foreign_key='provider_service.id', default=None)
providerservice = relationship('ProviderService', uselist=False)
description = column(String(250))
answers = one_to_many('Answer', primaryjoin=['question.id','answer.fromquestion_id']('question.id','answer.fromquestion_id'))
wikilink = column(String(80))

import logging
logging.getLogger('sqlalchemy').setLevel(logging.DEBUG)
logging.getLogger('sqlalchemy').addHandler(logging.FileHandler("c:\\temp\\sqalog.txt","w"))

q = Question(description="testtest")
q.flush()

After a new question is created, and then flush() is called, an IntegrityError exception is raised. If I remove one of the classes, such as Issue (and it's relating columns \ relations) I don't get the error. And example of Issue being removed:

from sqlalchemy import *
from sqlalchemy.ext.activemapper import *
import sys

_engine = create_engine("postgres://****:****@localhost/issuetracker")
metadata.connect(_engine)

class Answer(ActiveMapper):
class mapping:
__table__ = 'answer'
id = column(Integer, primary_key=True)
label = column(String(80), default=None)
fromquestion_id = column(Integer, foreign_key='question.id', default=None)
fromquestion = relationship('Question', primaryjoin=['answer.fromquestion_id','question.id']('answer.fromquestion_id','question.id'), uselist=False)
toquestion_id = column(Integer, foreign_key='question.id', default=None)
toquestion = relationship('Question', primaryjoin=['answer.toquestion_id','question.id']('answer.toquestion_id','question.id'), uselist=False)

class Provider(ActiveMapper):
class mapping:
__table__ = 'provider'
id = column(Integer, primary_key=True)
firstquestion_id = column(Integer, foreign_key='question.id', default=None)
firstquestion = relationship('Question', primaryjoin=['provider.firstquestion_id','question.id']('provider.firstquestion_id','question.id'), uselist=False)
questions = one_to_many('Question', primaryjoin=['provider.id','question.provider_id']('provider.id','question.provider_id'))
providerservices = one_to_many('ProviderService')

class ProviderService(ActiveMapper):
class mapping:
__table__ = 'provider_service'
id = column(Integer, primary_key=True)
name = column(String(80), default='')
provider_id = column(Integer, foreign_key='provider.id')
provider = relationship('Provider', uselist=False)
issues = one_to_many('Issue')

class Question(ActiveMapper):
class mapping:
__table__ = 'question'
id = column(Integer, primary_key=True)
provider_id = column(Integer, foreign_key='provider.id', default=None)
provider = relationship('Provider', primaryjoin=['question.provider_id','provider.id']('question.provider_id','provider.id'), uselist=False)
provider_service_id = column(Integer, foreign_key='provider_service.id', default=None)
providerservice = relationship('ProviderService', uselist=False)
description = column(String(250))
answers = one_to_many('Answer', primaryjoin=['question.id','answer.fromquestion_id']('question.id','answer.fromquestion_id'))
wikilink = column(String(80))

import logging
logging.getLogger('sqlalchemy').setLevel(logging.DEBUG)
logging.getLogger('sqlalchemy').addHandler(logging.FileHandler("c:\\temp\\sqalog.txt","w"))

q = Question(description="testtest")
q.flush()

The test succeeds with that class removed. It also works if Provider, or ProviderService is removed (along with the relations that point to them). The log for when the IntegrityError is raised is as follows:

Dependency sort:
Mapper|ProviderService|provider_service (cycles: [Mapper|Question|question, Mapper|Provider|provider](Mapper|ProviderService|provider_service,))
Mapper|Issue|issue (cycles: [Mapper|Question|question](Mapper|Issue|issue,))
Mapper|Answer|answer

Task dump:

UOWTask(0xc96230, ProviderService/provider_service/None) (contains cyclical sub-tasks) (save/update phase)
|- Save (placeholder)
|
|- UOWTask(0xc96230->0xc9de90, Question/question/None) (save/update phase)
| |- Save Question(0xc8a7b0)
| |----
|
| |- Process Question(0xc8a7b0).answers
|
|- UOWTask(0xc967d0, Issue/issue/None) (contains cyclical sub-tasks) (save/update phase)
| |- Save (placeholder)
| |
| |- UOWTask(0xc967d0->0xca1310, Question/question/None) (save/update phase)
| | |- Save Question(0xc8a7b0)
| | |----
| |
| | |- Process Question(0xc8a7b0).answers
| |
| |- UOWTask(0xc9def0, Answer/answer/None) (save/update phase)
| | |- (empty task)
| |
| |----
|
|
|- UOWTask(0xc967d0, Issue/issue/None) (contains cyclical sub-tasks) (delete phase)
| |
| |- UOWTask(0xc9def0, Answer/answer/None) (delete phase)
| | |- (empty task)
| |
| |
| |- UOWTask(0xc967d0->0xca1310, Question/question/None) (delete phase)
| | |----
| |
| |----
|
|
|- UOWTask(0xc96230->0xc9de90, Question/question/None) (delete phase)
| |----
|
|----

(ProviderService|provider_service) save_obj() start, batched
Created new connection <connection object at 0x00BACF90; dsn: 'dbname=issuetracker host=localhost user=**** password=****', closed: 0>
Connection <connection object at 0x00BACF90; dsn: 'dbname=issuetracker host=localhost user=**** password=****', closed: 0> checked out from pool
BEGIN
(Question|question) save_obj() start, batched
(Question|question) save_obj() table 'question' instance Question@0xc8a7b0 identity (<class '__main__.Question'>, (None,), None)
select nextval('"question_id_seq"')
None
INSERT INTO question (description, id, issue_id, provider_id, provider_service_id, wikilink) VALUES (%(description)s, %(id)s, %(issue_id)s, %(provider_id)s, %(provider_service_id)s, %(wikilink)s)
{'provider_id': None, 'description': 'testtest', 'provider_service_id': None, 'issue_id': None, 'wikilink': None, 'id': 2628L}
(Issue|issue) save_obj() start, batched
(Question|question) save_obj() start, batched
(Question|question) save_obj() table 'question' instance Question@0xc8a7b0 identity (<class '__main__.Question'>, (2628L,), None)
INSERT INTO question (description, id, issue_id, provider_id, provider_service_id, wikilink) VALUES (%(description)s, %(id)s, %(issue_id)s, %(provider_id)s, %(provider_service_id)s, %(wikilink)s)
{'provider_id': None, 'description': 'testtest', 'provider_service_id': None, 'issue_id': None, 'wikilink': None, 'id': 2628L}
ROLLBACK
Connection <connection object at 0x00BACF90; dsn: 'dbname=issuetracker host=localhost user=**** password=****', closed: 0> being returned to pool

The log shows the the UOWTask is doing:

Save Question(0xc8a7b0)

Twice in this case, and both times saving the Question with the same id. I haven't checked out the UOW code at all, and have simplified the test as much as possible. If more information is needed, fell free to contact me at warren at serverplus dot com.

Attachments: test2.py | sqalog.txt | test.py

Issue 2:
Migrated issue, originally created by Mario Benito Ríos (@Maberi)

With MySQL backend, on commit after creating a new object with "autoincrement" PK, key updating is triggering validator. See https://bitbucket.org/zzzeek/sqlalchemy/src/2de7f94739ec1873e1dce48797e1e6f12044cf4c/lib/sqlalchemy/orm/persistence.py?at=master#cl-650

Documentation says only user assignments trigger validations, so this case shouldn't trigger validation.