PostgreSQL-Ansichten verwalten

Einführung

In diesem Lab untersuchen Sie die Verwaltung von PostgreSQL-Ansichten. Das Hauptziel ist das Verständnis und die Implementierung verschiedener Ansichtsarten, einschließlich einfacher Ansichten und materialisierter Ansichten.

Sie beginnen mit der Definition einer einfachen Ansicht basierend auf einer employees-Tabelle und demonstrieren, wie Sie eine Ansicht erstellen, die bestimmte Spalten auswählt. Anschließend lernen Sie, wie Sie Daten über Ansichten abfragen und gegebenenfalls modifizieren. Schließlich behandelt das Lab die Erstellung und Befüllung von materialisierten Ansichten sowie das manuelle Aktualisieren dieser Ansichten, um sie aktuell zu halten.

Definition einer einfachen Ansicht

In diesem Schritt lernen Sie, wie Sie eine einfache Ansicht in PostgreSQL definieren. Ansichten sind virtuelle Tabellen, die auf dem Ergebnis eines SQL-Statements basieren. Sie sind nützlich, um komplexe Abfragen zu vereinfachen, Abstraktion zu bieten und den Datenzugriff zu steuern.

Verständnis von Ansichten

Eine Ansicht ist im Wesentlichen eine gespeicherte Abfrage. Wenn Sie eine Ansicht abfragen, führt PostgreSQL die zugrunde liegende Abfrage aus und gibt das Ergebnis als wäre es eine echte Tabelle zurück. Ansichten speichern keine Daten selbst; sie bieten eine andere Möglichkeit, auf die in den Basis-Tabellen gespeicherten Daten zuzugreifen.

Erstellung der Tabelle employees

Erstellen wir zunächst eine Tabelle namens employees, mit der wir arbeiten können. Öffnen Sie ein Terminal und stellen Sie eine Verbindung zur PostgreSQL-Datenbank als Benutzer postgres her:

sudo -u postgres psql

Erstellen Sie nun die Tabelle employees:

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

Als Nächstes fügen Sie einige Beispieldaten in die Tabelle employees ein:

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);

Sie können die Daten überprüfen, indem Sie die folgende Abfrage ausführen:

SELECT * FROM employees;

Sie sollten die eingefügten Daten im Ausgabefenster sehen.

Definition der Ansicht employee_info

Nachdem wir nun eine Tabelle mit Daten haben, erstellen wir eine einfache Ansicht. Diese Ansicht zeigt nur den Vornamen, den Nachnamen und die Abteilung jedes Mitarbeiters an. Wir können eine Ansicht namens employee_info mit folgender SQL-Anweisung definieren:

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

Diese Anweisung erstellt eine Ansicht namens employee_info, die die Spalten first_name, last_name und department aus der Tabelle employees auswählt.

Abfragen der Ansicht

Um die Ansicht abzufragen, können Sie eine SELECT-Anweisung verwenden, genau wie bei einer normalen Tabelle:

SELECT * FROM employee_info;

Diese Abfrage gibt den Vornamen, den Nachnamen und die Abteilung aller Mitarbeiter zurück, wie in der Ansicht definiert.

Beschreibung der Ansicht

Sie können die Ansicht mit dem Befehl \d in psql beschreiben:

\d employee_info

Dies zeigt Ihnen die Definition der Ansicht und die enthaltenen Spalten.

Abfragen und Ändern von Daten über Ansichten

In diesem Schritt lernen Sie, wie Sie Daten über Ansichten in PostgreSQL abfragen und ändern können. Während Ansichten hauptsächlich zum Abfragen von Daten verwendet werden, können sie in einigen Fällen auch verwendet werden, um die zugrunde liegenden Daten in den Basis-Tabellen zu ändern.

Abfragen von Daten über Ansichten

Wie im vorherigen Schritt gezeigt, ist die Abfrage von Daten über eine Ansicht unkompliziert. Sie können eine SELECT-Anweisung verwenden, um Daten aus der Ansicht abzurufen, als wäre es eine reguläre Tabelle.

Um beispielsweise alle Daten aus der Ansicht employee_info abzurufen:

SELECT * FROM employee_info;

Sie können auch WHERE-Klauseln und andere SQL-Konstrukte verwenden, um die Daten zu filtern und zu sortieren:

SELECT * FROM employee_info WHERE department = 'Sales';

Diese Abfrage gibt nur die Mitarbeiter in der Abteilung "Sales" zurück.

Ändern von Daten über Ansichten

Die Änderung von Daten über eine Ansicht ist unter bestimmten Bedingungen möglich. Die Ansicht muss einfach genug sein, damit PostgreSQL bestimmen kann, welche Basis-Tabelle und -Spalten aktualisiert werden sollen. Im Allgemeinen ist eine Ansicht modifizierbar, wenn sie die folgenden Kriterien erfüllt:

