Управление представлениями в PostgreSQL

Введение

В этом лабораторном практикуме вы изучите управление представлениями PostgreSQL. Основная цель — понять и реализовать различные типы представлений, включая простые представления и материализованные представления.

Вы начнете с определения простого представления, основанного на таблице employees, продемонстрировав, как создать представление, выбирающее определенные столбцы. Затем вы узнаете, как выполнять запросы и, возможно, изменять данные через представления. Наконец, практикум охватывает создание и заполнение материализованных представлений, а также ручное обновление этих представлений для поддержания актуальности данных.

Определение простого представления

В этом шаге вы узнаете, как определить простое представление в PostgreSQL. Представления — это виртуальные таблицы, основанные на наборе результатов SQL-запроса. Они полезны для упрощения сложных запросов, предоставления абстракции и управления доступом к данным.

Понимание представлений

Представление по сути является сохранённым запросом. Когда вы выполняете запрос к представлению, PostgreSQL выполняет лежащий в основе запрос и возвращает набор результатов так, как если бы это была реальная таблица. Представления сами по себе не хранят данные; они предоставляют другой способ доступа к данным, хранящимся в базовых таблицах.

Создание таблицы employees

Сначала давайте создадим таблицу с именем employees, с которой будем работать. Откройте терминал и подключитесь к базе данных PostgreSQL как пользователь postgres:

sudo -u postgres psql

Теперь создайте таблицу employees:

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

Далее, вставьте некоторые образцы данных в таблицу 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);

Вы можете проверить данные, выполнив следующий запрос:

SELECT * FROM employees;

В выводе вы должны увидеть вставленные данные.

Определение представления employee_info

Теперь, когда у нас есть таблица с данными, давайте создадим простое представление. Это представление будет отображать только имя, фамилию и отдел каждого сотрудника. Мы можем определить представление с именем employee_info с помощью следующего SQL-запроса:

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

Этот запрос создаёт представление с именем employee_info, которое выбирает столбцы first_name, last_name и department из таблицы employees.

Запрос к представлению

Для запроса к представлению вы можете использовать оператор SELECT так же, как и с обычной таблицей:

SELECT * FROM employee_info;

Этот запрос вернёт имя, фамилию и отдел всех сотрудников, как определено в представлении.

Описание представления

Вы можете описать представление, используя команду \d в psql:

\d employee_info

Это покажет вам определение представления и содержащиеся в нём столбцы.

Описание представления PostgreSQL employee_info

Выполнение запросов и изменение данных через представления

В этом шаге вы узнаете, как выполнять запросы и изменять данные через представления в PostgreSQL. Хотя представления в основном используются для запросов данных, в некоторых случаях их можно использовать для изменения данных в базовых таблицах.

Запрос данных через представления

Как показано в предыдущем шаге, запрос данных через представление прост. Вы можете использовать оператор SELECT, чтобы извлечь данные из представления, как если бы это была обычная таблица.

Например, чтобы извлечь все данные из представления employee_info:

SELECT * FROM employee_info;

Вы также можете использовать предложения WHERE и другие SQL-конструкции для фильтрации и сортировки данных:

SELECT * FROM employee_info WHERE department = 'Sales';

Этот запрос вернёт только сотрудников отдела продаж.

Изменение данных через представления

Изменение данных через представление возможно при определённых условиях. Представление должно быть достаточно простым, чтобы PostgreSQL мог определить, какую базу таблиц и столбцы нужно обновить. Как правило, представление изменяемо, если оно соответствует следующим критериям:

Оно выбирает данные только из одной таблицы.
Оно не содержит агрегатные функции (например, SUM, AVG, COUNT).
Оно не содержит предложения GROUP BY, HAVING или DISTINCT.

Давайте создадим ещё одно представление, включающее employee_id, чтобы упростить обновления:

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

Теперь давайте попробуем обновить зарплату сотрудника через представление employee_details:

UPDATE employee_details
SET salary = 65000.00
WHERE employee_id = 1;

Эта команда обновляет зарплату сотрудника с employee_id 1 до 65000.00.

Вы можете проверить обновление, запросив данные из таблицы employees напрямую:

SELECT * FROM employees WHERE employee_id = 1;

Вы должны увидеть, что зарплата для employee_id 1 была обновлена.

Вставка данных через представления

Вы также можете вставлять данные через представление, при условии, что представление включает все не-NULL столбцы базовой таблицы. Поскольку наше представление employee_details включает все столбцы таблицы employees, мы можем вставить нового сотрудника:

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

Обратите внимание, что мы не указываем employee_id, так как это столбец SERIAL и он будет автоматически сгенерирован.

Проверьте вставку:

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

Удаление данных через представления

Аналогично, вы можете удалить данные через изменяемое представление:

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

Проверьте удаление:

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

Важные моменты

Не все представления изменяемы. Сложные представления с объединениями, агрегациями или другими сложными операциями обычно являются только для чтения.
Изменение данных через представления может повлиять на производительность. PostgreSQL необходимо преобразовать операции представления в операции над базовыми таблицами.
Будьте осторожны при изменении данных через представления, так как изменения будут непосредственно влиять на базовые таблицы.

Пример изменения данных через представление PostgreSQL

Создание и заполнение материализованного представления

