Re: [sqlalchemy] Implementing a "radio button" behavior?
Yes, I earlier said it was merge() that took effect before update() because that's how it looked like (didn't know about autoflush). Putting a sleep before update() and merge() showed that merge() issued no SQL because the autoflush (as you say) of the update() practically synced the session with the database. The update() does technically affect the row in teh database which is already selected and in session and dirtied, but not via primary key. What I'm doing is this: 1. select a row into session 2. assign some data to it (dirties it) 3. if this row's "flag" property is set to true, first set flag=false to all rows in the same group (the Update), this one included 4. now merge this row Or via plain SQL: 1. SELECT ... 2. UPDATE tablename SET flag=false WHERE group_id=123; 3. UPDATE tablename SET flag=true, ... WHERE primary_key=456; The end result is that only one row in the group can have the flag set to true. The blanket set flag=false is imho faster and cleaner than finding out which row in the group has the flag and then updating just that one row, before our main model row. No? I thought that session was only tracking changes via primary key so it never occurred to me that session would realize it is holding a row that's about to be updated, so it issues a flush first... Or am I misunderstanding what is going on here? Turning autoflush off did the trick and the updates are now in order. Many thanks for your help! .oO V Oo. On 09/09/2011 12:17 AM, Michael Bayer wrote: On Sep 8, 2011, at 6:00 PM, Vlad K. wrote: Yes that's how I know the order of events. I just checked the logs again and put some sleep() between update() and merge(). It appears that the update() does some kind of implicit flush because that "commits" the dirtied properties of the row instance BEFORE the update is issued, so that when merge() comes, everything appears in sync to the session. that's autoflush, which is part of the update() (earlier you said the merge() was taking effect before the update()).Its a little strange to change an attribute on an object that dirties it for update, then manually do an update() that affects the same object - is it the same attribute you're trying to update there ? Anyway, turn off autoflush. Here's some recipes to do that as needed: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush -- 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] Implementing a "radio button" behavior?
On Sep 8, 2011, at 6:00 PM, Vlad K. wrote: > > Yes that's how I know the order of events. I just checked the logs again and > put some sleep() between update() and merge(). It appears that the update() > does some kind of implicit flush because that "commits" the dirtied > properties of the row instance BEFORE the update is issued, so that when > merge() comes, everything appears in sync to the session. that's autoflush, which is part of the update() (earlier you said the merge() was taking effect before the update()).Its a little strange to change an attribute on an object that dirties it for update, then manually do an update() that affects the same object - is it the same attribute you're trying to update there ? Anyway, turn off autoflush. Here's some recipes to do that as needed: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush -- 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] Implementing a "radio button" behavior?
Yes that's how I know the order of events. I just checked the logs again
and put some sleep() between update() and merge(). It appears that the
update() does some kind of implicit flush because that "commits" the
dirtied properties of the row instance BEFORE the update is issued, so
that when merge() comes, everything appears in sync to the session.
So, in short, I'm doing this:
1. select row or new
2. assign some values to it
3. issue an update to a group of other rows (this should happen FIRST)
4. merge the row with db, flush, commit. (this should happen SECOND)
What I'm getting:
1. SELECTed row data
2. UPDATE on changed values (implicit flush of dirtied session data)
3. UPDATE as expected in step 3 above
4. COMMIT
.oO V Oo.
On 09/08/2011 10:04 PM, Michael Bayer wrote:
On Sep 8, 2011, at 3:32 PM, Vlad K. wrote:
For example the following:
row = session.query(Model).filter_by(pkey=pkey_value).first() or Model()
row.some_field = 123;
...
session.query(Model).filter_by(nonprimary_key=some_value).update({...}, false)
session.merge(row)
session.flush()
When flush() gets called, the merge() is executed (query sent to DB) before the
update called above it, in this particular example.
That isn't correct, query.update() emits UPDATE immediately. Do you have a
SQL log illustrating what is being emitted ?
--
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] Implementing a "radio button" behavior?
On Sep 8, 2011, at 3:32 PM, Vlad K. wrote:
>
> For example the following:
>
> row = session.query(Model).filter_by(pkey=pkey_value).first() or Model()
> row.some_field = 123;
> ...
>
>
> session.query(Model).filter_by(nonprimary_key=some_value).update({...}, false)
> session.merge(row)
> session.flush()
>
> When flush() gets called, the merge() is executed (query sent to DB) before
> the update called above it, in this particular example.
That isn't correct, query.update() emits UPDATE immediately. Do you have a
SQL log illustrating what is being emitted ?
>
>
>
> .oO V Oo.
>
>
> On 09/08/2011 04:37 PM, Michael Bayer wrote:
>> On Sep 8, 2011, at 9:32 AM, Vlad K. wrote:
>>
>>>
>>> As a "by the way" to this question, I've noticed that the order of queries
>>> given before flush() is not preserved for the flush(). Any way to enforce
>>> the order?
>> Trying to parse what this means. Suppose you did a single SELECT, loaded
>> five objects. Then changed them and did a flush. What is the "order of
>> queries" to be preserved?
>>
>> Guessing, perhaps you mean, the order in which a particular object became
>> present in the Session, that's the order in which UPDATE statements should
>> be emitted.UPDATE statements are in fact ordered in terms of the primary
>> key of the row. The reason for this is to minimize the chance of
>> deadlocks.Process A and process B both need to update primary key 1 and
>> 2 in a table. If process A starts with 1 and process B starts with 2, you
>> have a deadlock.So an ordering that is deterministic across
>> transactions, where PK ordering is a pretty good assumption in most cases,
>> is the best behavior here.
>>
>> If you need UPDATE statements in a specific order, you can A. emit flush()
>> specifically against a Session in which you're controlling what's "dirty",
>> B. use query.update(), C. use the Core SQL language instead of the ORM for
>> this particular series of operations (though query.update() likely a happy
>> medium).
>>
>>
>>
>>
>
> --
> 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.
>
--
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] Implementing a "radio button" behavior?
For example the following:
row = session.query(Model).filter_by(pkey=pkey_value).first() or Model()
row.some_field = 123;
...
session.query(Model).filter_by(nonprimary_key=some_value).update({...},
false)
session.merge(row)
session.flush()
When flush() gets called, the merge() is executed (query sent to DB)
before the update called above it, in this particular example.
.oO V Oo.
On 09/08/2011 04:37 PM, Michael Bayer wrote:
On Sep 8, 2011, at 9:32 AM, Vlad K. wrote:
As a "by the way" to this question, I've noticed that the order of queries
given before flush() is not preserved for the flush(). Any way to enforce the order?
Trying to parse what this means. Suppose you did a single SELECT, loaded five objects.
Then changed them and did a flush. What is the "order of queries" to be
preserved?
Guessing, perhaps you mean, the order in which a particular object became
present in the Session, that's the order in which UPDATE statements should be
emitted.UPDATE statements are in fact ordered in terms of the primary key
of the row. The reason for this is to minimize the chance of deadlocks.
Process A and process B both need to update primary key 1 and 2 in a table.
If process A starts with 1 and process B starts with 2, you have a deadlock.
So an ordering that is deterministic across transactions, where PK ordering is
a pretty good assumption in most cases, is the best behavior here.
If you need UPDATE statements in a specific order, you can A. emit flush() specifically
against a Session in which you're controlling what's "dirty", B. use
query.update(), C. use the Core SQL language instead of the ORM for this particular
series of operations (though query.update() likely a happy medium).
--
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] Implementing a "radio button" behavior?
On Sep 8, 2011, at 9:32 AM, Vlad K. wrote: > > > As a "by the way" to this question, I've noticed that the order of queries > given before flush() is not preserved for the flush(). Any way to enforce the > order? Trying to parse what this means. Suppose you did a single SELECT, loaded five objects. Then changed them and did a flush. What is the "order of queries" to be preserved? Guessing, perhaps you mean, the order in which a particular object became present in the Session, that's the order in which UPDATE statements should be emitted.UPDATE statements are in fact ordered in terms of the primary key of the row. The reason for this is to minimize the chance of deadlocks. Process A and process B both need to update primary key 1 and 2 in a table. If process A starts with 1 and process B starts with 2, you have a deadlock. So an ordering that is deterministic across transactions, where PK ordering is a pretty good assumption in most cases, is the best behavior here. If you need UPDATE statements in a specific order, you can A. emit flush() specifically against a Session in which you're controlling what's "dirty", B. use query.update(), C. use the Core SQL language instead of the ORM for this particular series of operations (though query.update() likely a happy medium). -- 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.
