通过JDBC进行Postgres连接：org.postgresql.util.PSQLException：错误：关系“处方”不存在

I'm having a ton of trouble connecting to a dockerized postgres instance. The error I get looks like this:

我在连接到一个dockerized postgres实例时遇到了很多麻烦。我得到的错误看起来像这样:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: relation "prescriptions" does not exist
  Position: 29
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:321)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:313)

Here are the steps to recreate the issue. First, I set up the database like so:

以下是重新创建问题的步骤。首先,我像这样设置数据库:

docker run -p 7654:5432 --name zoo_postgres -e POSTGRES_PASSWORD="stranger" -e POSTGRES_USER=stranger -d postgres

And run a setup script, that looks like this:

并运行一个安装脚本,如下所示:

create DATABASE stranger;
create SCHEMA   stranger;

CREATE TABLE prescriptions (
  prescription_id BIGINT NOT NULL,
  PRIMARY KEY (prescription_id)
);

CREATE USER stranger_user WITH ENCRYPTED password 'stranger_user';

grant CONNECT on DATABASE stranger to stranger_user;
grant USAGE on SCHEMA stranger to stranger_user;

GRANT SELECT ON ALL TABLES IN SCHEMA stranger TO stranger_user;
GRANT INSERT ON ALL TABLES IN SCHEMA stranger TO stranger_user;
GRANT DELETE ON ALL TABLES IN SCHEMA stranger TO stranger_user;
GRANT UPDATE ON ALL TABLES IN SCHEMA stranger TO stranger_user;

-- I've tried several variations of the line below, without any effect
ALTER USER stranger_user SET SEARCH_PATH to stranger,stranger_user,public;

On the Java side, the code is extremely simple and looks like the following:

在Java方面,代码非常简单,如下所示:

public static void main(String[] args) throws Exception {
    Class.forName("org.postgresql.Driver");
    Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:7654/stranger","stranger_user", "stranger_user"); // this succeeds (suggesting the connection string is correct)
    connection.setCatalog("stranger");
    connection.setSchema("stranger");
    Statement stmt = connection.createStatement();
    stmt.execute("select prescription_id from prescriptions");
    // code dies before getting to the close line.
    connection.close();
    System.out.println("Successfully connected using JDBC directly");
}

Setting the catalog and schema has no effect and neither does changing the search path. Everything is lower case, so I don't think it has anything to do with escaping the table names. I've tried using the real user (and skipping the user account), omitting the database name and several other tricks. I suspect that there's something wrong with the way that I'm connecting to the database, but I can connect to is using psql, like so:

设置目录和架构无效,也不会更改搜索路径。一切都是小写的,所以我认为它与逃避表名没有任何关系。我尝试使用真实用户(并跳过用户帐户),省略数据库名称和其他几个技巧。我怀疑我连接数据库的方式有问题,但我可以连接到使用psql,如下所示:

% psql -h localhost -p 7654 -d postgres -U stranger_user

From this point, I can access the tables with normal sql:

从这一点来说,我可以使用普通的sql访问表:

% psql -h localhost -p 7654 -d postgres -U stranger_user     
Password for user stranger_user: 
psql (9.4.6, server 9.5.2)
WARNING: psql major version 9.4, server major version 9.5.
         Some psql features might not work.
Type "help" for help.

postgres=> \d
              List of relations
  Schema  |     Name      | Type  |  Owner   
----------+---------------+-------+----------
 stranger | prescriptions | table | stranger
(1 row)

postgres=> select * from prescriptions;
 prescription_id 
-----------------
(0 rows)

postgres=> select * from stranger.prescriptions;
 prescription_id 
-----------------
(0 rows)

I'm using the latest version of the postgres driver: "org.postgresql:postgresql:9.4+", and can't see anything else that would cause this issue. At this point I'm out of ideas and can't figure out what's wrong.

我正在使用最新版本的postgres驱动程序:“org.postgresql:postgresql:9.4+”,并且看不到任何会导致此问题的内容。在这一点上,我没有想法,也无法弄清楚出了什么问题。

2 个解决方案

#1

If you use pgAdmin or psql, and log in to server localhost:6543, database stranger with user stranger_user and password stranger_user, what do you see?

如果您使用pgAdmin或psql,并使用用户stranger_user和密码stranger_user登录到服务器localhost:6543,数据库陌生人,您看到了什么?

I expect that you see an empty public schema.

我希望你看到一个空的公共架构。

Running create DATABASE stranger does not make it the active database.

运行create DATABASE stranger并不会使它成为活动数据库。

So, running create SCHEMA stranger will create that schema in the postgres database. It also won't make the new schema active.

因此,运行create SCHEMA stranger将在postgres数据库中创建该模式。它也不会使新架构处于活动状态。

So, running CREATE TABLE prescriptions will create that table in the public schema of the postgres database.

因此,运行CREATE TABLE处方将在postgres数据库的公共模式中创建该表。

Of course, all that is not even in the PostgreSQL database instance/cluster served on port 6543, because you did all that on a different instance/cluster on port 7654.

当然,甚至在端口6543上提供的PostgreSQL数据库实例/集群中都没有,因为您在端口7654上的不同实例/集群上完成了所有操作。

#2

You're connecting with psql to port 7654, and then in your JDBC URL to port 6543. Also, you're connecting to the postgres database with psql, and the stranger database in your JDBC URL.

您将psql连接到端口7654,然后在JDBC URL中连接到端口6543.此外,您将使用psql连接到postgres数据库,并使用JDBC URL中的陌生人数据库连接。

Could these differences be the source of the exception you're seeing?

这些差异可能是你所看到的例外的根源吗?

https://jdbc.postgresql.org/documentation/94/connect.html

#1