• Sie wählt nur aus einer Tabelle aus.
• Sie enthält keine Aggregatfunktionen (z. B. SUM, AVG, COUNT).
• Sie enthält keine GROUP BY, HAVING oder DISTINCT-Klauseln.

Erstellen wir eine weitere Ansicht, die die employee_id enthält, um einfachere Aktualisierungen zu ermöglichen:

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

Versuchen wir nun, das Gehalt eines Mitarbeiters über die Ansicht employee_details zu aktualisieren:

UPDATE employee_details
SET salary = 65000.00
WHERE employee_id = 1;

Diese Anweisung aktualisiert das Gehalt des Mitarbeiters mit der employee_id 1 auf 65000,00.

Sie können die Aktualisierung überprüfen, indem Sie die Tabelle employees direkt abfragen:

SELECT * FROM employees WHERE employee_id = 1;

Sie sollten sehen, dass das Gehalt für die employee_id 1 aktualisiert wurde.

Einfügen von Daten über Ansichten

Sie können auch Daten über eine Ansicht einfügen, vorausgesetzt, die Ansicht enthält alle nicht-nullable Spalten der Basis-Tabelle. Da unsere Ansicht employee_details alle Spalten der Tabelle employees enthält, können wir einen neuen Mitarbeiter einfügen:

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

Beachten Sie, dass wir die employee_id nicht angeben, da es sich um eine serielle Spalte handelt und automatisch generiert wird.

Überprüfen Sie die Einfügung:

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

Löschen von Daten über Ansichten

Entsprechend können Sie Daten über eine modifizierbare Ansicht löschen:

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

Überprüfen Sie die Löschung:

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

Wichtige Überlegungen

• Nicht alle Ansichten sind modifizierbar. Komplexe Ansichten mit Joins, Aggregationen oder anderen komplexen Operationen sind in der Regel schreibgeschützt.
• Die Änderung von Daten über Ansichten kann Auswirkungen auf die Leistung haben. PostgreSQL muss die Ansichtsoperationen in Operationen auf den zugrunde liegenden Basis-Tabellen übersetzen.
• Seien Sie vorsichtig beim Ändern von Daten über Ansichten, da Änderungen die Basis-Tabellen direkt betreffen.

Erstellen und Befüllen einer Materialisierten Ansicht

In diesem Schritt lernen Sie, wie Sie eine materialisierte Ansicht in PostgreSQL erstellen und befüllen. Im Gegensatz zu normalen Ansichten speichert eine materialisierte Ansicht das Ergebnis der Abfrage als physische Tabelle. Dies kann die Abfrageleistung, insbesondere bei komplexen Abfragen oder Abfragen, die auf Daten aus externen Quellen zugreifen, deutlich verbessern. Die Daten in einer materialisierten Ansicht werden jedoch nicht automatisch aktualisiert, wenn sich die zugrunde liegenden Daten ändern. Sie müssen sie manuell aktualisieren oder eine regelmäßige Aktualisierung planen.

Erstellen einer Materialisierten Ansicht

Um eine materialisierte Ansicht zu erstellen, verwenden Sie die Anweisung CREATE MATERIALIZED VIEW. Erstellen wir eine materialisierte Ansicht namens employee_salaries, die den Durchschnittslohn für jede Abteilung anzeigt.

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

Diese Anweisung erstellt eine materialisierte Ansicht namens employee_salaries, die den Durchschnittslohn für jede Abteilung basierend auf den Daten in der Tabelle employees berechnet.

Abfragen der Materialisierten Ansicht

Sie können eine materialisierte Ansicht wie eine reguläre Tabelle abfragen:

SELECT * FROM employee_salaries;

Dies gibt die Abteilung und den Durchschnittslohn für jede Abteilung zurück, basierend auf den Daten, die in der Tabelle employees vorhanden waren, als die materialisierte Ansicht erstellt wurde.

Befüllen der Materialisierten Ansicht

Wenn Sie eine materialisierte Ansicht erstellen, wird sie automatisch mit den ursprünglichen Daten gefüllt. Wenn sich jedoch die zugrunde liegenden Daten in der Tabelle employees ändern, werden die Daten in der materialisierten Ansicht employee_salaries nicht automatisch aktualisiert.

Fügen wir einen neuen Mitarbeiter in die Tabelle employees ein:

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

Wenn Sie nun die materialisierte Ansicht employee_salaries erneut abfragen:

SELECT * FROM employee_salaries;

Sie werden feststellen, dass sich der Durchschnittslohn für die Abteilung IT nicht geändert hat, um den neuen Mitarbeiter widerzuspiegeln. Dies liegt daran, dass die materialisierte Ansicht nicht aktualisiert wurde.

Beschreibung der Materialisierten Ansicht

