prompt: We are currently solving the following issue within our repository. Here is the issue text:
  --- BEGIN ISSUE ---
  SEARCH_PATH for Geotrek DB user
  Since Geotrek 0.28, tables and functions have be moved to different schemas, which is a very good point (https://github.com/makinacorpus/Geotrek/releases/tag/v0.28.0).
  
  Schemas are not mentionned in triggers which is OK too, as Django is doing it in his connexions so it is not a problem for GEOTREK applications.
  
  It gets a problem when you try to edit or insert a data from an external tool (QGIS, Talend...). 
  You have to change the db_user search_path so that he can find tables and functions not only in public schemas.
  
  It could be interesting to do it during GEOTREK installation for the Geotrek DB user mentionned in settings : 
  
  ALTER USER $geotrek_db_user SET 
  search_path=public,django,geotrek,gestion,rando,zonage,foncier,tourisme; 
  
  Of course if you are using another user to edit datas in external tools, you will have to do it manually the first time. 
  
  
  --- END ISSUE ---
  
  Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
  --- BEGIN FILES ---
  Path: `geotrek/common/utils/postgresql.py`
  Content:
  ```
  1 import re
  2 import os
  3 import logging
  4 import traceback
  5 from functools import wraps
  6 
  7 from django.db import connection, models
  8 from django.conf import settings
  9 from django.db.models import get_app, get_models
  10 
  11 
  12 logger = logging.getLogger(__name__)
  13 
  14 
  15 def debug_pg_notices(f):
  16 
  17     @wraps(f)
  18     def wrapped(*args, **kwargs):
  19         before = len(connection.connection.notices) if connection.connection else 0
  20         try:
  21             r = f(*args, **kwargs)
  22         finally:
  23             # Show triggers output
  24             allnotices = []
  25             current = ''
  26             if connection.connection:
  27                 notices = []
  28                 for notice in connection.connection.notices[before:]:
  29                     try:
  30                         notice, context = notice.split('CONTEXT:', 1)
  31                         context = re.sub("\s+", " ", context)
  32                     except ValueError:
  33                         context = ''
  34                     notices.append((context, notice))
  35                     if context != current:
  36                         allnotices.append(notices)
  37                         notices = []
  38                         current = context
  39                 allnotices.append(notices)
  40             current = ''
  41             for notices in allnotices:
  42                 for context, notice in notices:
  43                     if context != current:
  44                         if context != '':
  45                             logger.debug('Context %s...:' % context.strip()[:80])
  46                         current = context
  47                     notice = notice.replace('NOTICE: ', '')
  48                     prefix = '' if context == '' else '        '
  49                     logger.debug('%s%s' % (prefix, notice.strip()))
  50         return r
  51 
  52     return wrapped
  53 
  54 
  55 def load_sql_files(app_label):
  56     """
  57     Look for SQL files in Django app, and load them into database.
  58     We remove RAISE NOTICE instructions from SQL outside unit testing
  59     since they lead to interpolation errors of '%' character in python.
  60     """
  61     app_dir = os.path.dirname(models.get_app(app_label).__file__)
  62     sql_dir = os.path.normpath(os.path.join(app_dir, 'sql'))
  63     if not os.path.exists(sql_dir):
  64         logger.debug("No SQL folder for %s" % app_label)
  65         return
  66 
  67     r = re.compile(r'^.*\.sql$')
  68     sql_files = [os.path.join(sql_dir, f)
  69                  for f in os.listdir(sql_dir)
  70                  if r.match(f) is not None]
  71     sql_files.sort()
  72 
  73     if len(sql_files) == 0:
  74         logger.warning("Empty folder %s" % sql_dir)
  75 
  76     cursor = connection.cursor()
  77     for sql_file in sql_files:
  78         try:
  79             logger.info("Loading initial SQL data from '%s'" % sql_file)
  80             f = open(sql_file)
  81             sql = f.read()
  82             f.close()
  83             if not settings.TEST:
  84                 # Remove RAISE NOTICE (/!\ only one-liners)
  85                 sql = re.sub(r"\n.*RAISE NOTICE.*\n", "\n", sql)
  86                 # TODO: this is the ugliest driver hack ever
  87                 sql = sql.replace('%', '%%')
  88 
  89             # Replace curly braces with settings values
  90             pattern = re.compile(r'{{\s*(.*)\s*}}')
  91             for m in pattern.finditer(sql):
  92                 value = getattr(settings, m.group(1))
  93                 sql = sql.replace(m.group(0), unicode(value))
  94             cursor.execute(sql)
  95         except Exception as e:
  96             logger.critical("Failed to install custom SQL file '%s': %s\n" %
  97                             (sql_file, e))
  98             traceback.print_exc()
  99             raise
  100 
  101 
  102 def move_models_to_schemas(app_label):
  103     """
  104     Move models tables to PostgreSQL schemas.
  105 
  106     Views, functions and triggers will be moved in Geotrek app SQL files.
  107     """
  108     app = get_app(app_label)
  109     default_schema = settings.DATABASE_SCHEMAS.get('default')
  110     app_schema = settings.DATABASE_SCHEMAS.get(app_label, default_schema)
  111 
  112     table_schemas = {}
  113     for model in get_models(app):
  114         model_name = model._meta.module_name
  115         table_name = model._meta.db_table
  116         model_schema = settings.DATABASE_SCHEMAS.get(model_name, app_schema)
  117         table_schemas.setdefault(model_schema, []).append(table_name)
  118 
  119         for m2m_field in model._meta.many_to_many:
  120             table_name = m2m_field.db_table
  121             if table_name:
  122                 table_schemas[model_schema].append(table_name)
  123 
  124     cursor = connection.cursor()
  125 
  126     for schema_name in table_schemas.keys():
  127         try:
  128             sql = "CREATE SCHEMA %s;" % model_schema
  129             cursor.execute(sql)
  130             logger.info("Created schema %s" % model_schema)
  131         except Exception:
  132             logger.debug("Schema %s already exists." % model_schema)
  133 
  134     for schema_name, tables in table_schemas.items():
  135         for table_name in tables:
  136             try:
  137                 sql = "ALTER TABLE %s SET SCHEMA %s;" % (table_name, schema_name)
  138                 cursor.execute(sql)
  139                 logger.info("Moved %s to schema %s" % (table_name, schema_name))
  140             except Exception:
  141                 logger.debug("Table %s already in schema %s" % (table_name, schema_name))
  142
  ```
  --- END FILES ---
  
  Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.
  
  Here is an example:
  
  ```diff
  diff --git a/examples/server_async.py b/examples/server_async.py
  --- a/examples/server_async.py
  +++ b/examples/server_async.py
  @@ -313,4 +313,4 @@
   
   
   if __name__ == "__main__":
  -    asyncio.run(run_async_server("."), debug=True)
  +    asyncio.run(run_async_server(), debug=True)
  diff --git a/examples/server_sync.py b/examples/server_sync.py
  --- a/examples/server_sync.py
  +++ b/examples/server_sync.py
  @@ -313,5 +313,5 @@
   
   
   if __name__ == "__main__":
  -    server = run_sync_server(".")
  +    server = run_sync_server()
       server.shutdown()
  
  ```
  
golden_diff: diff --git a/geotrek/common/utils/postgresql.py b/geotrek/common/utils/postgresql.py
  --- a/geotrek/common/utils/postgresql.py
  +++ b/geotrek/common/utils/postgresql.py
  @@ -139,3 +139,12 @@
                   logger.info("Moved %s to schema %s" % (table_name, schema_name))
               except Exception:
                   logger.debug("Table %s already in schema %s" % (table_name, schema_name))
  +
  +    # For Django, search_path is set in connection options.
  +    # But when accessing the database using QGis or ETL, search_path must be
  +    # set database level (for all users, and for this database only).
  +    if app_label == 'common':
  +        dbname = settings.DATABASES['default']['NAME']
  +        search_path = 'public,%s' % ','.join(set(settings.DATABASE_SCHEMAS.values()))
  +        sql = "ALTER DATABASE %s SET search_path=%s;" % (dbname, search_path)
  +        cursor.execute(sql)
  
verification_info: {"golden_diff": "diff --git a/geotrek/common/utils/postgresql.py b/geotrek/common/utils/postgresql.py\n--- a/geotrek/common/utils/postgresql.py\n+++ b/geotrek/common/utils/postgresql.py\n@@ -139,3 +139,12 @@\n                 logger.info(\"Moved %s to schema %s\" % (table_name, schema_name))\n             except Exception:\n                 logger.debug(\"Table %s already in schema %s\" % (table_name, schema_name))\n+\n+    # For Django, search_path is set in connection options.\n+    # But when accessing the database using QGis or ETL, search_path must be\n+    # set database level (for all users, and for this database only).\n+    if app_label == 'common':\n+        dbname = settings.DATABASES['default']['NAME']\n+        search_path = 'public,%s' % ','.join(set(settings.DATABASE_SCHEMAS.values()))\n+        sql = \"ALTER DATABASE %s SET search_path=%s;\" % (dbname, search_path)\n+        cursor.execute(sql)\n", "issue": "SEARCH_PATH for Geotrek DB user\nSince Geotrek 0.28, tables and functions have be moved to different schemas, which is a very good point (https://github.com/makinacorpus/Geotrek/releases/tag/v0.28.0).\n\nSchemas are not mentionned in triggers which is OK too, as Django is doing it in his connexions so it is not a problem for GEOTREK applications.\n\nIt gets a problem when you try to edit or insert a data from an external tool (QGIS, Talend...). \nYou have to change the db_user search_path so that he can find tables and functions not only in public schemas.\n\nIt could be interesting to do it during GEOTREK installation for the Geotrek DB user mentionned in settings : \n\nALTER USER $geotrek_db_user SET \nsearch_path=public,django,geotrek,gestion,rando,zonage,foncier,tourisme; \n\nOf course if you are using another user to edit datas in external tools, you will have to do it manually the first time. \n\n", "before_files": [{"content": "import re\nimport os\nimport logging\nimport traceback\nfrom functools import wraps\n\nfrom django.db import connection, models\nfrom django.conf import settings\nfrom django.db.models import get_app, get_models\n\n\nlogger = logging.getLogger(__name__)\n\n\ndef debug_pg_notices(f):\n\n    @wraps(f)\n    def wrapped(*args, **kwargs):\n        before = len(connection.connection.notices) if connection.connection else 0\n        try:\n            r = f(*args, **kwargs)\n        finally:\n            # Show triggers output\n            allnotices = []\n            current = ''\n            if connection.connection:\n                notices = []\n                for notice in connection.connection.notices[before:]:\n                    try:\n                        notice, context = notice.split('CONTEXT:', 1)\n                        context = re.sub(\"\\s+\", \" \", context)\n                    except ValueError:\n                        context = ''\n                    notices.append((context, notice))\n                    if context != current:\n                        allnotices.append(notices)\n                        notices = []\n                        current = context\n                allnotices.append(notices)\n            current = ''\n            for notices in allnotices:\n                for context, notice in notices:\n                    if context != current:\n                        if context != '':\n                            logger.debug('Context %s...:' % context.strip()[:80])\n                        current = context\n                    notice = notice.replace('NOTICE: ', '')\n                    prefix = '' if context == '' else '        '\n                    logger.debug('%s%s' % (prefix, notice.strip()))\n        return r\n\n    return wrapped\n\n\ndef load_sql_files(app_label):\n    \"\"\"\n    Look for SQL files in Django app, and load them into database.\n    We remove RAISE NOTICE instructions from SQL outside unit testing\n    since they lead to interpolation errors of '%' character in python.\n    \"\"\"\n    app_dir = os.path.dirname(models.get_app(app_label).__file__)\n    sql_dir = os.path.normpath(os.path.join(app_dir, 'sql'))\n    if not os.path.exists(sql_dir):\n        logger.debug(\"No SQL folder for %s\" % app_label)\n        return\n\n    r = re.compile(r'^.*\\.sql$')\n    sql_files = [os.path.join(sql_dir, f)\n                 for f in os.listdir(sql_dir)\n                 if r.match(f) is not None]\n    sql_files.sort()\n\n    if len(sql_files) == 0:\n        logger.warning(\"Empty folder %s\" % sql_dir)\n\n    cursor = connection.cursor()\n    for sql_file in sql_files:\n        try:\n            logger.info(\"Loading initial SQL data from '%s'\" % sql_file)\n            f = open(sql_file)\n            sql = f.read()\n            f.close()\n            if not settings.TEST:\n                # Remove RAISE NOTICE (/!\\ only one-liners)\n                sql = re.sub(r\"\\n.*RAISE NOTICE.*\\n\", \"\\n\", sql)\n                # TODO: this is the ugliest driver hack ever\n                sql = sql.replace('%', '%%')\n\n            # Replace curly braces with settings values\n            pattern = re.compile(r'{{\\s*(.*)\\s*}}')\n            for m in pattern.finditer(sql):\n                value = getattr(settings, m.group(1))\n                sql = sql.replace(m.group(0), unicode(value))\n            cursor.execute(sql)\n        except Exception as e:\n            logger.critical(\"Failed to install custom SQL file '%s': %s\\n\" %\n                            (sql_file, e))\n            traceback.print_exc()\n            raise\n\n\ndef move_models_to_schemas(app_label):\n    \"\"\"\n    Move models tables to PostgreSQL schemas.\n\n    Views, functions and triggers will be moved in Geotrek app SQL files.\n    \"\"\"\n    app = get_app(app_label)\n    default_schema = settings.DATABASE_SCHEMAS.get('default')\n    app_schema = settings.DATABASE_SCHEMAS.get(app_label, default_schema)\n\n    table_schemas = {}\n    for model in get_models(app):\n        model_name = model._meta.module_name\n        table_name = model._meta.db_table\n        model_schema = settings.DATABASE_SCHEMAS.get(model_name, app_schema)\n        table_schemas.setdefault(model_schema, []).append(table_name)\n\n        for m2m_field in model._meta.many_to_many:\n            table_name = m2m_field.db_table\n            if table_name:\n                table_schemas[model_schema].append(table_name)\n\n    cursor = connection.cursor()\n\n    for schema_name in table_schemas.keys():\n        try:\n            sql = \"CREATE SCHEMA %s;\" % model_schema\n            cursor.execute(sql)\n            logger.info(\"Created schema %s\" % model_schema)\n        except Exception:\n            logger.debug(\"Schema %s already exists.\" % model_schema)\n\n    for schema_name, tables in table_schemas.items():\n        for table_name in tables:\n            try:\n                sql = \"ALTER TABLE %s SET SCHEMA %s;\" % (table_name, schema_name)\n                cursor.execute(sql)\n                logger.info(\"Moved %s to schema %s\" % (table_name, schema_name))\n            except Exception:\n                logger.debug(\"Table %s already in schema %s\" % (table_name, schema_name))\n", "path": "geotrek/common/utils/postgresql.py"}], "after_files": [{"content": "import re\nimport os\nimport logging\nimport traceback\nfrom functools import wraps\n\nfrom django.db import connection, models\nfrom django.conf import settings\nfrom django.db.models import get_app, get_models\n\n\nlogger = logging.getLogger(__name__)\n\n\ndef debug_pg_notices(f):\n\n    @wraps(f)\n    def wrapped(*args, **kwargs):\n        before = len(connection.connection.notices) if connection.connection else 0\n        try:\n            r = f(*args, **kwargs)\n        finally:\n            # Show triggers output\n            allnotices = []\n            current = ''\n            if connection.connection:\n                notices = []\n                for notice in connection.connection.notices[before:]:\n                    try:\n                        notice, context = notice.split('CONTEXT:', 1)\n                        context = re.sub(\"\\s+\", \" \", context)\n                    except ValueError:\n                        context = ''\n                    notices.append((context, notice))\n                    if context != current:\n                        allnotices.append(notices)\n                        notices = []\n                        current = context\n                allnotices.append(notices)\n            current = ''\n            for notices in allnotices:\n                for context, notice in notices:\n                    if context != current:\n                        if context != '':\n                            logger.debug('Context %s...:' % context.strip()[:80])\n                        current = context\n                    notice = notice.replace('NOTICE: ', '')\n                    prefix = '' if context == '' else '        '\n                    logger.debug('%s%s' % (prefix, notice.strip()))\n        return r\n\n    return wrapped\n\n\ndef load_sql_files(app_label):\n    \"\"\"\n    Look for SQL files in Django app, and load them into database.\n    We remove RAISE NOTICE instructions from SQL outside unit testing\n    since they lead to interpolation errors of '%' character in python.\n    \"\"\"\n    app_dir = os.path.dirname(models.get_app(app_label).__file__)\n    sql_dir = os.path.normpath(os.path.join(app_dir, 'sql'))\n    if not os.path.exists(sql_dir):\n        logger.debug(\"No SQL folder for %s\" % app_label)\n        return\n\n    r = re.compile(r'^.*\\.sql$')\n    sql_files = [os.path.join(sql_dir, f)\n                 for f in os.listdir(sql_dir)\n                 if r.match(f) is not None]\n    sql_files.sort()\n\n    if len(sql_files) == 0:\n        logger.warning(\"Empty folder %s\" % sql_dir)\n\n    cursor = connection.cursor()\n    for sql_file in sql_files:\n        try:\n            logger.info(\"Loading initial SQL data from '%s'\" % sql_file)\n            f = open(sql_file)\n            sql = f.read()\n            f.close()\n            if not settings.TEST:\n                # Remove RAISE NOTICE (/!\\ only one-liners)\n                sql = re.sub(r\"\\n.*RAISE NOTICE.*\\n\", \"\\n\", sql)\n                # TODO: this is the ugliest driver hack ever\n                sql = sql.replace('%', '%%')\n\n            # Replace curly braces with settings values\n            pattern = re.compile(r'{{\\s*(.*)\\s*}}')\n            for m in pattern.finditer(sql):\n                value = getattr(settings, m.group(1))\n                sql = sql.replace(m.group(0), unicode(value))\n            cursor.execute(sql)\n        except Exception as e:\n            logger.critical(\"Failed to install custom SQL file '%s': %s\\n\" %\n                            (sql_file, e))\n            traceback.print_exc()\n            raise\n\n\ndef move_models_to_schemas(app_label):\n    \"\"\"\n    Move models tables to PostgreSQL schemas.\n\n    Views, functions and triggers will be moved in Geotrek app SQL files.\n    \"\"\"\n    app = get_app(app_label)\n    default_schema = settings.DATABASE_SCHEMAS.get('default')\n    app_schema = settings.DATABASE_SCHEMAS.get(app_label, default_schema)\n\n    table_schemas = {}\n    for model in get_models(app):\n        model_name = model._meta.module_name\n        table_name = model._meta.db_table\n        model_schema = settings.DATABASE_SCHEMAS.get(model_name, app_schema)\n        table_schemas.setdefault(model_schema, []).append(table_name)\n\n        for m2m_field in model._meta.many_to_many:\n            table_name = m2m_field.db_table\n            if table_name:\n                table_schemas[model_schema].append(table_name)\n\n    cursor = connection.cursor()\n\n    for schema_name in table_schemas.keys():\n        try:\n            sql = \"CREATE SCHEMA %s;\" % model_schema\n            cursor.execute(sql)\n            logger.info(\"Created schema %s\" % model_schema)\n        except Exception:\n            logger.debug(\"Schema %s already exists.\" % model_schema)\n\n    for schema_name, tables in table_schemas.items():\n        for table_name in tables:\n            try:\n                sql = \"ALTER TABLE %s SET SCHEMA %s;\" % (table_name, schema_name)\n                cursor.execute(sql)\n                logger.info(\"Moved %s to schema %s\" % (table_name, schema_name))\n            except Exception:\n                logger.debug(\"Table %s already in schema %s\" % (table_name, schema_name))\n\n    # For Django, search_path is set in connection options.\n    # But when accessing the database using QGis or ETL, search_path must be\n    # set database level (for all users, and for this database only).\n    if app_label == 'common':\n        dbname = settings.DATABASES['default']['NAME']\n        search_path = 'public,%s' % ','.join(set(settings.DATABASE_SCHEMAS.values()))\n        sql = \"ALTER DATABASE %s SET search_path=%s;\" % (dbname, search_path)\n        cursor.execute(sql)\n", "path": "geotrek/common/utils/postgresql.py"}]}