postgresql - ¿Cómo influye search_path en la resolución del identificador y el "esquema actual"?

schema database-table

Question

¿Es posible definir en qué esquema se crean nuevas tablas de forma predeterminada? (Referido por "nombres de tabla no calificados").

He visto algunos detalles sobre el uso de la "ruta de búsqueda" en Postgres, pero creo que solo funciona al recuperar datos, no al crearlos.

Tengo un montón de scripts SQL, que crean muchas tablas. En lugar de modificar los scripts, quiero establecer las tablas de creación de base de datos en un esquema específico de forma predeterminada, cuando tienen nombres no calificados.

es posible?

Answer 1

La ruta de búsqueda es de hecho lo que quieres:

% create schema blarg;
% set search_path to blarg;
% create table foo (id int);
% /d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
blarg | foo | table | pgsql

Answer 2

¿Cuál es la ruta de búsqueda?

Por documentación:

[...] las tablas a menudo se mencionan con nombres no calificados, que consisten solo en el nombre de la tabla. El sistema determina qué tabla quiere decir siguiendo una ruta de búsqueda, que es una lista de esquemas para buscar .

Negrita énfasis mío. Esto explica la resolución del identificador , y el "esquema actual" es, según la documentación:

El primer esquema nombrado en la ruta de búsqueda se llama esquema actual . Además de ser el primer esquema buscado, también es el esquema en el que se crearán nuevas tablas si el comando CREATE TABLE no especifica un nombre de esquema.

Negrita énfasis mío. Los esquemas de sistema pg_temp (esquema para objetos temporales de la sesión actual) y pg_catalog son automáticamente parte de la ruta de búsqueda y se buscan primero , en este orden. Por documentación:

pg_catalog siempre es efectivamente parte de la ruta de búsqueda. Si no se nombra explícitamente en la ruta, se busca implícitamente antes de buscar los esquemas de la ruta. Esto garantiza que los nombres incorporados siempre sean identificables. Sin embargo, puede colocar explícitamente pg_catalog al final de su ruta de búsqueda si prefiere que los nombres definidos por el usuario anulen los nombres incorporados.

Énfasis en negrita según el original. Y pg_temp viene antes de eso, a menos que se ponga en una posición diferente.

¿Cómo configurarlo?

Tiene varias opciones para establecer realmente la variable de tiempo de ejecución search_path .

1. Establezca un valor predeterminado de clúster para todos los roles en todas las bases de datos en postgresql.conf (y vuelva a cargar). ¡Cuidado con eso!

   search_path = ''blarg,public''

   El valor predeterminado enviado para esta configuración es:

   search_path = "$user",public

   El primer elemento especifica que se debe buscar un esquema con el mismo nombre que el usuario actual. Si no existe tal esquema, la entrada se ignora.

2. Establecerlo como predeterminado para una base de datos :

   ALTER DATABASE test SET search_path = blarg,public;

3. Configúrelo como predeterminado para la función con la que se conecta (efectivo en todo el clúster):

   ALTER ROLE foo SET search_path = blarg,public;

4. O incluso (¡a menudo lo mejor!) Como predeterminado para el rol solo en una base de datos dada :

   ALTER ROLE foo IN DATABASE test SET search_path = blarg,public;

5. Escriba el comando en la parte superior de su script (O ejecútelo en cualquier punto de su sesión :

   SET search_path = blarg,public;

6. Establezca un search_path específico para el alcance de una función (para estar a salvo de usuarios malintencionados con suficientes privilegios). Lea sobre Escribir SECURITY DEFINER Funciones de forma segura en el manual.

CREATE FUNCTION foo() RETURNS void AS
$func$
BEGIN
-- do stuff
END
$func$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path=blarg,public,pg_temp;

El número más alto en mi lista supera al número más bajo.
El manual tiene aún más formas , como establecer variables de entorno o usar opciones de línea de comandos.

Para ver la configuración actual:

SHOW search_path;

Para reset :

RESET search_path;

reset :

El valor predeterminado se define como el valor que tendría el parámetro, si nunca se había emitido SET para él en la sesión actual.