Gestion des vues PostgreSQL

Introduction

Dans ce laboratoire, vous explorerez la gestion des vues PostgreSQL. L'objectif principal est de comprendre et de mettre en œuvre différents types de vues, y compris les vues simples et les vues matérialisées.

Vous commencerez par définir une vue simple basée sur une table employees, démontrant comment créer une vue qui sélectionne des colonnes spécifiques. Vous apprendrez ensuite comment interroger et potentiellement modifier des données via des vues. Enfin, le laboratoire couvre la création et la population de vues matérialisées, ainsi que le rafraîchissement manuel de ces vues pour les maintenir à jour.

Définir une vue simple

Dans cette étape, vous apprendrez à définir une vue simple dans PostgreSQL. Les vues sont des tables virtuelles basées sur l'ensemble de résultats d'une instruction SQL. Elles sont utiles pour simplifier les requêtes complexes, fournir une abstraction et contrôler l'accès aux données.

Comprendre les vues

Une vue est essentiellement une requête stockée. Lorsque vous interrogez une vue, PostgreSQL exécute la requête sous-jacente et retourne l'ensemble de résultats comme s'il s'agissait d'une table réelle. Les vues ne stockent pas les données elles-mêmes ; elles offrent un moyen différent d'accéder aux données stockées dans les tables de base.

Créer la table employees

Tout d'abord, créons une table nommée employees pour travailler. Ouvrez un terminal et connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres :

sudo -u postgres psql

Maintenant, créez la table employees :

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

Ensuite, insérez des données d'exemple dans la table 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);

Vous pouvez vérifier les données en exécutant la requête suivante :

SELECT * FROM employees;

Vous devriez voir les données insérées dans la sortie.

Définir la vue employee_info

Maintenant que nous avons une table avec des données, créons une vue simple. Cette vue affichera uniquement le prénom, le nom de famille et le service de chaque employé. Nous pouvons définir une vue nommée employee_info à l'aide de la déclaration SQL suivante :

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

Cette instruction crée une vue nommée employee_info qui sélectionne les colonnes first_name, last_name et department de la table employees.

Interroger la vue

Pour interroger la vue, vous pouvez utiliser une instruction SELECT comme vous le feriez avec une table régulière :

SELECT * FROM employee_info;

Cette requête retournera le prénom, le nom de famille et le service de tous les employés, comme défini dans la vue.

Décrire la vue

Vous pouvez décrire la vue à l'aide de la commande \d dans psql :

\d employee_info

Cela vous affichera la définition de la vue et les colonnes qu'elle contient.

Interroger et modifier les données via les vues

Dans cette étape, vous apprendrez à interroger et à modifier les données via les vues dans PostgreSQL. Bien que les vues soient principalement utilisées pour interroger les données, dans certains cas, elles peuvent également être utilisées pour modifier les données sous-jacentes dans les tables de base.

Interroger les données via les vues

Comme démontré dans l'étape précédente, interroger les données via une vue est simple. Vous pouvez utiliser une instruction SELECT pour récupérer des données de la vue comme si c'était une table régulière.

Par exemple, pour récupérer toutes les données de la vue employee_info :

SELECT * FROM employee_info;

Vous pouvez également utiliser des clauses WHERE et d'autres constructions SQL pour filtrer et trier les données :

SELECT * FROM employee_info WHERE department = 'Sales';

Cette requête ne retournera que les employés du service Ventes.

Modifier les données via les vues

La modification des données via une vue est possible sous certaines conditions. La vue doit être suffisamment simple pour que PostgreSQL puisse déterminer la table de base et les colonnes à mettre à jour. Généralement, une vue est modifiable si elle répond aux critères suivants :

• Elle sélectionne une seule table.
• Elle ne contient pas de fonctions d'agrégation (par exemple, SUM, AVG, COUNT).
• Elle ne contient pas de clauses GROUP BY, HAVING ou DISTINCT.

