在psycopg2中为连接的所有查询设置模式：设置search_path时获取竞争条件 [英] Setting schema for all queries of a connection in psycopg2: Getting race condition when setting search_path

问题描述

我们的系统在Ubuntu，python 3.4，postgres 9.4.x和psycopg2上运行。

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

我们（将来会）在之间拆分dev ， test 和 prod 环境使用模式。我创建了一种方便的方法来创建与数据库的连接。它使用json连接配置文件来顺序创建连接字符串。我想将连接配置为对使用返回的连接的所有后续查询使用特定的架构。我不希望我的查询具有硬编码的架构，因为我们应该能够轻松地在它们之间进行切换，这取决于我们处于开发，测试还是生产阶段/环境。

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

只要您有时间执行它就可以正常工作设置的 search_path 查询。不幸的是，如果我执行以下查询的速度过快，则会出现未设置 search_path 的竞争情况。我试图通过在 return conn 之前执行 conn.commit（）来强制执行，但是，这将 search_path 重置为默认架构 postgres ，以使其不使用，例如 prod 。在数据库或应用程序层的建议是可取的，但是，我知道我们也可以在操作系统级别解决此问题，也欢迎朝这个方向提出任何建议。

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.

示例json配置文件如下所示：

An example json configuration file looks like the following:

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

任何建议都很感激。

推荐答案

我认为一个更优雅的解决方案是设置<$ c在 connect（）的选项参数中的$ c> search_path ，如下所示：

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.

我在 psycopg2功能请求。至于选项参数本身，在此处。

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

这篇关于在psycopg2中为连接的所有查询设置模式：设置search_path时获取竞争条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！