Re: [sqlalchemy] Re: ObjectDeletedError in master/slave configuration

2020-03-26 Thread Jonathan Vanasco 


On Thursday, March 26, 2020 at 2:53:08 PM UTC-4, Mike Bayer wrote:
>
> is the issue that your follower database is only updating 
> asynchronously?   I would likely organize the application to simply use two 
> different Session objects, one for master one for follower.   Trying to do 
> it on a per-query basis is needlessly implicit and complicated.
>

 In my applications, I have explicit Primary/Writer and Secondary/Reader 
sessions.  As a convenience, I will create a shortcut to proxy one of them 
onto a request(web)/context object as `.dbSession`.  Certain logic flows 
will only utilize the Primary/Writer, other logic flows will only utilize 
the Secondary/Reader.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/5a92dc19-0d7a-41e0-bc6f-47b96ac1d32b%40googlegroups.com.

Re: [sqlalchemy] Re: ObjectDeletedError in master/slave configuration

2020-03-26 Thread Mike Bayer 


On Thu, Mar 26, 2020, at 2:45 PM, Colton Allen wrote:
> Aside from disabling expire_on_commit, any thoughts on how I can prevent this 
> error? I guess I just need a method to force the attribute refresh to use the 
> master database. I'm just not sure where I should put that. Thoughts?

is the issue that your follower database is only updating asynchronously? I 
would likely organize the application to simply use two different Session 
objects, one for master one for follower. Trying to do it on a per-query basis 
is needlessly implicit and complicated. Individual service methods would 
declare whether they are "read only" or "writer" and they start out with one or 
the other session/engine. That's how we do it in Openstack. thinking in terms 
of logical service methods rather than individual queries is the way to go.


> 
> I think one of my previous comments got lost because of formatting. Quoting 
> it here for safety.
> 
> "I agree with your assessment. I think its because every time I call 
> "session". I'm actually saying "scoped_session(session_maker)()". So the 
> _flushing attribute will be reset because its a new session instance."
> 
> 
> 
> On Thursday, March 26, 2020 at 1:35:14 PM UTC-5, Mike Bayer wrote:
>> 
>> 
>> On Thu, Mar 26, 2020, at 2:18 PM, Colton Allen wrote:
>>> > You can adjust `expire_on_commit` if you're only doing short-term 
>>> > read-only actions.
>>> 
>>> Can you expand on this? Or link to docs/blog so I can do some research. 
>>> Google hasn't helped me so far. Why would I want to expire after every 
>>> commit?
>> 
>> because once the transaction is completed, there are other transactions 
>> going on in a database concurrently which can change the state of the 
>> objects as they are represented in the database. in order that when you next 
>> access these local objects so that they have the correct state, they are 
>> automatically expired. However this behavior is not desirable in many cases, 
>> so this flag is very commonly used to disable this behavior when you are not 
>> concerned about your objects having stale data relative to other 
>> transactions going on, when the new transaction begins.
>> 
>> this behavior is described at: 
>> https://docs.sqlalchemy.org/en/13/orm/session_basics.html#committing
>> 
>> 
>> 
>> 
>>> 
>>> ---
>>> 
>>> I agree with your assessment. I think its because every time I call 
>>> "session". I'm actually saying "session_maker()". So the _flushing 
>>> attribute will be reset because its a new session instance.
>>> 
>>> On Thursday, March 26, 2020 at 1:02:18 PM UTC-5, Jonathan Vanasco wrote:
 My first guess is two things are going on:
 
 1. This is a behavior of `expire_on_commit` on the session. Once you 
 commit on the Primary database, the object is stale.
 https://docs.sqlalchemy.org/en/13/orm/session_api.html
 
 2. The session is then trying to read off a Secondary database, but the 
 row has not yet synced.
 
 You can adjust `expire_on_commit` if you're only doing short-term 
 read-only actions. However, I would explore to ensure this is trying to 
 read off the other database and why.
>>> 

