Repository navigation
PostgreSQL schema not working #2705
Description
Expected behavior
Setting schema schould make effect on database connection:
databaseConfig.setDbSchema("test1");https://ebean.io/docs/database/postgres/#schema
Actual behavior
Setting databaseConfig.setDbSchema("test1") does nothing, schema is public.
Comment
I have checked source code and is seems, that Ebean is setting property schema but Postgresql uses currentSchema.
Setting ?currentSchema=test in URL solves this problem.
Source code from PostgreSQL JDBC driver ConnectionFactoryImpl.java:
private List<String[]> getParametersForStartup(String user, String database, Properties info) {
....
String currentSchema = PGProperty.CURRENT_SCHEMA.get(info);
if (currentSchema != null) {
paramList.add(new String[]{"search_path", currentSchema});
}
....
}And PGProperty.java:
CURRENT_SCHEMA(
"currentSchema",
null,
"Specify the schema (or several schema separated by commas) to be set in the search-path"),I think, that ConnectionPool.java should be changed from:
final String schema = params.getSchema();
if (schema != null) {
this.connectionProps.setProperty("schema", schema);
}to:
final String schema = params.getSchema();
if (schema != null) {
this.connectionProps.setProperty("currentSchema", schema);
}
Activity
Fix schema in Postgres
jnehlmeier commentedon May 24, 2022
Shouldn't
ConnectionPoolbe JDBC driver independent? Seems like configuring the schema is different for different JDBC drivers / databases, so it should be configured elsewhere. For example in Oracle it seems you need to execute analter session set current_schema=myschemaquery to switch schema.Ryszard-Trojnacki commentedon May 24, 2022
Not sure.
According to Ebean documentation it is only for Postgres https://ebean.io/docs/database/postgres/#schema and I was using this function base on code example. It didn't work. Therefore is this issue.
rbygrave commentedon May 25, 2022
Ah, no that is not the intention here.
Sorry, this is confusing and dealing with a very Postgres specific "convention" / "best practice" ... which is TLDR
publicschema [Edit: added in the NOT here]That is, the motivation is more specifically with Postgres having a default
publicschema and that "best practice" suggests that we should actually prefer to use another schema to put our objects in AND ... that schema name should match the database username because then ...@Table, we don't modify the currentSchema or modify the search path> ... there is no need to use currentSchema or modify the search path
Said another way:
publicschemaUnder these conditions, we do NOT specify currentSchema, we do NOT modify the search path and we do NOT explicitly specify the schema in
@Table.... but the objects (tables, views etc) are put under that schema and NOT the
publicschema. [edit: Hence all things that run DDL are interested in this flag/value]Let's say our Postgres database username is
my_app, we want a schema calledmy_appand our tables and objects go in that schema AND we do not need to use currentSchema OR modify the search path OR specify the schema in@Table.Said another way, this is a Postgres specific convention, that if followed means that the objects do not go into the
publicschema.If we want to use a schema explicitly ... like
@Table(name="myschema.customer")... that's different. It's explicitly use of schema.Dang, I've probably not explained this well. Did any of the above help clarify?
rbygrave commentedon May 25, 2022
It could almost be a boolean flag like
boolean useDatabaseUsernameAsDefaultSchema;that only applies to Postgres ... so could even beboolean postgresUseUsernameAsDefaultSchema;.When this is set to
truethen (for Postgres only) make sure that the objects (tables, views, functions) go into a database schema that is the same name as the database username / role [and not thepublicschema].In order to make it a boolean, ebean would need to extract the database username when 1. running db migrations and 2. when the DdlRunner runs during testing. With this "kind of magic schema" / postgres specific convention the schema name must always match the database username / role.
Implementation wise, instead of using a boolean and extracting the username, we've got a really poorly named
databaseConfig.setDbSchema("...");...rbygrave commentedon Jun 26, 2022
Ok, I'm going to move this into Discussions - Backlog.
The task that should be done at some point is:
dbSchema->defaultSchemadefaultSchemaassigned to it. This would be done when generating DDL for migrations and generating DDL forcreate-all/drop-allI'll move this to Discussions - Backlog as a kind of test to see how that workflow goes.
@Ryszard-Trojnacki