Support non-'public' schema in PostgreSQL

[joshmoore]

see: https://forum.image.sc/t/setting-postgresql-schema-for-docker-version-of-omero-server/54043/7?u=joshmoore

Using a schema other than public fails on the call to prepareSession:

    public void prepareSession(final long eventId, final long userId, final long groupId) {
        SimpleJdbcCall call = new SimpleJdbcCall(_jdbc()).withFunctionName("_prepare_session"); // FIXME
        MapSqlParameterSource in = new MapSqlParameterSource();
        in.addValue("_event_id", eventId);
        in.addValue("_user_id", userId);
        in.addValue("_group_id", groupId);
        call.executeFunction(void.class, in);
    }

since the lookup for that function happens in the public schema (ignoring the search_path:

database_1 | 2021-06-18 07:09:00.776 UTC [86] LOG: execute : SELECT NULL AS 
PROCEDURE_CAT, n.nspname AS PROCEDURE_SCHEM, p.proname AS 
PROCEDURE_NAME, NULL, NULL, NULL, d.description AS REMARKS, 2 AS 
PROCEDURE_TYPE, p.proname || ‘_’ || p.oid AS SPECIFIC_NAME FROM 
pg_catalog.pg_namespace n, pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_description 
d ON (p.oid=d.objoid) LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND 
c.relname=‘pg_proc’) LEFT JOIN pg_catalog.pg_namespace pn ON (c.relnamespace=pn.oid 
AND pn.nspname=‘pg_catalog’) WHERE p.pronamespace=n.oid AND n.nspname LIKE 
‘public’ AND p.proname LIKE ‘_prepare_session’ ORDER BY PROCEDURE_SCHEM, 
PROCEDURE_NAME, p.oid::text

[imagesc-bot]

This issue has been mentioned on Image.sc Forum. There might be relevant details there:

https://forum.image.sc/t/setting-postgresql-schema-for-docker-version-of-omero-server/54043/9