Créons une autre vue qui inclut l'identifiant de l'employé (employee_id) pour permettre des mises à jour plus faciles :

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

Essayons maintenant de mettre à jour le salaire d'un employé via la vue employee_details :

UPDATE employee_details
SET salary = 65000.00
WHERE employee_id = 1;

Cette instruction met à jour le salaire de l'employé avec l'identifiant employee_id 1 à 65000,00.

Vous pouvez vérifier la mise à jour en interrogeant directement la table employees :

SELECT * FROM employees WHERE employee_id = 1;

Vous devriez constater que le salaire pour l'identifiant employee_id 1 a été mis à jour.

Insérer des données via les vues

Vous pouvez également insérer des données via une vue, à condition que la vue inclut toutes les colonnes non nulles de la table de base. Puisque notre vue employee_details inclut toutes les colonnes de la table employees, nous pouvons insérer un nouvel employé :

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

Notez que nous ne spécifions pas l'employee_id car il s'agit d'une colonne séquentielle et sera automatiquement générée.

Vérifiez l'insertion :

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

Supprimer des données via les vues

De même, vous pouvez supprimer des données via une vue modifiable :

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

Vérifiez la suppression :

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

Considérations importantes

• Toutes les vues ne sont pas modifiables. Les vues complexes avec des jointures, des agrégations ou d'autres opérations complexes sont généralement en lecture seule.
• La modification des données via les vues peut avoir des implications sur les performances. PostgreSQL doit traduire les opérations de vue en opérations sur les tables de base sous-jacentes.
• Faites attention lors de la modification des données via les vues, car les modifications affecteront directement les tables de base.

Créer et remplir une vue matérialisée

Dans cette étape, vous apprendrez à créer et à remplir une vue matérialisée dans PostgreSQL. Contrairement aux vues régulières, les vues matérialisées stockent l'ensemble de résultats de la requête sous forme de table physique. Cela peut améliorer significativement les performances des requêtes, en particulier pour les requêtes complexes ou celles qui accèdent à des données provenant de sources distantes. Cependant, les données d'une vue matérialisée ne sont pas automatiquement mises à jour lorsque les données sous-jacentes changent. Vous devez la rafraîchir manuellement ou la programmer pour qu'elle soit rafraîchie périodiquement.

Créer une vue matérialisée

Pour créer une vue matérialisée, vous utilisez l'instruction CREATE MATERIALIZED VIEW. Créons une vue matérialisée nommée employee_salaries qui affiche le salaire moyen par service.

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

Cette instruction crée une vue matérialisée nommée employee_salaries qui calcule le salaire moyen par service basé sur les données de la table employees.

Interroger la vue matérialisée

Vous pouvez interroger une vue matérialisée comme une table régulière :

SELECT * FROM employee_salaries;

Cela retournera le service et le salaire moyen pour chaque service, basé sur les données présentes dans la table employees au moment de la création de la vue matérialisée.

Remplir la vue matérialisée

Lors de la création d'une vue matérialisée, elle est automatiquement remplie avec les données initiales. Cependant, si les données sous-jacentes dans la table employees changent, les données de la vue matérialisée employee_salaries ne seront pas automatiquement mises à jour.

Insérons un nouvel employé dans la table employees :

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

Maintenant, si vous interrogez à nouveau la vue matérialisée employee_salaries :

SELECT * FROM employee_salaries;

Vous remarquerez que le salaire moyen du service informatique n'a pas changé pour refléter le nouvel employé. Ceci est dû au fait que la vue matérialisée n'a pas été rafraîchie.

Décrire la vue matérialisée

Vous pouvez décrire la vue matérialisée à l'aide de la commande \d dans psql :

\d employee_salaries

Cela vous affichera la définition de la vue matérialisée et les colonnes qu'elle contient.

Rafraîchir manuellement une vue matérialisée

Dans cette étape, vous apprendrez comment rafraîchir manuellement une vue matérialisée dans PostgreSQL. Comme mentionné dans l'étape précédente, les vues matérialisées ne se mettent pas automatiquement à jour lorsque les données sous-jacentes changent. Pour refléter les données les plus récentes, vous devez les rafraîchir explicitement.