Sie können die materialisierte Ansicht mit dem Befehl \d in psql beschreiben:

\d employee_salaries

Dies zeigt Ihnen die Definition der materialisierten Ansicht und die enthaltenen Spalten.

Manuelle Aktualisierung einer Materialisierten Ansicht

In diesem Schritt erfahren Sie, wie Sie eine materialisierte Ansicht in PostgreSQL manuell aktualisieren. Wie im vorherigen Schritt erwähnt, werden materialisierte Ansichten nicht automatisch aktualisiert, wenn sich die zugrunde liegenden Daten ändern. Um die neuesten Daten widerzuspiegeln, müssen Sie sie explizit aktualisieren.

Aktualisierung der Materialisierten Ansicht

Um eine materialisierte Ansicht zu aktualisieren, verwenden Sie die Anweisung REFRESH MATERIALIZED VIEW. Es gibt zwei Hauptmöglichkeiten:

• REFRESH MATERIALIZED VIEW view_name: Dies aktualisiert die materialisierte Ansicht, indem die Abfrage, die sie definiert, erneut ausgeführt wird. Sie erwirbt einen ACCESS EXCLUSIVE-Sperre auf der materialisierten Ansicht, wodurch der gleichzeitige Zugriff verhindert wird.

• REFRESH MATERIALIZED VIEW CONCURRENTLY view_name: Dies aktualisiert die materialisierte Ansicht ohne Blockierung gleichzeitiger Abfragen. Allerdings ist dafür erforderlich, dass die materialisierte Ansicht mindestens einen Index besitzt.

Versuchen wir zunächst, die materialisierte Ansicht employee_salaries mit dem Standardbefehl REFRESH MATERIALIZED VIEW zu aktualisieren:

REFRESH MATERIALIZED VIEW employee_salaries;

Fragen Sie nun die materialisierte Ansicht employee_salaries erneut ab:

SELECT * FROM employee_salaries;

Sie sollten sehen, dass der Durchschnittslohn für die Abteilung IT nun aktualisiert wurde, um den neuen Mitarbeiter widerzuspiegeln.

Gleichzeitige Aktualisierung

Um die materialisierte Ansicht gleichzeitig zu aktualisieren, müssen wir zuerst einen Index darauf erstellen. Erstellen wir einen Index auf die Spalte department:

CREATE INDEX idx_employee_salaries_department ON employee_salaries (department);

Jetzt können wir die materialisierte Ansicht gleichzeitig aktualisieren:

REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;

Fragen Sie die materialisierte Ansicht employee_salaries erneut ab, um zu bestätigen, dass die Daten noch aktuell sind:

SELECT * FROM employee_salaries;

Auswahl der richtigen Aktualisierungsmethode

• Verwenden Sie REFRESH MATERIALIZED VIEW für einfache materialisierte Ansichten oder wenn Sie eine kurze Zeit der Nichtverfügbarkeit tolerieren können.
• Verwenden Sie REFRESH MATERIALIZED VIEW CONCURRENTLY für größere materialisierte Ansichten oder wenn Sie die Unterbrechung gleichzeitiger Abfragen minimieren müssen. Denken Sie daran, zuerst einen Index auf die materialisierte Ansicht zu erstellen.

Wichtige Überlegungen

• Die Aktualisierung einer materialisierten Ansicht kann eine ressourcenintensive Operation sein, insbesondere bei großen Datensätzen.
• Erwägen Sie die Planung regelmäßiger Aktualisierungen mit einem Tool wie cron, um die Daten in der materialisierten Ansicht aktuell zu halten.

Denken Sie daran, die psql-Shell mit \q und der Eingabetaste zu verlassen.

Zusammenfassung

In diesem Labor haben Sie gelernt, wie Sie eine einfache Ansicht in PostgreSQL definieren. Sie haben mit der Erstellung einer Tabelle employees mit Beispieldaten begonnen, einschließlich Spalten für Mitarbeiter-ID, Vorname, Nachname, Abteilung und Gehalt. Anschließend haben Sie eine Ansicht namens employee_info definiert, die nur Vorname, Nachname und Abteilung aus der Tabelle employees auswählt, um zu demonstrieren, wie Ansichten Abfragen vereinfachen und einen spezifischen Blick auf die zugrunde liegenden Daten bieten können.

Sie haben auch gelernt, wie Sie Daten über Ansichten abfragen und ändern können, sowie wie Sie materialisierte Ansichten erstellen und aktualisieren. Materialisierte Ansichten speichern das Ergebnis einer Abfrage als Tabelle und verbessern die Leistung bei komplexen Abfragen. Sie haben verschiedene Methoden zur Aktualisierung materialisierter Ansichten erforscht, einschließlich der gleichzeitigen Aktualisierung, um Störungen zu minimieren.