Issue
I am working on a multi tenant Spring Boot application using Postgresql and different schemas for tenants. Everything works fine until we need to use Postgresql's extensions, then we got errors about missing types.
ERROR: function text2ltree(character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 266
The extension is installed using Liquibase and I have updated the changesets to perform the following:
CREATE SCHEMA myschema;
CREATE EXTENSION IF NOT EXISTS ltree WITH SCHEMA myschema;
ALTER DATABASE mydb SET search_path TO "$user", myschema
Then I can control where the extension is installed and updating the search path should avoid to specify the schema all the time. I have run the Liquibase migration and check Postgresql configuration for search_path and it works. I can run queries (using Squirrel) without the need to include the schema prefix for the ltree extension types and functions.
It does not work as expected as I still got the same error message. If I explicitly prefix the ltree types and functions with myschema, I got an error indicating that the operator is not found.
ERROR: operator does not exist: myschema.ltree <@ myschema.ltree
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 263
For multi tenancy, we implement a custom org.hibernate.engine.jdbc.connections.spi.MultiTenantConnectionProvider that sets the schema in the MultitenantConnectionProvider.getAnyConnection() method.
I want to simplify the setup so I want to avoid to include always the extension's schema, besides the search_path configuration, I can not found anything related. Is there a way to implement the described setup to prevent prefixing with the extension schema in all the code?
Solution
If I install the extensions in the pg_catalog schema, then everything works as expected. See this answer.
I want to prevent to install the extensions in the pg_catalog schema so, a closer look to the PGConnection class reveals that the method to set the default schemas updates the search_path value to the schema given.
StringBuilder sb = new StringBuilder();
sb.append("SET SESSION search_path TO '");
Utils.escapeLiteral(sb, schema, getStandardConformingStrings());
sb.append("'");
stmt.executeUpdate(sb.toString());
It is escaping the schema argument, therefore if I try to use multiple schemas separated by commas, the whole expression is escaped.
The solution has been to create my own setSchema method in the MultiTenantConnectionProvider class. It does the same as the code above but adding my extensions' schema to the search_path value.
Answered By - jpuerto
Answer Checked By - David Goodson (JavaFixing Volunteer)
