Hi Everyone.
The thing is that I’ve been trying to run openmrs reference application with a postgres db hosted in the cloud, but this cloud service doesn’t allow to create extensions in the public schema, but in another one, so these two changesets in liquibase-update-to-latest-2.4.x.xml (openmrs-core) fail.
<changeSet id="20200604-soundex_extension" author="aman" dbms="postgresql">
<comment> Soundex extension for PostgreSQL</comment>
<sql> CREATE EXTENSION IF NOT EXISTS fuzzystrmatch SCHEMA public;</sql>
</changeSet>
<changeSet id="20200715-uuid_ossp_extension" author="aman" dbms="postgresql">
<comment> Extension to use UUID functions with PostgreSQL and creating an alias similar to MySQL</comment>
<sql>
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA public;
CREATE FUNCTION UUID() RETURNS UUID LANGUAGE SQL AS $ SELECT uuid_generate_v1() $;
</sql>
</changeSet>
By removing the SCHEMA public bit doesn’t seem to help, because even though then the functions are created in the proper schema, then the CREATE FUNCTION UUID() is not executed, I have no idea why. The search path for the database admin user already contains both public and the extensions schemas, but I don’t know why, I can’t make this work only with the liquibase scripts.
The only way I found to make this work is by manually adding the function in the database prior to starting an empty reference application, and even then, the UUID() alias is not working because some module wasn’t able to use the underlying uuid_generate_v1() function, which is also in the extensions schema.
This is a mistery to me, because as I said, the user has both schemas in the search path, but I can see in the openmrs’ logs that the search path is being set to only public somewhere, I don’t know where. My clumsy solution was executing this manually before starting the reference application:
CREATE FUNCTION public.uuid_generate_v1() RETURNS UUID LANGUAGE SQL AS $ SELECT extension_schema.uuid_generate_v1() $;
CREATE FUNCTION public.UUID() RETURNS UUID LANGUAGE SQL AS $ SELECT public.uuid_generate_v1() $;
In conclusion, I think openmrs is not prepared to use separate schemas in postgres, or I can’t make it work properly. Does anyone see a better solution to this?
Thanks a lot!