Setting schema for all queries of a connection in psycopg2: Getting race condition when setting search_path

Question

Our system is running on Ubuntu, python 3.4, postgres 9.4.x and psycopg2.

We (will in the furture) split between dev, test and prod environments using schemas. I've create a convenience method for creating connections to our database. It uses json connection configuration files in order create the connection string. I want to configure the connection to use a particular schema for all following queries using the returned connection. I don't want my queries to have hardcoded schemas, because we should be able to easily switch between them depending on if we are in development, testing or production phase/environment.

Currently the convenience method looks like the following:

def connect(conn_config_file = 'Commons/config/conn_commons.json'):     with open(conn_config_file) as config_file:             conn_config = json.load(config_file)      conn = psycopg2.connect(         "dbname='" + conn_config['dbname'] + "' " +         "user='" + conn_config['user'] + "' " +         "host='" + conn_config['host'] + "' " +         "password='" + conn_config['password'] + "' " +         "port=" + conn_config['port'] + " "     )     cur = conn.cursor()     cur.execute("SET search_path TO " + conn_config['schema'])      return conn 

It works fine as long as you give it time to execute the set search_path query. Unfortunately, if I'm too fast with executing a following query a race condition happens where the search_path isn't set. I've tried to force the execution with doing a conn.commit() before the return conn, however, this resets the search_path to the default schema postgres so that it doesn't use, say, prod. Suggestions at the database or application layer is preferable, however, I know we probably could solve this at the OS level too, any suggestions in that direction are also welcomed.

An example json configuration file looks like the following:

{     "dbname": "thedatabase",     "user": "theuser",     "host": "localhost",     "password": "theusers_secret_password",     "port": "6432",     "schema": "prod" } 

Any suggestion is very appreciated.

Answer 1

I think a more elegant solution would be to set the search_path in options parameter of connect(), like so:

def connect(conn_config_file = 'Commons/config/conn_commons.json'):     with open(conn_config_file) as config_file:             conn_config = json.load(config_file)      schema = conn_config['schema']     conn = psycopg2.connect(         dbname=conn_config['dbname'],         user=conn_config['user'],         host=conn_config['host'],         password=conn_config['password'],         port=conn_config['port'],         options=f'-c search_path={schema}',     )     return conn 

Of course, you can use "options" as part of the connection string. But using keyword arguments prevents all the hassle with string concatenations.

I found this solution in this psycopg2 feature request. As for the "options" parameter itself, it's mentioned here.