xtro xtro - 6 months ago 127
Java Question

Postgres connectivity via JDBC: org.postgresql.util.PSQLException: ERROR: relation "prescriptions" does not exist

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

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:

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 -h localhost -p 7654 -d postgres -U stranger_user


From this point, I can access the tables with normal 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.

Answer

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?

I expect that you see an empty public schema.

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

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

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

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.