postgres postgresql schema namespace search_path

Um schema (também chamado de namespace) é uma separação lógica dos objetos do banco de dados. Assim como arquivos não estão todos na raiz de um sistema de arquivos, mas sim organizados em diretórios, também objetos (tabelas, visões…) do banco de dados são colocados em espaços próprios. Dessa forma, o usuário pode trabalhar com um conjunto de objetos de cada vez, alcançando maior produtividades em tarefas como exportação e importação de dados, movimentação de objetos entre tablespaces, mapeamento de schemas remotos, alteração de permissões em lote, entre outras.

                     ┌───────────────────┐
╔════════════════════╡ instância/cluster ╞═════════════════════╗
║                    └───────────────────┘                     ║
╠═╡global/╞═══════╗  ╔═════════════════════════════════╡base/╞═╣
║                 ║  ║                                         ║
║     O     O     ║  ║ ┏┥banco de dados┝┓ ┏━┥banco de dados┝━┓ ║
║    ─┼─   ─┼─    ║  ║ ┃└┤postgres├────┘┃ ┃ ┌─┤schema├─────┐ ┃ ║
║     │     │     ║  ║ ┗━━━━━━━━━━━━━━━━┛ ┃ │ ☑ tabelas    │ ┃ ║
║    ╱ ╲   ╱ ╲    ║  ║ ┏┥banco de dados┝┓ ┃ │ ☑ índices    │ ┃ ║
║ ☑ usuários      ║  ║ ┃└┤template1├───┘┃ ┃ │ ☑ visões     │ ┃ ║
║ ☑ grupos        ║  ║ ┗━━━━━━━━━━━━━━━━┛ ┃ │ ☑ sequências │ ┃ ║
║ ☑ slots de      ║  ║ ┏┥banco de dados┝┓ ┃ │ ☑ …          │ ┃ ║
║ ☑ replicação    ║  ║ ┃└┤template0├───┘┃ ┃ └──────────────┘ ┃ ║
║ ☑ pg_control    ║  ║ ┗━━━━━━━━━━━━━━━━┛ ┗━━━━━━━━━━━━━━━━━━┛ ║
║                 ║  ║                                         ║
╠═════════════════╝  ╚═════════════════════════════════════════╣
║                                                              ║
╠═╡pg_wal/╞══════════════════════════╗     ╔══════╡pg_tblspc/╞═╣
║ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐    ║     ║                   ║
║ │…++++│→│…++++│→│…++++│→│…++++│→…  ║     ║                   ║
║ └┤/…1├┘ └┤/…2├┘ └┤/…3├┘ └┤/…4├┘    ║     ║        │          ║
╚════════════════════════════════════╩═════╩════════╪══════════╝
                                                    │
                                                    ↓
                                                 ╔╡/…╞╗
                                                 ║    ║
                                                 ╚════╝

Quando uma consulta é enviada para execução, objetos são buscados nos schemas específicos mencionados, como em SELECT * FROM public.tabela, que busca a tabela do schema public.

Mas podemos omitir o schema da consulta, como em SELECT * FROM tabela. Nesse caso, o PostgreSQL vai buscar a tabela em uma lista de schemas chamada search_path e usar o primeiro objeto encontrado nos schemas dessa lista, exatamente como o seu shell percorre os diretórios da variável $PATH para buscar executáveis que foram mencionados sem o caminho completo.

O usuário pode consultar o valor do search_path com um SHOW:

[[local]:5432] postgres@postgres=# SHOW search_path;
╔═════════════════╗
║   search_path   ║
╠═════════════════╣
║ "$user", public ║
╚═════════════════╝

O valor "$user", public indica que um schema com o mesmo nome do usuário será consultado primeiro e então um schema com o nome public. Schemas que não existirem são ignorados.

O usuário pode mudar o valor de search_path para aquela sessão com um comando SET, o que não influencia outras sessões:

[[local]:5432] postgres@postgres=# SET search_path to financeiro, rh, public;

Ele também pode alterar permanentemente o valor search_path para um usuário:

[[local]:5432] postgres@postgres=# ALTER USER usuario SET search_path to financeiro, rh, public;

Ou o valor padão para todo usuário que se conectar ao banco de dados:

[[local]:5432] postgres@postgres=# ALTER DATABASE app SET search_path to financeiro, rh, public;

Da mesma forma que search_path é consultado para leitura e escrita de dados dos objetos, ele também é usado para a criação de objetos. Quando um objeto é criado sem mencionar o schema de destino, o primeiro schema existente da lista do search_path vai ser usado como destino. Então uma nova tabela financeiro.contas pode ser criada como:

[[local]:5432] postgres@postgres=# CREATE TABLE financeiro.contas ();

Ou como:

[[local]:5432] postgres@postgres=# SET search_path TO financeiro;
[[local]:5432] postgres@postgres=# CREATE TABLE contas ();