When installing Semarchy on a PostgreSQL database, customers sometimes run into errors like this:

+- ERROR: function uuid_generate_v1() does not exist
 

 Hint: No function matches the given name and argument types. 
 You might need to add explicit type casts.

This error can occur when the search path in the database is not correctly configured.

Do you have a search_path problem?

The above error will be thrown if:

  • The function does not exist.
  • The function exists, but you do not have access to it.
  • The function exists, and you have access, but it is not in the search_path.
/* First confirm that the function exists and you have access to it.
  * Semarchy recommends creating a schema called extensions.
  * This is documented in the installation guide.
  */
 select extensions.uuid_generate_v1() ;
 

 -- The preceding select should return something in this form:
 cb61fd16-197c-11eb-9a95-024bff2da95a
 

 select uuid_generate_v1() ;
 

 /* If the fully qualified function works well, 
  * but the preceding select fails, then you have 
  * a search_path problem. You must fix it 
  * in order for xDM to work properly.
  */

What is the Postgres search path?

In Postgres, you can create multiple schemas within the database. Each of those schemas have objects like tables, functions, etc. The search path allows the ability for an application to search through multiple schemas to find the object it is looking for. The path is set by the database administrator when creating the database.

How Do You Configure the search path?

In the Semarchy application we have multiple schemas. semarchy_repository, data location schemas, public and extensions. The search path should always include the current schema, the public and extensions schemas. The current schema can be either the semarchy_repository or any data location schema.

You can run this SQL command to set your search path

ALTER DATABASE <postgresql_database_name> 
 SET SEARCH_PATH TO "$user",public,extensions;

You can also find the command in the Installation Guide in the section Configuring the Repository Storage

Here is a quick example of how the search path works.

In our sample, we created a repository schema.

CREATE USER  semarchy_repository WITH PASSWORD 'semarchy_repository';
 CREATE SCHEMA semarchy_repository AUTHORIZATION semarchy_repository;

That user is semarchy_repository and the schema is also named semarchy_repository.

In this use case, the search path starts with the current schema, which is using the variable $user. It will then check if the current schema has the desired object. If it cannot find it there, it will then search the public schema, if it cannot find it there, it will then check the extensions schema. This is essentially the search path you set in your database.

How to resolve issues with the search_path?

Users who incorrectly set their search_path or did not set it at all will receive errors like this:

+- ERROR: function uuid_generate_v1() does not exist

Postgres will try to find that function in the schemas listed in the search_path. The most common mistake is that the customer will forget the extensions schema when setting the search path. this schema contains the functions needed in the application. Postgres will first search the current schema, then public. If extensions is missing from the search path, you will get this error message because Postgres will not know to check the extensions schema for function.

So now that we understand search_paths, what happens if you did not set it correctly?

Check your current search_path.

show search_path ;

Your output should look like this, in this order:

"$user", public, extensions

If your output does not indicate both $user and extensions in your search_path, xDM will not work correctly.

You will need to run the alter database command to set the search path

ALTER DATABASE <postgresql_database_name> 
 SET SEARCH_PATH TO "$user",public,extensions ;

You should run this command using the Postgres admin database account.

  • Once you have run the command, disconnect your session in your database management tool.
  • Reconnect and run the show search_path command again.
  • Verify that your search_path is now correct. Your results should look like the sample output provided above (in the same order).
  • You will then want to restart your Tomcat, so that the changes to the database are reflected in the application.

Now the application should be able to find all of the objects required to run Semarchy xDM.