Re: [sqlalchemy] Session.flush() before Session.begin()

[Jonathan Vanasco]

On Thursday, February 4, 2016 at 9:36:43 AM UTC-5, Michael Bayer wrote:

You might find it a better approach overall to use a separate Session 
> for persistence, have your users deal with entirely detached objects and 
> then just copy their state to the Session when you're ready to persist 
> them, using merge() or something homegrown. 


I'm assuming the desire to not have long transactions is because you have 
multiple concurrent users.  if that's the case, you'll always run into 
issues with a ORM (sqlalchemy or not).

Personally, I would track changes into a `dict` (instead the ORM objects) 
and on "Save" I would start a transaction and copy the data over.  I'd also 
track a "revision id" or "revision timestamp" on each object , and use that 
as a comparison to ensure that any operations happen on the same revision.

-- 
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.

Re: [sqlalchemy] Session.flush() before Session.begin()

[Mike Bayer]

On 02/03/2016 11:10 PM, James Emerton wrote:






im not sure why you need to use begin() at all?  in autocommit mode, the user 
presses save, you just call flush().  flush() always uses a transaction 
internally.  That's originally how the Session was meant to be used, having an 
explicitly open transaction was an optional feature.


Unfortunately, some processes in our application execute SQL directly, often 
operating on records which were very recently inserted into the database. Our 
models have save methods that validate the record and then issue a flush. This 
means that flush will be called several times during the course of a 
transaction. (Yay legacy code!)


Since you're looking for an ORM that can rollback a transaction and 
simultaneously reset the state of the objects to exactly what they were at the 
beginning of that transaction, there is no ORM I'm aware of that can do this 
any differently than SQLAlchemy does.   More simplistic ORMs like Django / 
Peewee etc. have no concept of expiration-on-rollback, and after a transaction 
rollback your objects have exactly the same in-memory state as before.   
SQLAlchemy offers this mode of operation both through the 
_enable_transaction_accounting=False (with small bugfixes apparently) as well 
as the use of an external transaction.


The only requirement is that attributes and instances that were dirty before 
the transaction began must be dirty after a rollback.


I’m not concerned about instance attributes being rolled back to their 
previous value (we didn’t have this before.)


If we use _enable_transaction_accounting=False and issue multiple calls 
to flush during the course of a transaction is


there a way to restore the dirty state to the point before the 
transaction began?



there are two things that are possible.  not touching the object's 
attributes at all, in which case they have at all times whatever you 
last put into them, or there is expiring them completely and restoring 
whatever is in the database at the moment.   There is no ability to 
reset in-memory state to some previous snapshot.


You might find it a better approach overall to use a separate Session 
for persistence, have your users deal with entirely detached objects and 
then just copy their state to the Session when you're ready to persist 
them, using merge() or something homegrown.









Thanks,
   James




--
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.

Re: [sqlalchemy] Session.flush() before Session.begin()

[Mike Bayer]

On 02/03/2016 09:39 PM, jemer...@spiresystems.com wrote:

We're using SQLAlchemy in a GUI application and have been experiencing
some challenges integrating the ORM Session with our usage model. (This
is difficult to change as we have ported from a legacy system to
SQLAlchemy.)

We're currently using autocommit=True. When the user edits a record we
create a new session, load the record from the database, and allow them
to interact with the data. When they save the record we validate and
flush the changes. Since we don't want to hold database transactions
open for a long period of time, we start a transaction once the user has
clicked save. Unfortunately, as soon as we call Session.begin() the
session will call flush() outside the context of the transaction.


ah, that's some old behavior but yes, that will happen *if you have 
dirty state* in the session.   The Session can't keep it's contract of 
being able to roll back state to a point that matches what's in the 
database if it doesn't flush out dirty state beforehand; because when a 
rollback happens, it wants to just expire all the state, which means 
your pre-transaction changes will be lost.


You can disable this by passing the flag 
_enable_transaction_accounting=False, but you then the state of the 
session will be out of sync after a rollback.





We have tried a couple approaches to work around this, to very limited
success:

First, we set _enable_transaction_accounting=False, but this causes
Session._expunge_states() to fail with an AttributeError accessing
SessionTransaction._deleted.


well that's just a bug.  Feel free to file a test case for that and it's 
likely a simple fix.




Subsequently we tried using Connection.begin() to "hide" the transaction
from the Session, but this causes problems when there is an error
causing the transaction to rollback as the Session and the database are
now inconsistent with each other.


that would be my next idea, so if you do need to have rollbacks cause 
the object state to reset itself and remain consistent with the 
beginning of the transaction, this indicates 
_enable_transaction_accounting won't work for you.


If you need the Session to expire its state such that it matches what 
was present at the beginning of the transaction, then I'm not sure how 
you intend to have dirty state from before the transaction lying around 
unflushed. Syncing up with a rollback necessarily means that we 
expire all in-memory state and we have to load it again.   Your 
pre-transaction changes however would be lost.


Only the approach of doing a full in-memory rollback to the previous 
state of the objects would get around this, but there is no such ORM in 
the Python world I'm familiar with which performs such a task, while 
it's "simple" for things like scalar attributes, it quickly becomes 
massively complicated for collections and object graphs.






Most recently, I put on my mad science gear and have attempted to pickle
the __dict__ and InstanceState to restore these on rollback, but this
doesn't seem to work. (My understanding of session internals is still
fairly weak, so perhaps I'm simply missing something or perhaps this is
an unworkable solution.)

