Gerenciamento de Vistas PostgreSQL

Introdução

Neste laboratório, você explorará a Gestão de Visões PostgreSQL. O objetivo principal é compreender e implementar diferentes tipos de visões, incluindo visões simples e visões materializadas.

Você começará definindo uma visão simples baseada na tabela employees, demonstrando como criar uma visão que selecione colunas específicas. Em seguida, aprenderá como consultar e, potencialmente, modificar dados através de visões. Finalmente, o laboratório abrange a criação e população de visões materializadas, juntamente com a atualização manual dessas visões para mantê-las atualizadas.

Definir uma Vista Simples

Neste passo, aprenderá a definir uma visão simples no PostgreSQL. As visões são tabelas virtuais baseadas no conjunto de resultados de uma instrução SQL. São úteis para simplificar consultas complexas, fornecer abstração e controlar o acesso a dados.

Compreendendo Visões

Uma visão é essencialmente uma consulta armazenada. Quando consulta uma visão, o PostgreSQL executa a consulta subjacente e retorna o conjunto de resultados como se fosse uma tabela real. As visões não armazenam dados por si mesmas; elas fornecem uma forma diferente de aceder aos dados armazenados nas tabelas base.

Criando a Tabela employees

Primeiro, vamos criar uma tabela chamada employees para trabalhar. Abra um terminal e conecte-se ao banco de dados PostgreSQL como o utilizador postgres:

sudo -u postgres psql

Agora, crie a tabela employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Em seguida, insira alguns dados de amostra na tabela employees:

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('John', 'Doe', 'Sales', 60000.00),
('Jane', 'Smith', 'Marketing', 75000.00),
('Robert', 'Jones', 'Sales', 62000.00),
('Emily', 'Brown', 'IT', 80000.00),
('Michael', 'Davis', 'Marketing', 70000.00);

Pode verificar os dados executando a seguinte consulta:

SELECT * FROM employees;

Deverá ver os dados inseridos na saída.

Definindo a Visão employee_info

Agora que temos uma tabela com dados, vamos criar uma visão simples. Esta visão mostrará apenas o primeiro nome, o último nome e o departamento de cada empregado. Podemos definir uma visão chamada employee_info usando a seguinte instrução SQL:

CREATE VIEW employee_info AS
SELECT first_name, last_name, department
FROM employees;

Esta instrução cria uma visão chamada employee_info que seleciona as colunas first_name, last_name e department da tabela employees.

Consultando a Visão

Para consultar a visão, pode usar uma instrução SELECT tal como faria com uma tabela regular:

SELECT * FROM employee_info;

Esta consulta retornará o primeiro nome, o último nome e o departamento de todos os empregados, conforme definido na visão.

Descrevendo a Visão

Pode descrever a visão usando o comando \d no psql:

\d employee_info

Isto mostrará a definição da visão e as colunas que contém.

Consultar e Modificar Dados Através de Vistas

Neste passo, aprenderá a consultar e modificar dados através de visões no PostgreSQL. Embora as visões sejam principalmente usadas para consultar dados, em alguns casos, também podem ser usadas para modificar os dados subjacentes nas tabelas base.

Consultar Dados Através de Visões

Como demonstrado no passo anterior, consultar dados através de uma visão é simples. Pode usar uma instrução SELECT para recuperar dados da visão como se fosse uma tabela regular.

Por exemplo, para recuperar todos os dados da visão employee_info:

SELECT * FROM employee_info;

Também pode usar cláusulas WHERE e outras construções SQL para filtrar e ordenar os dados:

SELECT * FROM employee_info WHERE department = 'Sales';

Esta consulta retornará apenas os empregados no departamento de Vendas.

Modificar Dados Através de Visões

Modificar dados através de uma visão é possível em certas condições. A visão deve ser suficientemente simples para que o PostgreSQL determine qual a tabela base e as colunas a atualizar. Geralmente, uma visão é modificável se atender aos seguintes critérios:

