Solving errors while setting search path to schemas

Problem

I am experiencing issues while using SET commands such as the following:

SET search_path TO my_schema

Cause

• SET search_path TO commands to use schemas works, but will be shared across multiple clients due to connection pooling, and will lead to unwanted effects.

Possible solutions

• Use fully qualified schema names (schema.table) to perform stateless and self-contained transactions that do not rely on or modify session-level settings.

• Perform the SET command in the same transaction as the request, using the SET LOCAL command so it applies to the current transaction only:

BEGIN;
SET LOCAL search_path TO schema1;
SELECT ... ;
COMMIT;