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

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

> 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.
>

Thanks for the clarification

Dave

>
> 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.
>
>