SQLAlchemy ORM - Deferring constraint checking

Question

I have a unique constraint on one of my tables that I would like to defer, which is something Postgresql supports from what I understand, but I can't seem to find where I can tell my operation to do so in SQLAlchemy when using the ORM (in general, not just specifically this case). I am using the bulk_update_mappings() function, and the constraint is the 2nd under __table_args__. Is this something I need to use SQLAlchemy Core or create my own SQL statement to achieve?

class Question(Base):
    QuestionType = enum.Enum('QuestionType', 'mcq')
    __tablename__ = 'questions'
    id = Column(Integer, primary_key=True)
    type = Column(Enum(_QuestionType), nullable=False)
    description = Column(String, nullable=False)
    question_order = Column(Integer, nullable=False)
    question_set_id = Column(Integer, ForeignKey('question_sets.id', ondelete='cascade'), nullable=False)

    question_set = relationship('QuestionSet', back_populates='questions')

    __table_args__ = (
        UniqueConstraint('question_set_id', 'description'),
        UniqueConstraint('question_set_id', 'question_order', deferrable=True)
    )
    __mapper_args__ = {
        'polymorphic_identity': 'question',
        'polymorphic_on': type,
    }

#from another class
def reorder(self, new_order, db):
    order = [{'id':i, 'question_order': index} for index, i in enumerate(new_order)]
    db.bulk_update_mappings(Question, order)
    db.commit()

Run `db.execute('SET CONSTRAINTS ALL DEFERRED')` before your bulk operation in order to defer all deferrable constraints in the current transaction: https://www.postgresql.org/docs/current/static/sql-set-constraints.html. You could opt to defer the unique constraint only, if you know its name. — Ilja Everilä, Dec 31 '17 at 09:04
This works. I gave my constraint a name and did `db.execute('SET CONSTRAINTS unique_order DEFERRED;')` — nav, Dec 31 '17 at 20:27
Related, though not a dupe: https://stackoverflow.com/questions/32533592/how-to-set-constraints-deferred-in-sqlalchemy-core, https://stackoverflow.com/questions/14210453/how-to-set-constraints-deferred-in-sqlalchemy-expression-language — Ilja Everilä, Jan 02 '18 at 08:28

Ilja Everilä · Accepted Answer · 2018-01-02T08:39:23.730

6

Given that db is your session instance, run

db.execute('SET CONSTRAINTS ALL DEFERRED')

before your bulk operation in order to defer all deferrable constraints in the current transaction. Note that not all constraints are deferrable, even if they're declared as such. You could opt to defer the unique constraint only, if you know its name, such as unique_order:

def reorder(self, new_order, db):
    order = [{'id':i, 'question_order': index} for index, i in enumerate(new_order)]
    db.execute('SET CONSTRAINTS unique_order DEFERRED')
    db.bulk_update_mappings(Question, order)
    db.commit()

edited Jan 02 '18 at 08:39

answered Jan 02 '18 at 08:31

Ilja Everilä

50,538
7
126
127

After the bulk operation, is it necessary to `SET CONSTRAINTS ALL IMMEDIATE`? Or does that happen automatically following the `commit()`? – Jean Monet Feb 12 '21 at 21:32
1

`SET CONSTRAINTS` controls constraints within the transaction, so after a commit its effects are gone. – Ilja Everilä Feb 13 '21 at 12:32

SQLAlchemy ORM - Deferring constraint checking

1 Answers1