Rafraîchir la Vue Matérialisée

Pour rafraîchir une vue matérialisée, vous utilisez l'instruction REFRESH MATERIALIZED VIEW. Il existe deux options principales :

• REFRESH MATERIALIZED VIEW nom_vue: Ceci rafraîchira la vue matérialisée en réexécutant la requête qui la définit. Elle acquiert un verrou ACCESS EXCLUSIVE sur la vue matérialisée, empêchant l'accès concurrent.

• REFRESH MATERIALIZED VIEW CONCURRENTLY nom_vue: Ceci rafraîchira la vue matérialisée sans bloquer les requêtes concurrentes. Cependant, cela nécessite que la vue matérialisée ait au moins un index.

Essayons d'abord de rafraîchir la vue matérialisée employee_salaries en utilisant la commande standard REFRESH MATERIALIZED VIEW :

REFRESH MATERIALIZED VIEW employee_salaries;

Maintenant, interrogez à nouveau la vue matérialisée employee_salaries :

SELECT * FROM employee_salaries;

Vous devriez constater que le salaire moyen du département informatique a maintenant été mis à jour pour refléter le nouvel employé.

Rafraîchir Concurremment

Pour rafraîchir la vue matérialisée de manière concurrente, nous devons d'abord y créer un index UNIQUE. C'est une exigence pour les rafraîchissements concurrents, car PostgreSQL a besoin d'un moyen d'identifier de manière unique les lignes pour effectuer le rafraîchissement sans verrouiller la vue entière. La colonne department dans notre vue employee_salaries est unique car notre vue regroupe par département, nous pouvons donc y créer un index unique.

Créons un index unique sur la colonne department :

CREATE UNIQUE INDEX idx_employee_salaries_department ON employee_salaries (department);

Maintenant, nous pouvons rafraîchir la vue matérialisée de manière concurrente :

REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;

Interrogez à nouveau la vue matérialisée employee_salaries pour confirmer que les données sont toujours à jour :

SELECT * FROM employee_salaries;

Choisir la Bonne Méthode de Rafraîchissement

• Utilisez REFRESH MATERIALIZED VIEW pour les vues matérialisées simples ou lorsque vous pouvez tolérer une courte période d'indisponibilité.
• Utilisez REFRESH MATERIALIZED VIEW CONCURRENTLY pour les vues matérialisées plus volumineuses ou lorsque vous devez minimiser les perturbations des requêtes concurrentes. N'oubliez pas de créer un index sur la vue matérialisée au préalable.

Considérations Importantes

• Le rafraîchissement d'une vue matérialisée peut être une opération gourmande en ressources, en particulier pour les grands ensembles de données.
• Envisagez de planifier des rafraîchissements réguliers à l'aide d'un outil comme cron pour maintenir à jour les données de la vue matérialisée.

N'oubliez pas de quitter le shell psql en tapant \q et en appuyant sur Entrée.

Résumé

Dans ce laboratoire, vous avez appris à définir une vue simple dans PostgreSQL. Vous avez commencé par créer une table employees avec des données d'exemple, incluant des colonnes pour l'ID de l'employé, le prénom, le nom, le service et le salaire. Vous avez ensuite défini une vue nommée employee_info qui sélectionne uniquement le prénom, le nom et le service de la table employees, démontrant comment les vues peuvent simplifier les requêtes et fournir une perspective spécifique sur les données sous-jacentes.

Vous avez également appris à interroger et à modifier les données via les vues, ainsi qu'à créer et rafraîchir les vues matérialisées. Les vues matérialisées stockent le résultat d'une requête sous forme de table, améliorant les performances pour les requêtes complexes. Vous avez exploré différentes méthodes pour rafraîchir les vues matérialisées, y compris le rafraîchissement concurrent pour minimiser les perturbations.