>>> --
>>> 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 sqlal...@googlegroups.com.
>>> To view this discussion on the web visit 
>>> https://groups.google.com/d/msgid/sqlalchemy/8185b96d-35cb-4973-a9cf-1e572ebd643b%40googlegroups.com
>>>  
>>> .
>> 
> 

> --
>  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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/dd5c4d05-5af3-451e-b965-52ac50b432ac%40googlegroups.com
>  
> .

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

Re: [sqlalchemy] Re: ObjectDeletedError in master/slave configuration

2020-03-26 Thread Colton Allen 
Aside from disabling expire_on_commit, any thoughts on how I can prevent 
this error?  I guess I just need a method to force the attribute refresh to 
use the master database.  I'm just not sure where I should put that.  
Thoughts?

I think one of my previous comments got lost because of formatting.  
Quoting it here for safety.

"I agree with your assessment.  I think its because every time I call 
"session".  I'm actually saying "scoped_session(session_maker)()".  So the 
_flushing attribute will be reset because its a new session instance."

 

On Thursday, March 26, 2020 at 1:35:14 PM UTC-5, Mike Bayer wrote:
>
>
>
> On Thu, Mar 26, 2020, at 2:18 PM, Colton Allen wrote:
>
> > You can adjust `expire_on_commit` if you're only doing short-term 
> read-only actions.
>
> Can you expand on this?  Or link to docs/blog so I can do some research.  
> Google hasn't helped me so far.  Why would I want to expire after every 
> commit?
>
>
> because once the transaction is completed, there are other transactions 
> going on in a database concurrently which can change the state of the 
> objects as they are represented in the database.  in order that when you 
> next access these local objects so that they have the correct state, they 
> are automatically expired.   However this behavior is not desirable in many 
> cases, so this flag is very commonly used to disable this behavior when you 
> are not concerned about your objects having stale data relative to other 
> transactions going on, when the new transaction begins.
>
> this behavior is described at: 
> https://docs.sqlalchemy.org/en/13/orm/session_basics.html#committing
>
>
>
>
>
> ---
>
> I agree with your assessment.  I think its because every time I call 
> "session".  I'm actually saying "session_maker()".  So the _flushing 
> attribute will be reset because its a new session instance.
>
> On Thursday, March 26, 2020 at 1:02:18 PM UTC-5, Jonathan Vanasco wrote:
>
> My first guess is two things are going on:
>
> 1. This is a behavior of `expire_on_commit` on the session.  Once you 
> commit on the Primary database, the object is stale.
>https://docs.sqlalchemy.org/en/13/orm/session_api.html
>
> 2. The session is then trying to read off a Secondary database, but the 
> row has not yet synced.
>
> You can adjust `expire_on_commit` if you're only doing short-term 
> read-only actions. However,  I would explore to ensure this is trying to 
> read off the other database and why.
>
>
> --
> 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 sqlal...@googlegroups.com .
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/8185b96d-35cb-4973-a9cf-1e572ebd643b%40googlegroups.com
>  
> 
> .
>
>
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/dd5c4d05-5af3-451e-b965-52ac50b432ac%40googlegroups.com.

Re: [sqlalchemy] Re: ObjectDeletedError in master/slave configuration

2020-03-26 Thread Mike Bayer 


On Thu, Mar 26, 2020, at 2:18 PM, Colton Allen wrote:
> > You can adjust `expire_on_commit` if you're only doing short-term read-only 
> > actions.
> 
> Can you expand on this? Or link to docs/blog so I can do some research. 
> Google hasn't helped me so far. Why would I want to expire after every commit?

because once the transaction is completed, there are other transactions going 
on in a database concurrently which can change the state of the objects as they 
are represented in the database. in order that when you next access these local 
objects so that they have the correct state, they are automatically expired. 
However this behavior is not desirable in many cases, so this flag is very 
commonly used to disable this behavior when you are not concerned about your 
objects having stale data relative to other transactions going on, when the new 
transaction begins.

