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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
