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

Введение

В этой лабораторной работе вы изучите управление представлениями (Views) в PostgreSQL. Основная цель - понять и реализовать различные типы представлений, включая простые представления (simple views) и материализованные представления (materialized views).

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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/rows_add("Insert Multiple Rows") postgresql/PostgreSQLGroup -.-> postgresql/row_edit("Update Single Row") postgresql/PostgreSQLGroup -.-> postgresql/row_drop("Delete One Row") postgresql/PostgreSQLGroup -.-> postgresql/view_init("Create Basic View") postgresql/PostgreSQLGroup -.-> postgresql/view_drop("Drop Existing View") postgresql/PostgreSQLGroup -.-> postgresql/func_call("Call Stored Function") subgraph Lab Skills postgresql/rows_add -.-> lab-550966{{"Управление представлениями PostgreSQL"}} postgresql/row_edit -.-> lab-550966{{"Управление представлениями PostgreSQL"}} postgresql/row_drop -.-> lab-550966{{"Управление представлениями PostgreSQL"}} postgresql/view_init -.-> lab-550966{{"Управление представлениями PostgreSQL"}} postgresql/view_drop -.-> lab-550966{{"Управление представлениями PostgreSQL"}} postgresql/func_call -.-> lab-550966{{"Управление представлениями PostgreSQL"}} end

Определение простого представления (Simple View)

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

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

Представление (view) - это, по сути, сохраненный запрос. Когда вы запрашиваете представление, PostgreSQL выполняет базовый запрос и возвращает результирующий набор (result set), как если бы это была реальная таблица. Представления не хранят данные сами по себе; они предоставляют другой способ доступа к данным, хранящимся в базовых таблицах.

Создание таблицы 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

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

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

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

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

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

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

SELECT * FROM employee_info;

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

SELECT * FROM employee_info WHERE department = 'Sales';

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

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

Изменение данных через представление возможно при определенных условиях. Представление должно быть достаточно простым (simple), чтобы 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';

Важные соображения

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

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

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

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

Чтобы создать материализованное представление, вы используете оператор 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

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

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

В этом шаге вы узнаете, как вручную обновить материализованное представление (materialized view) в 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-отдела теперь обновлена, чтобы отразить нового сотрудника.

Обновление параллельно (Concurrently)

Чтобы обновить материализованное представление параллельно, нам сначала нужно создать индекс на нем. Давайте создадим индекс по столбцу department:

CREATE 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.

Итог

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

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