this behavior is described at: 
https://docs.sqlalchemy.org/en/13/orm/session_basics.html#committing




> 
> ---
> 
> I agree with your assessment. I think its because every time I call 
> "session". I'm actually saying "session_maker()". So the _flushing attribute 
> will be reset because its a new session instance.
> 
> On Thursday, March 26, 2020 at 1:02:18 PM UTC-5, Jonathan Vanasco wrote:
>> My first guess is two things are going on:
>> 
>> 1. This is a behavior of `expire_on_commit` on the session. Once you commit 
>> on the Primary database, the object is stale.
>> https://docs.sqlalchemy.org/en/13/orm/session_api.html
>> 
>> 2. The session is then trying to read off a Secondary database, but the row 
>> has not yet synced.
>> 
>> You can adjust `expire_on_commit` if you're only doing short-term read-only 
>> actions. However, I would explore to ensure this is trying to read off the 
>> other database and why.
> 

> --
>  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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/8185b96d-35cb-4973-a9cf-1e572ebd643b%40googlegroups.com
>  
> .

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/2ae3206b-d8e6-43ef-a661-b52bcc88b6db%40www.fastmail.com.

[sqlalchemy] Re: ObjectDeletedError in master/slave configuration

2020-03-26 Thread Jonathan Vanasco 
By default, SqlAlchemy has `expire_on_commit=True`. 

I'm going to poorly restate most of what Mike Bayer has told me in the 
past:  the rationale behind this- an active Session is supposed to mirror 
the current state in the database; within a transaction we know the object 
values are equal to the table values.  Once the transaction ends via 
`commit`, the object is no longer in sync - another transaction may have 
modified that database row.  

In your example, note these 2 lines from the stacktrace:

File "sqlalchemy/orm/attributes.py", line 669, in get
  value = state._load_expired(state, passive)

when you invoked a `commit`, SqlAlchemy expired the object (after the 
commit).

when you invoked `print()`, SqlAclhemy recognized the object as expired and 
is now trying to load the expired attributes so it can print them for you.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/bdb5dde6-ff15-472d-a3da-4aa5b980fdfc%40googlegroups.com.

[sqlalchemy] Re: ObjectDeletedError in master/slave configuration

2020-03-26 Thread Colton Allen 
> You can adjust `expire_on_commit` if you're only doing short-term 
read-only actions.

Can you expand on this?  Or link to docs/blog so I can do some research.  
Google hasn't helped me so far.  Why would I want to expire after every 
commit?

---

I agree with your assessment.  I think its because every time I call 
"session".  I'm actually saying "session_maker()".  So the _flushing 
attribute will be reset because its a new session instance.

On Thursday, March 26, 2020 at 1:02:18 PM UTC-5, Jonathan Vanasco wrote:
>
> My first guess is two things are going on:
>
> 1. This is a behavior of `expire_on_commit` on the session.  Once you 
> commit on the Primary database, the object is stale.
>https://docs.sqlalchemy.org/en/13/orm/session_api.html
>
> 2. The session is then trying to read off a Secondary database, but the 
> row has not yet synced.
>
> You can adjust `expire_on_commit` if you're only doing short-term 
> read-only actions. However,  I would explore to ensure this is trying to 
> read off the other database and why.
>

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/8185b96d-35cb-4973-a9cf-1e572ebd643b%40googlegroups.com.

[sqlalchemy] Re: ObjectDeletedError in master/slave configuration

2020-03-26 Thread Jonathan Vanasco 
My first guess is two things are going on:

1. This is a behavior of `expire_on_commit` on the session.  Once you 
commit on the Primary database, the object is stale.
   https://docs.sqlalchemy.org/en/13/orm/session_api.html

2. The session is then trying to read off a Secondary database, but the row 
has not yet synced.

You can adjust `expire_on_commit` if you're only doing short-term read-only 
actions. However,  I would explore to ensure this is trying to read off the 
other database and why.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/7cdd3a62-63d3-4b3c-8366-4748fc2256cf%40googlegroups.com.