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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

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

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      16028
Logged by:          cd a
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-4
Interesting,

Thanks for the report.

Dave Cramer


On Thu, 26 Sep 2019 at 18:16, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16028
Logged by:          cd a
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

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

On Monday, September 30, 2019, 3:45:48 AM GMT+3, Dave Cramer <[hidden email]> wrote:


Interesting,

Thanks for the report.

Dave Cramer


On Thu, 26 Sep 2019 at 18:16, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16028
Logged by:          cd a
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-4
Alex,



On Mon, 30 Sep 2019 at 06:13, Alex <[hidden email]> 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 <[hidden email]> wrote:


Interesting,

Thanks for the report.

Dave Cramer


On Thu, 26 Sep 2019 at 18:16, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16028
Logged by:          cd a
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

Alex-10
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)?






On Monday, September 30, 2019, 1:56:38 PM GMT+3, Dave Cramer <[hidden email]> wrote:


Alex,



On Mon, 30 Sep 2019 at 06:13, Alex <[hidden email]> 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://<a rel="nofollow" shape="rect" target="_blank" onclick="return window.theMainWindow.showLinkWarning(this)" href="http://127.0.0.1:5432/test?currentSchema=dev,public">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 <[hidden email]> wrote:


Interesting,

Thanks for the report.

Dave Cramer


On Thu, 26 Sep 2019 at 18:16, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16028
Logged by:          cd a
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-4


On Mon, 30 Sep 2019 at 07:37, Alex <[hidden email]> 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)?


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 <[hidden email]> wrote:


Alex,



On Mon, 30 Sep 2019 at 06:13, Alex <[hidden email]> 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 <[hidden email]> wrote:


Interesting,

Thanks for the report.

Dave Cramer


On Thu, 26 Sep 2019 at 18:16, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16028
Logged by:          cd a
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

Alex-10
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 <[hidden email]> wrote:




On Mon, 30 Sep 2019 at 07:37, Alex <[hidden email]> 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)?


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 <[hidden email]> wrote:


Alex,



On Mon, 30 Sep 2019 at 06:13, Alex <[hidden email]> 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://<a rel="nofollow" shape="rect" target="_blank" onclick="return window.theMainWindow.showLinkWarning(this)" href="http://127.0.0.1:5432/test?currentSchema=dev,public">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 <[hidden email]> wrote:


Interesting,

Thanks for the report.

Dave Cramer


On Thu, 26 Sep 2019 at 18:16, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16028
Logged by:          cd a
Email address:      [hidden email]
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.

Reply | Threaded
Open this post in threaded view
|

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

Dave Cramer-4

On Mon, 30 Sep 2019 at 08:26, Alex <[hidden email]> 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 <[hidden email]> wrote:




On Mon, 30 Sep 2019 at 07:37, Alex <[hidden email]> 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)?


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 <[hidden email]> wrote:


Alex,



On Mon, 30 Sep 2019 at 06:13, Alex <[hidden email]> 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 <[hidden email]> wrote:


Interesting,

Thanks for the report.

Dave Cramer


On Thu, 26 Sep 2019 at 18:16, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      16028
Logged by:          cd a
Email address:      [hidden email]
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.