Re: [sqlalchemy] Question on the precedence of insert and delete in session flush
And the recipe I have used is to issue a flush() after the deletes and before the inserts. In most cases this is sufficient to get things to work in the right order. I can imagine that there are some complex data management use cases where that is not sufficient. It works for your sample as the comment in your code indicates. This should not change the performance of the app since we are only changing the sequence of statements, not introducing additional transaction overhead. When I run into scenarios where the sequence of issuing SQL is significant, I'm not sure how much control I want to give up to the UOW code. After all if sequence is important this could very well be application dependent and the UOW might have to get really sophisticated to guess right. That increases the risk of taking a performance hit in the 90+% of cases where it doesn't matter. -- Mike Conley -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Question on the precedence of insert and delete in session flush
On Jul 27, 2011, at 3:34 AM, ammar azif wrote: > Hi, > > I am using SQLAlchemy 0.6.4 with postgres db. I have two tables - users and > addresses tables with addresses table having a foreign key constraint > referencing the users table. Each address record is identified by a unique > constraint key 'email_address'. > In my test case, each user instance have a collection of addresses. For each > user instance, I want to delete every address instance in the addresses > collection that the user instance has and then add new address instances > (they may have the same unique key that the previously deleted address > instance had). The problem I am having now is that at the end of the flush > call, unique constraint error for 'email_address' from addresses table is > thrown even though delete operation is done earlier than insert. Looking at > the echo output, INSERTs are indeed done first than DELETEs. The work around > that I have now is to call flush() right after the deletion of address > instances in each user. > > My question is - what is the precedence of insert, delete and update in > session flush? It would also be helpful if someone can explain the overview > of the mechanics of flushing in SQLAlchemy. > Attached is the python script that I wrote to understand why this problem > mentioned above happens. Right so, the unit of work was rewritten in version 0.6, because it was desperately needed, but also one issue I wanted to see if it could be solved was the one case that the UOW can't handle currently without direct intervention, that of the unique constraint that needs to be deleted before a new one is inserted. It should be noted that for an object where the primary key itself is the thing that might conflict for an outgoing / incoming situation, the UOW turns that operation into an UPDATE.But for an arbitrary column with a unique on it we don't have the mechanics in place to do it that way, nor would I want to . Id much rather have a delete + insert be a DELETE + INSERT in all cases. So with the UOW rewrite, its very clear cut how the order of steps is determined, and the architecture is fairly amenable to a strategy that would freely mix DELETE, INSERT and UPDATE. However, I didn't go this far with the rewrite. I tried a bit, trying to have the topological sort also take into account individual INSERTS that need to come before DELETES, instead of it being an overarching "two step" process, but there seemed to be a lot of really mindbendy types of cases where the fact that DELETES are unconditionally after the INSERT/UPDATEs makes things work out really well.The 0.6 rewrite had the highest priority on not introducing any new bugs vs. the previous version, which had been very stable for a long time (although impossible to work with) so I didn't go further with that idea, at that time. (There of course is no reason someone can't try to work with it some more) The current scheme is INSERT/UPDATES first, DELETES second, and the original idea is mostly from Hibernate's procedure (http://docs.jboss.org/hibernate/core/3.3/reference/en/html/objectstate.html#objectstate-flushing), which I seem to recall was not as verbose as that particular description is now. I have an architectural document I can send you under separate cover (it is not public yet). -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Question on the precedence of insert and delete in session flush
Hi,
I am using SQLAlchemy 0.6.4 with postgres db. I have two tables - users and
addresses tables with addresses table having a foreign key constraint
referencing the users table. Each address record is identified by a unique
constraint key 'email_address'.
In my test case, each user instance have a collection of addresses. For each
user instance, I want to delete every address instance in the addresses
collection that the user instance has and then add new address instances
(they may have the same unique key that the previously deleted address
instance had). The problem I am having now is that at the end of the flush
call, unique constraint error for 'email_address' from addresses table is
thrown even though delete operation is done earlier than insert. Looking at
the echo output, INSERTs are indeed done first than DELETEs. The work around
that I have now is to call flush() right after the deletion of address
instances in each user.
My question is - what is the precedence of insert, delete and update in
session flush? It would also be helpful if someone can explain the overview
of the mechanics of flushing in SQLAlchemy.
Attached is the python script that I wrote to understand why this problem
mentioned above happens.
Thanks.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column, UniqueConstraint, ForeignKeyConstraint
from sqlalchemy.types import String, Integer
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy import create_engine
engine = create_engine('postgres://postgres:data01@localhost:5432/flush_test')
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
__table_args__ = (
UniqueConstraint('name',
name='users_name_key'),
{})
id = Column(Integer, primary_key=True)
name = Column(String(20), nullable=False)
def __init__(self, name):
self.name = name
def __repr__(self):
return "" % self.name
class Address(Base):
__tablename__ = 'addresses'
__table_args__ = (
UniqueConstraint('email_address',
name='addresses_email_address_key'),
ForeignKeyConstraint(['username'],
['users.name'],
onupdate='cascade'),
{}
)
id = Column(Integer, primary_key=True)
email_address = Column(String(20), nullable=False)
username = Column(String(20), nullable=False)
user = relationship(User, backref=backref('addresses', cascade='all, delete-orphan'))
def __init__(self, email_address):
self.email_address = email_address
def __repr__(self):
return "" % self.email_address
metadata = Base.metadata
metadata.create_all(engine)
Session = sessionmaker(bind=engine, autoflush=False)
session = Session()
session.rollback()
# begin init code that puts the persisted instance into the database
if session.query(User).filter_by(name='user1').count()==0:
user1 = User('user1')
user1.addresses.append(Address('us...@email.com'))
session.add(user1)
session.commit()
else:
user1=session.query(User).filter_by(name='user1').one()
if len(user1.addresses)==0:
user1.addresses.append(Address('us...@email.com'))
session.commit()
# end init code
engine.echo = True
for address in user1.addresses:
session.delete(address)
user1.addresses.append(Address('us...@email.com'))
# After the flush line below unique constraint error for address table is thrown,
# echo output shows that insertion of child item was done first before delete, although we call delete first.
# This does not happen if we flush after delete.
session.flush()
