Re: [sqlalchemy] Re: Help with configuring connect_timeout setting for when we a high available postgres database drops
Ok, so we had a retry decorator around a group of ORM calls. This seems to
be the issue. If we wrap each call instead, things are much better.
I wonder if the transaction rollback is causing issues.
Karim
On Monday, 2 March 2020 10:28:41 UTC-8, Karim Gillani wrote:
>
> Sadly we are still working on the ORM calls for the connect timeout.
> Basic calls are working, of course ours are lot more complex so trying to
> get one of the complex ones in the test script.
>
> At the same time, I am trying the pg8000 driver instead of psycopg2. That
> driver seems to be much more promising.
>
> Very strange and the mystery continues..
>
> Karim
>
> On Thursday, 27 February 2020 14:30:32 UTC-8, Karim Gillani wrote:
>>
>> Latest update:
>>
>> 1. Psycopyg2 works with connect_timeout - Direct SQL call
>> 2. sqlalchemy works with connect_timeout (Direct SQL call no ORM)
>> 3. Flask-sqlalchemy works with connect_timeout (Direct SQL call, no ORM)
>>
>> Next up on our troubleshooting tree..
>>
>> Test sqlalchemy with ORM calls with Connect_timeout..
>>
>> Weird...
>>
>> If anyone wants to look at our code to see what our app is doing:
>> https://github.com/bcgov/queue-management/api
>>
>> Karim
>>
>>
>> On Thursday, 27 February 2020 10:24:25 UTC-8, Mike Bayer wrote:
>>>
>>> OK so that script I gave you, the exact SQLAlchemy equivalent is:
>>>
>>>
>>> from sqlalchemy import create_engine
>>>
>>>
>>> e = create_engine("postgresql+psycopg2://scott:tiger@localhost/test",
>>> connect_args={"connect_timeout": 2})
>>> c = e.connect()
>>>
>>>
>>> it also will accept it as:
>>>
>>> e =
>>> create_engine("postgresql+psycopg2://scott:tiger@localhost/test?connect_timeout=2")
>>>
>>> however, in the above case the timeout is passed as a string "2" and not
>>> an integer, which may interfere with it working, im not sure. the
>>> separate connect_args approach is safer.
>>>
>>>
>>>
>>> On Thu, Feb 27, 2020, at 12:57 PM, Karim Gillani wrote:
>>>
>>> Thank you for the great idea. I did not see the forest through the
>>> trees. Haha.
>>>
>>> We have the pscopyg2 script running and things seems to be working fine
>>> with that. We can see without the connect_timeout that the delay is about
>>> 2 minutes and with the connect_timeout set, the delay is only 2 seconds.
>>>
>>> Next, we are going to wrap the script (if possible) with Alchemy only
>>> and test.
>>> Then after that, we will wrap flask-alchemy.
>>>
>>> Thanks
>>> Karim
>>>
>>>
>>>
>>> On Thursday, 27 February 2020 06:48:00 UTC-8, Mike Bayer wrote:
>>>
>>> Can you write a script that uses psycopg2 directly and see if you can
>>> replicate the issue in that way? this would elimiante SQLAlchemy as part
>>> of the issue and you can interact with the psycopg2 developers directly:
>>>
>>> import psycopg2
>>>
>>> connection = psycopg2.connect(
>>> user="scott",
>>> password="tiger",
>>> host="localhost",
>>> database="test",
>>> connect_timeout=2,
>>> )
>>>
>>>
>>> There's no need to do pdb inside of psycopg2 itself. Within SQLAlchemy
>>> you could do this at lib/sqlalchemy/dialects/postgresql/psycopg2.py however
>>> only if using raw psycopg2 above proves that it solves your connection
>>> issue.
>>>
>>>
>>> On Wed, Feb 26, 2020, at 11:19 AM, Karim Gillani wrote:
>>>
>>> Thanks. I don't believe this is a Red Hat issue as much as a
>>> configuration issue. This is because I can replicate the issue using PSQL
>>> and using the connect_timeout argument in PSQL, I can fix it. With the
>>> complexity of using flask-sqlalchemy which uses sqlalchemy to pass
>>> arguments to psycopg2 to libpq, it is difficult to see where the issue
>>> actually is. Based on the links you provided, I am passing the
>>> connect_timeout parameter correctly. I am not sure what to do now. I am
>>> guessing I will need to somehow figure out if the parameter is being
>>> passed. I think I will play with getting connection.get_dsn_parameters()
>>> function to display the parameters.
>>>
>>> Karim
>>>
>>> On Wed, 26 Feb 2020 at 07:30, Mike Bayer
>>> wrote:
>>>
>>>
>>> This would be a psycopg2 level setting which are documented at
>>> https://www.psycopg.org/docs/module.html#psycopg2.connect and the
>>> available values are ultimately at
>>> https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS
>>>
>>> .On the SQLAlchemy side you pass these to create_engine using
>>> connect_args:
>>> https://docs.sqlalchemy.org/en/13/core/engines.html?highlight=connect_args#custom-dbapi-args
>>>
>>>
>>>
>>> On Wed, Feb 26, 2020, at 12:11 AM, Karim Gillani wrote:
>>>
>>> We are using openshift which uses HAProxy. What I am looking for is why
>>> the connect_timeout may not being applied so that we can retry the call
>>> after re-connect to the other database pod.
>>>
>>> It is taking *up to two minutes* before I get
>>> the psycopg2.OperationalError. It's very strange. The code will
>>> automatically retry and work after thi
Re: [sqlalchemy] Closing all sqlalchemy sessions, connections, pools, metadata
That's an interesting point. I actually pull it in as an imported class
using
@as_declarative()
class MyClass
Forcing an import reload for each initialization made all the difference.
Thanks for your help.
On Monday, March 9, 2020 at 4:19:31 PM UTC-7, Mike Bayer wrote:
>
>
>
> On Mon, Mar 9, 2020, at 5:40 PM, Don Smiley wrote:
>
> In unit testing with multiple configurations, I am having trouble closing
> out everything to go on to the next iteration. For example, the test
> creates a class Address. I get the following kind of error:
>
> ```
> /venv/lib/python3.6/site-packages/SQLAlchemy-1.3.13-py3.6-linux-x86_64.egg/sqlalchemy/ext/declarative/clsregistry.py",
>
> line 97, in attempt_get\n% (".".join(path +
> [key]))\nsqlalchemy.exc.InvalidRequestError: Multiple classes found for
> path "Address" in the registry of this declarative base. Please use a fully
> module-qualified path.\n')
>
>
>
> that error refers to the declarative base class. If your tests set up new
> mappers against a Base each time,, you should use a new Base for each test
> run that sets up those mappers. otherwise you will get conflicting names
> like above.
>
>
>
--
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/b5bec3b4-952a-4a65-834a-b02f69b0fe1f%40googlegroups.com.
Re: [sqlalchemy] Closing all sqlalchemy sessions, connections, pools, metadata
On Mon, Mar 9, 2020, at 5:40 PM, Don Smiley wrote:
> In unit testing with multiple configurations, I am having trouble closing out
> everything to go on to the next iteration. For example, the test creates a
> class Address. I get the following kind of error:
>
> ```
> /venv/lib/python3.6/site-packages/SQLAlchemy-1.3.13-py3.6-linux-x86_64.egg/sqlalchemy/ext/declarative/clsregistry.py",
> line 97, in attempt_get\n % (".".join(path +
> [key]))\nsqlalchemy.exc.InvalidRequestError: Multiple classes found for path
> "Address" in the registry of this declarative base. Please use a fully
> module-qualified path.\n')
that error refers to the declarative base class. If your tests set up new
mappers against a Base each time,, you should use a new Base for each test run
that sets up those mappers. otherwise you will get conflicting names like above.
> ```
>
> My endcycle consists of among other things:
> * drop_all(echo=True)
> * drop database
>>
> ```
> engine = create_engine(config)
> conn = engine.connect()
> conn.execute("COMMIT")
> result = conn.execute(f"DROP DATABASE IF EXISTS {dbname}")
> result.close()
> conn.close()
> engine.dispose()
> ```
> * orm.session.close_all_sessions()
> * I have even tried del objects that have been created, but that seemed
> futile and desperate.
>
> Is there something else that I should be doing here, or in a different order?
> Or, should approach unit testing in a different way?
>
> I am thinking that I could probably unittest by shelling out each
> configuration to a separate process, but it seems to me that if I had a
> better understanding I would not need to do that.
>
> Any advice is welcome.
>
>
>
> --
> 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/83a39dbf-2062-416b-9f77-bfea51b90661%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/6bfbee5c-4526-494d-8230-19c941fea410%40www.fastmail.com.
[sqlalchemy] Closing all sqlalchemy sessions, connections, pools, metadata
In unit testing with multiple configurations, I am having trouble closing
out everything to go on to the next iteration. For example, the test
creates a class Address. I get the following kind of error:
```
/venv/lib/python3.6/site-packages/SQLAlchemy-1.3.13-py3.6-linux-x86_64.egg/sqlalchemy/ext/declarative/clsregistry.py",
line 97, in attempt_get\n% (".".join(path +
[key]))\nsqlalchemy.exc.InvalidRequestError: Multiple classes found for
path "Address" in the registry of this declarative base. Please use a fully
module-qualified path.\n')
```
My endcycle consists of among other things:
* drop_all(echo=True)
* drop database
>
> ```
engine = create_engine(config)
conn = engine.connect()
conn.execute("COMMIT")
result = conn.execute(f"DROP DATABASE IF EXISTS {dbname}")
result.close()
conn.close()
engine.dispose()
```
* orm.session.close_all_sessions()
* I have even tried del objects that have been created, but that seemed
futile and desperate.
Is there something else that I should be doing here, or in a different
order?
Or, should approach unit testing in a different way?
I am thinking that I could probably unittest by shelling out each
configuration to a separate process, but it seems to me that if I had a
better understanding I would not need to do that.
Any advice is welcome.
--
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/83a39dbf-2062-416b-9f77-bfea51b90661%40googlegroups.com.
Re: [sqlalchemy] session.add with insert-or-update
Thanks Jonathan. This is a very low traffic application, so not a problem but I appreciate you mentioning it. -- 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/a93a8e0c-5c5e-4d64-98d8-be3725c5e54f%40googlegroups.com.
Re: [sqlalchemy] session.add with insert-or-update
FWIW: If your application is high-traffic/high-concurrency, depending on how your transactions are scoped within the code you may want to do the getcreate or create step that calls `.flush` within an exception block or savepoint, to catch duplicate inserts. I've only had to do this on 2 (out of dozens) of projects, but it was a substantial improvement in performance. -- 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/96f92929-8ff2-45b1-b93b-c26dd6fc467a%40googlegroups.com.