It's my sincere hope that we can find a solution that does not involve
replacing SQLAlchemy with another ORM.


im not sure why you need to use begin() at all?  in autocommit mode, the 
user presses save, you just call flush().  flush() always uses a 
transaction internally.  That's originally how the Session was meant to 
be used, having an explicitly open transaction was an optional feature.


Since you're looking for an ORM that can rollback a transaction and 
simultaneously reset the state of the objects to exactly what they were 
at the beginning of that transaction, there is no ORM I'm aware of that 
can do this any differently than SQLAlchemy does.   More simplistic ORMs 
like Django / Peewee etc. have no concept of expiration-on-rollback, and 
after a transaction rollback your objects have exactly the same 
in-memory state as before.   SQLAlchemy offers this mode of operation 
both through the _enable_transaction_accounting=False (with small 
bugfixes apparently) as well as the use of an external transaction.








Thanks,
   James Emerton

--
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.


--
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 

Re: [sqlalchemy] Session.flush() before Session.begin()

[James Emerton]

> On Feb 3, 2016, at 7:07 PM, Mike Bayer  wrote:
> 
> On 02/03/2016 09:39 PM, jemer...@spiresystems.com wrote:
>> We're using SQLAlchemy in a GUI application and have been experiencing
>> some challenges integrating the ORM Session with our usage model. (This
>> is difficult to change as we have ported from a legacy system to
>> SQLAlchemy.)
>> 
>> We're currently using autocommit=True. When the user edits a record we
>> create a new session, load the record from the database, and allow them
>> to interact with the data. When they save the record we validate and
>> flush the changes. Since we don't want to hold database transactions
>> open for a long period of time, we start a transaction once the user has
>> clicked save. Unfortunately, as soon as we call Session.begin() the
>> session will call flush() outside the context of the transaction.
> 
> ah, that's some old behavior but yes, that will happen *if you have dirty 
> state* in the session.   The Session can't keep it's contract of being able 
> to roll back state to a point that matches what's in the database if it 
> doesn't flush out dirty state beforehand; because when a rollback happens, it 
> wants to just expire all the state, which means your pre-transaction changes 
> will be lost.
> 
> You can disable this by passing the flag 
> _enable_transaction_accounting=False, but you then the state of the session 
> will be out of sync after a rollback.

Since the record objects are directly edited by the user, we will be 
accumulating dirty state outside the context of the transaction. (We also 
experimented with holding a transaction open while the record was being edited, 
but this wasn’t workable for several reasons.)

>> We have tried a couple approaches to work around this, to very limited
>> success:
>> 
>> First, we set _enable_transaction_accounting=False, but this causes
>> Session._expunge_states() to fail with an AttributeError accessing
>> SessionTransaction._deleted.
> 
> well that's just a bug.  Feel free to file a test case for that and it's 
> likely a simple fix.

Will do.

> 
>> 
>> Subsequently we tried using Connection.begin() to "hide" the transaction
>> from the Session, but this causes problems when there is an error
>> causing the transaction to rollback as the Session and the database are
>> now inconsistent with each other.
> 
> that would be my next idea, so if you do need to have rollbacks cause the 
> object state to reset itself and remain consistent with the beginning of the 
> transaction, this indicates _enable_transaction_accounting won't work for you.
> 
> If you need the Session to expire its state such that it matches what was 
> present at the beginning of the transaction, then I'm not sure how you intend 
> to have dirty state from before the transaction lying around unflushed. 
> Syncing up with a rollback necessarily means that we expire all in-memory 
> state and we have to load it again.   Your pre-transaction changes however 
> would be lost.
> 
> Only the approach of doing a full in-memory rollback to the previous state of 
> the objects would get around this, but there is no such ORM in the Python 
> world I'm familiar with which performs such a task, while it's "simple" for 
> things like scalar attributes, it quickly becomes massively complicated for 
> collections and object graphs.
> 
> 
> 
>> 
>> Most recently, I put on my mad science gear and have attempted to pickle
>> the __dict__ and InstanceState to restore these on rollback, but this
>> doesn't seem to work. (My understanding of session internals is still
>> fairly weak, so perhaps I'm simply missing something or perhaps this is
>> an unworkable solution.)
>> 
>> It's my sincere hope that we can find a solution that does not involve
>> replacing SQLAlchemy with another ORM.
> 
> im not sure why you need to use begin() at all?  in autocommit mode, the user 
> presses save, you just call flush().  flush() always uses a transaction 
> internally.  That's originally how the Session was meant to be used, having 
> an explicitly open transaction was an optional feature.

Unfortunately, some processes in our application execute SQL directly, often 
operating on records which were very recently inserted into the database. Our 
models have save methods that validate the record and then issue a flush. This 
means that flush will be called several times during the course of a 
transaction. (Yay legacy code!)

> Since you're looking for an ORM that can rollback a transaction and 
> simultaneously reset the state of the objects to exactly what they were at 
> the beginning of that transaction, there is no ORM I'm aware of that can do 
> this any differently than SQLAlchemy does.   More simplistic ORMs like Django 
> / Peewee etc. have no concept of expiration-on-rollback, and after a 
> transaction rollback your objects have exactly the same in-memory state as 
> before.   SQLAlchemy offers this mode of operation both