В этом шаге вы узнаете, как создать и заполнить материализованное представление в PostgreSQL. В отличие от обычных представлений, материализованные представления хранят результат запроса как физическую таблицу. Это может значительно улучшить производительность запросов, особенно для сложных запросов или запросов, которые обращаются к данным из удалённых источников. Однако данные в материализованном представлении не обновляются автоматически при изменении данных в базовых таблицах. Вам необходимо обновить его вручную или запланировать периодическое обновление.

Создание материализованного представления

Для создания материализованного представления используется оператор CREATE MATERIALIZED VIEW. Давайте создадим материализованное представление с именем employee_salaries, которое отображает среднюю зарплату для каждого отдела.

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

Эта команда создаёт материализованное представление employee_salaries, которое вычисляет среднюю зарплату для каждого отдела на основе данных в таблице employees.

Запрос к материализованному представлению

Вы можете запросить данные из материализованного представления так же, как и из обычной таблицы:

SELECT * FROM employee_salaries;

Это вернёт отдел и среднюю зарплату для каждого отдела, на основе данных, которые были в таблице employees в момент создания материализованного представления.

Заполнение материализованного представления

При создании материализованного представления оно автоматически заполняется начальными данными. Однако, если данные в таблице employees изменятся, данные в материализованном представлении employee_salaries не будут автоматически обновлены.

Давайте вставим нового сотрудника в таблицу employees:

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

Теперь, если вы снова запросите материализованное представление employee_salaries:

SELECT * FROM employee_salaries;

Вы заметите, что средняя зарплата для отдела IT не изменилась, чтобы отразить нового сотрудника. Это потому, что материализованное представление не было обновлено.

Описание материализованного представления

Вы можете описать материализованное представление, используя команду \d в psql:

\d employee_salaries

Это покажет вам определение материализованного представления и содержащиеся в нём столбцы.

Описание материализованного представления PostgreSQL

Ручная обновление материализованного представления

В этом разделе вы узнаете, как вручную обновить материализованное представление в PostgreSQL. Как упоминалось в предыдущем разделе, материализованные представления не обновляются автоматически при изменении базовых данных. Чтобы отразить последние данные, вам необходимо явно обновить их.

Обновление материализованного представления

Для обновления материализованного представления используется оператор REFRESH MATERIALIZED VIEW. Существует два основных варианта:

REFRESH MATERIALIZED VIEW view_name: Обновляет материализованное представление, повторно выполняя запрос, который его определяет. При этом на материализованное представление накладывается блокировка ACCESS EXCLUSIVE, предотвращающая одновременный доступ.
REFRESH MATERIALIZED VIEW CONCURRENTLY view_name: Обновляет материализованное представление, не блокируя одновременные запросы. Однако для этого требуется, чтобы материализованное представление имело хотя бы один индекс.

Сначала попробуем обновить материализованное представление employee_salaries с помощью стандартной команды REFRESH MATERIALIZED VIEW:

REFRESH MATERIALIZED VIEW employee_salaries;

Теперь снова выполните запрос к материализованному представлению employee_salaries:

SELECT * FROM employee_salaries;

Вы должны увидеть, что средняя зарплата для отдела IT теперь обновлена и отражает нового сотрудника.

Параллельное обновление

Для параллельного обновления материализованного представления нам сначала нужно создать на нем UNIQUE индекс. Это требование для параллельных обновлений, поскольку PostgreSQL необходимо иметь способ уникально идентифицировать строки для выполнения обновления без блокировки всего представления. Столбец department в нашем представлении employee_salaries является уникальным, поскольку наше представление группируется по отделам, поэтому мы можем создать на нем уникальный индекс.

Создадим уникальный индекс по столбцу department:

CREATE UNIQUE INDEX idx_employee_salaries_department ON employee_salaries (department);

Теперь мы можем параллельно обновить материализованное представление:

REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;

Снова выполните запрос к материализованному представлению employee_salaries, чтобы убедиться, что данные по-прежнему актуальны:

SELECT * FROM employee_salaries;

Выбор правильного метода обновления

Используйте REFRESH MATERIALIZED VIEW для простых материализованных представлений или когда вы можете смириться с коротким периодом недоступности.
Используйте REFRESH MATERIALIZED VIEW CONCURRENTLY для больших материализованных представлений или когда вам нужно минимизировать прерывание одновременных запросов. Не забудьте сначала создать индекс для материализованного представления.

Важные замечания

Обновление материализованного представления может быть ресурсоемкой операцией, особенно для больших наборов данных.
Рассмотрите возможность планирования регулярных обновлений с помощью таких инструментов, как cron, чтобы поддерживать актуальность данных в материализованном представлении.

Не забудьте выйти из оболочки psql, набрав \q и нажав Enter.

Резюме

В этом лабораторном практикуме вы изучили, как определить простое представление в PostgreSQL. Вы начали с создания таблицы employees с образцовыми данными, включая столбцы для идентификатора сотрудника, имени, фамилии, отдела и зарплаты. Затем вы определили представление employee_info, которое выбирает только имя, фамилию и отдел из таблицы employees, продемонстрировав, как представления могут упростить запросы и предоставить специфическую перспективу на данные в базовой таблице.

Вы также изучили, как выполнять запросы и изменять данные через представления, а также как создавать и обновлять материализованные представления. Материализованные представления хранят результат запроса как таблицу, улучшая производительность для сложных запросов. Вы исследовали различные методы обновления материализованных представлений, включая одновременное обновление для минимизации прерываний.