jOOQ中的动态运行时PostgreSQL模式选择 [英] Dynamic runtime PostgreSQL schema selection in jOOQ

问题描述

Alice和Bob希望Dave开发一个应用程序。他们俩都希望使用相同的数据类型，但是他们俩都希望保持数据的独立性和安全性。因此，Dave开始构建DEV模式。

Alice and Bob want Dave to develop an application. They both want to work with identical data types, but they both want to keep their data separate and secure. So Dave goes off to build a DEV schema.

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表和bar函数。 Dave使用jOOQ生成Java api，并在运行时将DEV模式映射到用户模式。戴夫（Dave）以前与任何一位客户都没有关系，突然发现自己是鲍勃（Bob）的侄子。

Dave uses Flyway to initialize schemas for Alice and Bob, so they both have the foo table and bar function. Dave uses jOOQ to generate a java api and map the DEV schema at runtime to the user schemas. Dave, previously having no relation to either of his clients, suddenly finds himself the nephew of Bob.

但是爱丽丝（Alice）和鲍勃（Bob）后来都回到戴夫（Dave），请他写信。他们的一些自动化。因此Dave决定创建一个可以访问Alice和Bob的架构的机器用户Rob。他可以重用所有相同的jOOQ生成的代码，并且使用foo的所有内容都可以完美无缺地工作，直到自动化程序尝试执行bar函数-错误：关系 foo不存在。运行时模式映射对功能栏内对foo的无模式引用没有影响。

But Alice and Bob both come back to Dave later and ask him to write some automation for them. So Dave decides to create a machine user Rob who has access to Alice and Bob's schemas. He can reuse all the same jOOQ generated code and everything that uses foo directly works flawlessly, until the automation tries to execute the bar function - Error: relation "foo" does not exist. The runtime schema mapping has no effect on the schema-less reference to foo inside the function bar.

最简单的答案是将search_path设置为用户Rob假装为。可以在每组jOOQ调用之前手动设置此参数，但感觉像是易于出错，繁琐的代码jOOQ通常使我们无法编写代码。 Dave可以创建两个用户Rolice和Robob，每个用户分别配置为访问各自的客户端。但这显然扩展性很差，尤其是在使用连接池时。 Dave选择了一个设置search_path的自定义ConnectionProvider，但这可能会产生大量开销，因为search_path查询必须在返回连接之前返回结果。

The trivial answer is to set the search_path to whichever user Rob should masquerade as. This could be set manually before every set of jOOQ calls, but that feels like the sort of error-prone, tedious code jOOQ generally insulates us from writing. Dave could create two users Rolice and Robob, each individually configured to access their respective client. But this obviously scales poorly, especially when using connection pooling. Dave opts for a custom ConnectionProvider that sets the search_path, but that's potentially an awful lot of overhead, as the search_path query has to return a result before returning the connection.

因此Dave到处乱逛，在StackOverflow上问了一个有关设置search_path的明显问题，并发现出于极好的原因，该功能已从jOOQ的3.0版本中删除。但是戴夫（Dave）是那种真的很喜欢jOOQ的优雅并且觉得必须有比他的黑客更好的解决方案的人。

So Dave hunts around, asks the obvious question on StackOverflow about setting the search_path, and finds out that the feature was removed, for excellent reasons, from jOOQ during the 3.0 release. But Dave's the kind of guy who really likes the elegance of jOOQ and feels like there must be a better solution than his hack.

我不是戴夫，但我当然可以看看他来自哪里。在jOOQ中，让一个自动化用户与几个相同的用户模式进行交互的最佳方法是什么，特别是与包含无模式引用的函数进行交互的最佳方法是什么？

I'm not Dave, but I certainly see where he's coming from. What is the best way in jOOQ to have one automated user interact with several identical user schemas, and in particular, with functions containing schema-less references?

，我使用的是PostgreSQL 9.3和jOOQ 3.5。自从半小时前我弥补了Dave的烦恼之后，Dave便开始采取行动。

In case it matters, I'm on PostgreSQL 9.3 and jOOQ 3.5. Dave is on whatever you want him to be, since I made him up a half hour ago.

ConnectionProvider解决方案的相关内容：

The relevant bit of the ConnectionProvider solution:

@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;
}

推荐答案

目前看来至少，设置search_path超出了jOOQ的范围。从理论上讲，我们可以在Flyway中使用Java迁移，并强制函数中的所有引用具有明确的模式，但这听起来很痛苦。

It appears that for the time being at least, setting search_path is something out of scope for jOOQ. We could, in theory, use Java migrations in Flyway and force all references within a function to have explicit schemas, but that sounds pretty painful. Which leaves us with either setting the search path manually, potentially adding it to our transaction management, or writing a smarter ConnectionProvider.

我们有一个非常严格的每次查询功能，可以让我们手动设置搜索路径，有可能将其添加到事务管理中，或者编写更智能的ConnectionProvider。 -transaction模型在我们的应用程序上，因此实际上没有任何事务管理可向其中添加 set search_path = ...查询。

We have a pretty strict one-query-per-transaction model on our application, so there isn't really any transaction management in place to add the "set search_path = ..." query to. The ConnectionProvider solution appears to be our best option.

我们已经有一个用于连接池的自定义ConnectionProvider实现，因此添加它并不是一件容易的事。逻辑（在问题末尾已在上面给出）来设置search_path。我们可以通过使用一些可以记住其当前search_path设置的内容来装饰Connection，并在其前面加上 set search_path = ...;前缀，从而提高其性能。如有需要，请先将其放在任何声明的开头。我们已经看到了对每个请求设置search_path的性能影响，因此这成为必需仅是时间问题。好吧，那还是回到了旧的专有数据库访问层的痛苦。

We already have a custom ConnectionProvider implementation for connection pooling, so it wasn't too big of a hassle to add in the logic (given above at the end of the question) to set the search_path. We could make it more performant by decorating the Connection with something that remembered what its current search_path is set to, and prefixing a "set search_path = ...;" to the front of any statement before it goes out the door if need be. We are already seeing a performance impact to setting the search_path every request, so it's just a matter of time before this becomes a necessity. Well, that or going back to the pain of our old proprietary database access layer.

至少，在我撰写并撰写完将连接池/ search_path设置为ConnectionProvider开源，希望其他人能在我解决之前找到更好的解决方案。

At the very least, I won't accept this as the answer until I write and open source the connection pooling/search_path setting ConnectionProvider, in the hope that someone else will come up with a better solution before I get around to it.

这篇关于jOOQ中的动态运行时PostgreSQL模式选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！