• Seleciona apenas de uma tabela.
• Não contém funções agregadas (por exemplo, SUM, AVG, COUNT).
• Não contém cláusulas GROUP BY, HAVING ou DISTINCT.

Vamos criar outra visão que inclua o employee_id para permitir atualizações mais fáceis:

CREATE VIEW employee_details AS
SELECT employee_id, first_name, last_name, department, salary
FROM employees;

Agora, vamos tentar atualizar o salário de um empregado através da visão employee_details:

UPDATE employee_details
SET salary = 65000.00
WHERE employee_id = 1;

Esta instrução atualiza o salário do empregado com employee_id 1 para 65000.00.

Pode verificar a atualização consultando a tabela employees diretamente:

SELECT * FROM employees WHERE employee_id = 1;

Deverá ver que o salário para employee_id 1 foi atualizado.

Inserir Dados Através de Visões

Também pode inserir dados através de uma visão, desde que a visão inclua todas as colunas não nulas da tabela base. Como a nossa visão employee_details inclui todas as colunas da tabela employees, podemos inserir um novo empregado:

INSERT INTO employee_details (first_name, last_name, department, salary)
VALUES ('David', 'Lee', 'IT', 90000.00);

Note que não especificamos o employee_id, pois é uma coluna serial e será gerada automaticamente.

Verifique a inserção:

SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';

Eliminar Dados Através de Visões

Da mesma forma, pode eliminar dados através de uma visão modificável:

DELETE FROM employee_details WHERE first_name = 'David' AND last_name = 'Lee';

Verifique a eliminação:

SELECT * FROM employees WHERE first_name = 'David' AND last_name = 'Lee';

Considerações Importantes

• Nem todas as visões são modificáveis. Visões complexas com junções, agregações ou outras operações complexas são normalmente de leitura apenas.
• Modificar dados através de visões pode ter implicações de desempenho. O PostgreSQL precisa traduzir as operações da visão em operações nas tabelas base subjacentes.
• Tenha cuidado ao modificar dados através de visões, pois as alterações afetarão diretamente as tabelas base.

Criar e Popular uma Vista Materializada

Neste passo, aprenderá a criar e popular uma visão materializada no PostgreSQL. Ao contrário das visões regulares, as visões materializadas armazenam o conjunto de resultados da consulta como uma tabela física. Isto pode melhorar significativamente o desempenho das consultas, especialmente para consultas complexas ou consultas que acedam a dados de fontes remotas. No entanto, os dados numa visão materializada não são automaticamente atualizados quando os dados subjacentes mudam. É necessário atualizá-la manualmente ou agendá-la para ser atualizada periodicamente.

Criar uma Visão Materializada

Para criar uma visão materializada, utiliza a instrução CREATE MATERIALIZED VIEW. Vamos criar uma visão materializada chamada employee_salaries que mostra o salário médio por departamento.

CREATE MATERIALIZED VIEW employee_salaries AS
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Esta instrução cria uma visão materializada chamada employee_salaries que calcula o salário médio por departamento com base nos dados da tabela employees.

Consultar a Visão Materializada

Pode consultar uma visão materializada como uma tabela regular:

SELECT * FROM employee_salaries;

Isto retornará o departamento e o salário médio para cada departamento, com base nos dados que estavam presentes na tabela employees no momento da criação da visão materializada.

Popular a Visão Materializada

Quando cria uma visão materializada, ela é automaticamente populada com os dados iniciais. No entanto, se os dados subjacentes na tabela employees mudarem, os dados na visão materializada employee_salaries não serão automaticamente atualizados.

Vamos inserir um novo empregado na tabela employees:

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Alice', 'Johnson', 'IT', 85000.00);

Agora, se consultar novamente a visão materializada employee_salaries:

SELECT * FROM employee_salaries;

