jOOQ中动态运行时PostgreSQL模式选择

Question

CREATE TABLE foo (id INT PRIMARY KEY);
CREATE FUNCTION bar(_id INT) RETURNS INT AS $$
    INSERT INTO foo (id) VALUES (_id) RETURNING id;
$$ LANGUAGE SQL;

Dave使用Flyway为Alice和Bob初始化模式，因此他们都有foo table和bar函数。Dave使用jOOQ生成java api，并在运行时将开发模式映射到用户模式。戴夫以前和他的任何一个客户都没有关系，突然发现自己是鲍勃的侄子。

但是爱丽丝和鲍勃后来都回来找戴夫，让他为他们写一些自动化。因此，Dave决定创建一个机器用户Rob，他可以访问Alice和Bob的模式。他可以重用所有相同的jOOQ生成的代码，并且所有使用foo的代码都可以完美无缺地直接工作，直到自动化试图执行bar函数-error：关系“foo”不存在。运行时模式映射对函数栏中对foo的无模式引用没有影响。

简单的答案是将search_path设置为Rob应该伪装成的用户。这可以在每组jOOQ调用之前手动设置，但这感觉就像是jOOQ通常使我们与编写隔离的那种容易出错、乏味的代码。Dave可以创建两个用户Rolice和Robob，每个用户都被单独配置为访问各自的客户端。但这显然伸缩性很差，尤其是在使用连接池时。Dave选择了自定义的ConnectionProvider来设置search_path，但是这可能会带来很大的开销，因为search_path查询必须在返回连接之前返回一个结果。

如果有关系的话，我使用的是PostgreSQL 9.3和jOOQ 3.5。戴夫是你想让他成为什么样的人，因为半小时前我编造了他。

ConnectionProvider解决方案的相关部分：

@Override
public Connection acquire() throws DataAccessException {
    Connection c = dataSource.getConnection();
    try(Statement s = c.createStatement()){
        s.execute("SET search_path = '"+schema+"'");
    }catch(SQLException e){
        throw new DataAccessException("Could not initialize connection", e);
    }
    return c;
}

Answer 1

至少就目前而言，设置search_path似乎超出了Jooq的范围。理论上，我们可以在Flyway中使用Java迁移，并强制函数中的所有引用都具有显式模式，但这听起来相当痛苦。这使得我们要么手动设置搜索路径，潜在地将其添加到事务管理中，要么编写一个更智能的ConnectionProvider。

我们的应用程序中有一个非常严格的每个事务一个查询的模型，因此实际上没有任何事务管理可以添加“set search_path=...”查询。ConnectionProvider解决方案似乎是我们的最佳选择。

我们已经有了一个用于连接池的自定义ConnectionProvider实现，因此添加设置search_path的逻辑（在问题的末尾给出）并不太麻烦。我们可以通过使用记住当前search_path被设置为什么的东西来装饰连接，并在前缀“set search_path=...；”来使其性能更好如果需要的话，在任何声明出门前。我们已经看到在每个请求中设置search_path会对性能产生影响，所以这只是时间问题。好吧，这还是回到我们旧的专有数据库访问层的痛苦。