Hi Everyone, Sorry to bother you all, I haven't found anything on Stack Overflow or in the documentation where users are only having ON CASCADE DELETE errors only *intermittently*. If I retry the deletion it works. I have so far been unable to figure out the conditions that leads to this problem, I've tried simultaneous access and high load but nothing has been consistently reproducible.
I am deleting an *organization* which should delete all *accounts* in the organization which should delete all *metadata* records for that account. In most cases it works. Very rarely, my service catches an exception. (psycopg2.IntegrityError) update or delete on table "accounts" violates foreign key constraint "metadata_account_id_fkey" on table "metadata" DETAIL: Key (id)=(28106) is still referenced from table "metadata". [SQL: 'DELETE FROM a.accounts WHERE a.accounts.id = %(id)s'] [parameters: {'id': 28106}] *PACKAGES* *python * *3.6.1* *SDK* *Name * *raven-python* *Version * *6.1.0* *psql (PostgreSQL) 9.4.6* Here is the definition of *organizations*, *accounts* and *metadata *tables: DROP TABLE IF EXISTS a.organizations CASCADE; CREATE TABLE a.organizations ( id bigserial NOT NULL, name varchar(50) NOT NULL ); ALTER TABLE a.organizations OWNER TO readwrite; ALTER TABLE a.organizations ADD CONSTRAINT organizations_pkey PRIMARY KEY (id); GRANT DELETE, SELECT, UPDATE, TRIGGER, INSERT, REFERENCES, TRUNCATE ON a.organizations TO readwrite; COMMIT; DROP TABLE IF EXISTS a.accounts CASCADE; CREATE TABLE a.accounts ( id serial NOT NULL, name varchar(50) NOT NULL, organization_id bigserial NOT NULL ); ALTER TABLE a.accounts OWNER TO readwrite; ALTER TABLE a.accounts ADD CONSTRAINT accounts_pkey PRIMARY KEY (id); ALTER TABLE accounts ADD CONSTRAINT accounts_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES a.organizations (id) ON UPDATE NO ACTION ON DELETE NO ACTION; GRANT DELETE, SELECT, UPDATE, TRIGGER, INSERT, REFERENCES, TRUNCATE ON a.accounts TO readwrite; COMMIT; DROP TABLE IF EXISTS a.metadata CASCADE; CREATE TABLE a.metadata ( id serial NOT NULL, account_id integer NOT NULL, gen_metadata text NOT NULL ); ALTER TABLE a.metadata OWNER TO readwrite; ALTER TABLE a.metadata ADD CONSTRAINT metadata_pkey PRIMARY KEY (id); ALTER TABLE metadata ADD CONSTRAINT metadata_account_id_fkey FOREIGN KEY (account_id) REFERENCES a.accounts (id) ON UPDATE NO ACTION ON DELETE NO ACTION; GRANT DELETE, SELECT, UPDATE, TRIGGER, INSERT, REFERENCES, TRUNCATE ON a. metadata TO readwrite; COMMIT; I defined the ON DELETE CASCADE relationships at the ORM level so that the cascade delete occurs only when the parent is deleted: class Accounts(BASE): """ORM model for the a.accounts table""" __tablename__ = 'accounts' # One to Many gen_metadata = relationship('Metadata', back_populates='account', cascade='all, delete-orphan') # Many to One, do not cascade delete organization = relationship('Organizations', back_populates='accounts') class Organizations(BASE): """ORM model for the a.organizations table""" __tablename__ = 'organizations' # One to Many accounts = relationship('Accounts', back_populates='organization', cascade='all, delete-orphan') class Metadata(BASE): """ORM model for the a.metadata table""" __tablename__ = 'metadata' # Many to One account = relationship('Accounts', back_populates='gen_metadata') And here is my Python to delete the *organization*: def delete_organization(org_id): """ Helper method to delete an organization. :param org_id: the organization to delete """ org = DB.session.query(Organizations).get(org_id) with DB.session.no_autoflush: if org is None: raise my_error() for i in range(max_retries): try: DB.session.delete(org) DB.session.flush() except exc.IntegrityError: DB.session.rollback() if i + 1 != max_retries: continue # retry else: raise # if we fail after max_retries, then re-raise the exception else: break # stop retrying on success # Do some other stuff DB.session.commit() return my_response() raise my_error() I would appreciate any and all suggestions. Thank you all in advance for your help! Regards, Chris -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.