Notará que o salário médio do departamento de TI não mudou para refletir o novo empregado. Isto porque a visão materializada não foi atualizada.

Descrever a Visão Materializada

Pode descrever a visão materializada usando o comando \d no psql:

\d employee_salaries

Isto mostrará a definição da visão materializada e as colunas que contém.

Atualizar Manualmente uma Vista Materializada

Nesta etapa, você aprenderá como atualizar manualmente uma materialized view no PostgreSQL. Conforme mencionado na etapa anterior, as materialized views não são atualizadas automaticamente quando os dados subjacentes mudam. Para refletir os dados mais recentes, você precisa atualizá-las explicitamente.

Atualizando a Materialized View

Para atualizar uma materialized view, você usa a instrução REFRESH MATERIALIZED VIEW. Existem duas opções principais:

• REFRESH MATERIALIZED VIEW nome_da_view: Isso atualizará a materialized view reexecutando a consulta que a define. Ela adquire um lock ACCESS EXCLUSIVE na materialized view, impedindo o acesso concorrente.

• REFRESH MATERIALIZED VIEW CONCURRENTLY nome_da_view: Isso atualizará a materialized view sem bloquear consultas concorrentes. No entanto, requer que a materialized view tenha pelo menos um índice.

Vamos primeiro tentar atualizar a materialized view employee_salaries usando o comando padrão REFRESH MATERIALIZED VIEW:

REFRESH MATERIALIZED VIEW employee_salaries;

Agora, consulte a materialized view employee_salaries novamente:

SELECT * FROM employee_salaries;

Você deverá ver que o salário médio do departamento de TI foi atualizado para refletir o novo funcionário.

Atualizando Concorrentemente

Para atualizar a materialized view concorrentemente, primeiro precisamos criar um índice UNIQUE nela. Este é um requisito para atualizações concorrentes, pois o PostgreSQL precisa de uma maneira de identificar unicamente as linhas para realizar a atualização sem bloquear toda a view. A coluna department em nossa view employee_salaries é única porque nossa view agrupa por departamento, então podemos criar um índice único nela.

Vamos criar um índice único na coluna department:

CREATE UNIQUE INDEX idx_employee_salaries_department ON employee_salaries (department);

Agora, podemos atualizar a materialized view concorrentemente:

REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;

Consulte a materialized view employee_salaries novamente para confirmar que os dados ainda estão atualizados:

SELECT * FROM employee_salaries;

Escolhendo o Método de Atualização Correto

• Use REFRESH MATERIALIZED VIEW para materialized views simples ou quando você puder tolerar um curto período de indisponibilidade.
• Use REFRESH MATERIALIZED VIEW CONCURRENTLY para materialized views maiores ou quando precisar minimizar a interrupção de consultas concorrentes. Lembre-se de criar um índice na materialized view primeiro.

Considerações Importantes

• A atualização de uma materialized view pode ser uma operação intensiva em recursos, especialmente para grandes conjuntos de dados.
• Considere agendar atualizações regulares usando uma ferramenta como cron para manter os dados na materialized view atualizados.

Lembre-se de sair do shell psql digitando \q e pressionando Enter.

Resumo

Neste laboratório, aprendeu a definir uma vista simples no PostgreSQL. Começou por criar uma tabela employees com dados de amostra, incluindo colunas para ID do empregado, primeiro nome, último nome, departamento e salário. Em seguida, definiu uma vista chamada employee_info que seleciona apenas o primeiro nome, o último nome e o departamento da tabela employees, demonstrando como as vistas podem simplificar consultas e fornecer uma perspectiva específica sobre os dados subjacentes.

Também aprendeu a consultar e modificar dados através de vistas, e como criar e atualizar vistas materializadas. As vistas materializadas armazenam o resultado de uma consulta como uma tabela, melhorando o desempenho para consultas complexas. Explorou diferentes métodos para atualizar vistas materializadas, incluindo a atualização concorrente para minimizar interrupções.