Re: BUG #16028: JDBC currentSchema connection parameter not working for text search configuration

After more debugging, it seems that the "search_path" is set to the old schema causing the error ( entityManager.createNativeQuery( "show search_path" ).getResultList() returns different schema).Initially I've thought that the connection param "currentSchema" will be used as fallback even if "search_path" is invalid.

I've set the necessary configuration params to the new schema, but somehow it remains set to an old value. Need to do more debugging.
This issue can be closed as this is not a postgres issue.

On Monday, September 30, 2019, 2:47:13 PM GMT+3, Dave Cramer <davecramer(at)gmail(dot)com> wrote:

On Mon, 30 Sep 2019 at 07:37, Alex <cdalxndr(at)yahoo(dot)com> wrote:

What's the behavior when both "currentSchema" is specified in the connection uri and "search_path" is overwritten to a different value by the app (inside the same transaction as the query)?Neither https://jdbc.postgresql.org/documentation/head/connect.html, nor https://www.postgresql.org/docs/9.1/runtime-config-client.html mention the behavior.

Initially the driver will set the search_path to whatever is in the "currentSchema"The driver doesn't do anything special if you change the search_path in a transaction, however:
The search_path setting will be set to whatever it was set to in the transaction if you commit. If you rollback it will rollback the setting.
Dave 

On Monday, September 30, 2019, 1:56:38 PM GMT+3, Dave Cramer <davecramer(at)gmail(dot)com> wrote:

Alex,

On Mon, 30 Sep 2019 at 06:13, Alex <cdalxndr(at)yahoo(dot)com> wrote:

Additional info:The original schema was exported using pg_dump (whole db) and imported using "psql -f".
Then I've renamed the schema in PgAdmin(4.13) to "dev" (right click -> Properties -> change name -> Save).
If I rename back the schema to original name, and use that name in "currentSchema" param, then everything works correctly.
In addition, I don't know if any configuration may override the connection param "currentSchema" (such as hibernate overwriting "search_path"), but in my case it should be used as fallback to resolve missing text search configuration.

Seems something else is going on then as this code:
@Test
public void testSchemaInProperties() throws Exception {
Connection con = java.sql.DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/test?currentSchema=dev,public","test","");

Statement stmt = con.createStatement();
stmt.execute("SHOW search_path");

ResultSet rs = stmt.getResultSet();
if (!rs.next()) {
Assert.fail("'options' connection initialization parameter should be passed to the database.");
}
Assert.assertEquals("'options' connection initialization parameter should be passed to the database.", "dev,public", rs.getString(1));

stmt.close();
TestUtil.closeDB(con);
}
works fine.
Dave.  

On Monday, September 30, 2019, 3:45:48 AM GMT+3, Dave Cramer <davecramer(at)gmail(dot)com> wrote:

Interesting,
Thanks for the report.
Dave Cramer

On Thu, 26 Sep 2019 at 18:16, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:

The following bug has been logged on the website:

Bug reference:      16028
Logged by:          cd a
Email address:      cdalxndr(at)yahoo(dot)com
PostgreSQL version: 10.3
Operating system:   Windows 10
Description:       

Using a java spring app with connection:
jdbc:postgresql://localhost:5433/postgres?currentSchema=dev,public&ApplicationName=myapp
Schema 'dev' has fts configuration 'en'.
Query (debugged PgPreparedStatement):
  select * from dev.category where category.language='en'
  the param 'en' is registered with oid (paramType) 3734 corresponding to
'regconfig' type

Query results in: ERROR: text search configuration "en" does not exist

The same error is shown when running the query in PgAdmin if search_path
doesn't contain 'dev' schema. With 'dev' in search_path, the query is ok.
I was expecting that 'currentSchema' connection parameter will be used to
resolve fts configuration to 'dev' schema.