How to set `lock_timeout` on a PostgreSQL connection with SQLAlchemy and psycopg2?

Question

With PostgreSQL, you can run this query to set a specific lock_timeout for a session:

SET lock_timeout TO '3s'

I'm wondering if there is a nice way to set this option when setting up a connection with SQLAlchemy. The way I'm instantiating SQLAlchemy sessions is the following:

engine = create_engine('postgresql+psycopg2://{user}:{pswd}@{host}:{port}/{name}')
session = scoped_session(sessionmaker(bind=engine))

I've tried passing it in connect_args but that is not supported:

engine = create_engine(
    'postgresql+psycopg2://{user}:{pswd}@{host}:{port}/{name}',
    connect_args={'lock_timeout': 3}
)

Is there a way to set this option per-session/connection with SQLAlchemy and psycopg2?

Artem Gordinsky · Accepted Answer · 2020-05-01 13:29:57Z

5

As it turned out, this is the right way to set lock_timeout for a session (note that the value is in milliseconds):

engine = create_engine(
    'postgresql+psycopg2://{user}:{pswd}@{host}:{port}/{name}',
    connect_args={'options': '-c lock_timeout=3000'}
)

edited May 1, 2020 at 13:29

answered Apr 30, 2020 at 20:16

Artem Gordinsky

6246 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kshitij Mittal Over a year ago

Does the connect_args={'lock_timeout': 3} method not work or it is not the right way to do it? Also, can you share the link to some documentation, I am not about this find this way: {'options': '-c lock_timeout=3000'} of passing arguments anywhere in the documentation.

aflukasz Over a year ago

@KshitijMittal it's somewhat tricky. No, "bare" lock_timeout: 3 is not the right way. lock_timeout is one of options as described at postgresql.org/docs/current/… (look for "options" param). So "options" is a "connection argument" that wraps another format for defining "options". "options" in general are described at postgresql.org/docs/current/runtime-config.html and lock_timeout in particular at postgresql.org/docs/current/….

aflukasz Over a year ago

... and the whole "-c" form is mentioned at postgresql.org/docs/current/config-setting.html.

jbarlow Over a year ago

Out of date. TypeError: Engine.connect() got an unexpected keyword argument 'connect_args'

Collectives™ on Stack Overflow

How to set `lock_timeout` on a PostgreSQL connection with SQLAlchemy and psycopg2?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related