PostgreSQL View-Verwaltung

Einführung

In diesem Lab werden Sie die PostgreSQL View-Verwaltung (PostgreSQL Views Management) erkunden. Das Hauptziel ist es, verschiedene Arten von Views zu verstehen und zu implementieren, einschließlich einfacher Views (simple views) und materialisierter Views (materialized views).

Sie beginnen mit der Definition einer einfachen View basierend auf einer employees-Tabelle und demonstrieren, wie man eine View erstellt, die bestimmte Spalten auswählt. Anschließend lernen Sie, wie Sie Daten über Views abfragen und möglicherweise ändern können. Abschließend behandelt das Lab die Erstellung und das Befüllen von materialisierten Views sowie die manuelle Aktualisierung dieser Views, um sie auf dem neuesten Stand zu halten.

Skills Graph

Eine einfache View definieren

In diesem Schritt lernen Sie, wie Sie eine einfache View (simple view) in PostgreSQL definieren. Views sind virtuelle Tabellen, die auf dem Result-Set einer SQL-Anweisung basieren. Sie sind nützlich, um komplexe Abfragen zu vereinfachen, Abstraktion zu ermöglichen und den Datenzugriff zu steuern.

Views verstehen

Eine View ist im Wesentlichen eine gespeicherte Abfrage. Wenn Sie eine View abfragen, führt PostgreSQL die zugrunde liegende Abfrage aus und gibt das Result-Set so zurück, als wäre es eine echte Tabelle. Views speichern selbst keine Daten; sie bieten eine andere Möglichkeit, auf die in den Basistabellen (base tables) gespeicherten Daten zuzugreifen.

Die Tabelle employees erstellen

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

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

Fügen Sie als Nächstes 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 in der Ausgabe sehen.

Die View employee_info definieren

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

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

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

Die View abfragen

Um die View abzufragen, können Sie eine SELECT-Anweisung verwenden, genau wie bei einer regulären Tabelle:

SELECT * FROM employee_info;

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

Die View beschreiben

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

\d employee_info

Dies zeigt Ihnen die View-Definition und die darin enthaltenen Spalten.

Daten über Views abfragen und ändern

In diesem Schritt lernen Sie, wie Sie Daten über Views (Views) in PostgreSQL abfragen und ändern. Während Views 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 Basistabellen (base tables) zu ändern.

Daten über Views abfragen

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

Um beispielsweise alle Daten aus der View 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 Vertriebsabteilung (Sales department) zurück.

Daten über Views ändern

Das Ändern von Daten über eine View ist unter bestimmten Bedingungen möglich. Die View muss einfach genug sein, damit PostgreSQL bestimmen kann, welche Basistabelle und Spalten aktualisiert werden sollen. Im Allgemeinen ist eine View änderbar, wenn sie die folgenden Kriterien erfüllt:

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

Erstellen wir eine weitere View, 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 View employee_details zu aktualisieren:

UPDATE employee_details
SET salary = 65000.00
WHERE employee_id = 1;

Diese Anweisung aktualisiert das Gehalt des Mitarbeiters mit 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 employee_id 1 aktualisiert wurde.

Daten über Views einfügen

Sie können auch Daten über eine View einfügen, vorausgesetzt, die View enthält alle Nicht-Null-Spalten (non-nullable columns) der Basistabelle. Da unsere View 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 Serial-Spalte handelt und sie automatisch generiert wird.

Überprüfen Sie die Einfügung:

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

Daten über Views löschen

Ebenso können Sie Daten über eine änderbare View 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 Views sind änderbar. Komplexe Views mit Joins, Aggregationen oder anderen komplexen Operationen sind in der Regel schreibgeschützt (read-only).
• Das Ändern von Daten über Views kann Auswirkungen auf die Leistung haben. PostgreSQL muss die View-Operationen in Operationen auf den zugrunde liegenden Basistabellen übersetzen.
• Seien Sie vorsichtig beim Ändern von Daten über Views, da Änderungen die Basistabellen direkt beeinflussen.

Eine materialisierte View erstellen und befüllen

In diesem Schritt lernen Sie, wie Sie eine materialisierte View (materialized view) in PostgreSQL erstellen und befüllen. Im Gegensatz zu regulären Views speichern materialisierte Views das Result-Set der Abfrage als physische Tabelle. Dies kann die Abfrageleistung erheblich verbessern, insbesondere bei komplexen Abfragen oder Abfragen, die auf Daten aus Remote-Quellen zugreifen. Die Daten in einer materialisierten View werden jedoch nicht automatisch aktualisiert, wenn sich die zugrunde liegenden Daten ändern. Sie müssen sie manuell aktualisieren oder die Aktualisierung periodisch planen.

Eine materialisierte View erstellen

Um eine materialisierte View zu erstellen, verwenden Sie die Anweisung CREATE MATERIALIZED VIEW. Erstellen wir eine materialisierte View namens employee_salaries, die das durchschnittliche Gehalt 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 View namens employee_salaries, die das durchschnittliche Gehalt für jede Abteilung basierend auf den Daten in der Tabelle employees berechnet.

Die materialisierte View abfragen

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

SELECT * FROM employee_salaries;

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

Die materialisierte View befüllen

Wenn Sie eine materialisierte View erstellen, wird sie automatisch mit den anfänglichen Daten befüllt. Wenn sich jedoch die zugrunde liegenden Daten in der Tabelle employees ändern, werden die Daten in der materialisierten View 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 View employee_salaries erneut abfragen:

SELECT * FROM employee_salaries;

Sie werden feststellen, dass sich das durchschnittliche Gehalt für die IT-Abteilung nicht geändert hat, um den neuen Mitarbeiter widerzuspiegeln. Dies liegt daran, dass die materialisierte View nicht aktualisiert wurde.

Die materialisierte View beschreiben

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

\d employee_salaries

Dies zeigt Ihnen die Definition der materialisierten View und die darin enthaltenen Spalten.

Eine materialisierte View manuell aktualisieren

In diesem Schritt lernen Sie, wie Sie eine materialisierte View (materialized view) in PostgreSQL manuell aktualisieren. Wie im vorherigen Schritt erwähnt, werden materialisierte Views nicht automatisch aktualisiert, wenn sich die zugrunde liegenden Daten ändern. Um die neuesten Daten widerzuspiegeln, müssen Sie sie explizit aktualisieren.

Die materialisierte View aktualisieren

Um eine materialisierte View zu aktualisieren, verwenden Sie die Anweisung REFRESH MATERIALIZED VIEW. Es gibt zwei Hauptoptionen:

• REFRESH MATERIALIZED VIEW view_name: Dies aktualisiert die materialisierte View, indem die Abfrage, die sie definiert, erneut ausgeführt wird. Sie erwirbt eine ACCESS EXCLUSIVE-Sperre (lock) für die materialisierte View und verhindert so den gleichzeitigen Zugriff (concurrent access).

• REFRESH MATERIALIZED VIEW CONCURRENTLY view_name: Dies aktualisiert die materialisierte View, ohne gleichzeitige Abfragen zu blockieren. Dies erfordert jedoch, dass die materialisierte View mindestens einen Index hat.

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

REFRESH MATERIALIZED VIEW employee_salaries;

Fragen Sie nun die materialisierte View employee_salaries erneut ab:

SELECT * FROM employee_salaries;

Sie sollten sehen, dass das durchschnittliche Gehalt für die IT-Abteilung jetzt aktualisiert wurde, um den neuen Mitarbeiter widerzuspiegeln.

Gleichzeitig aktualisieren (Refreshing Concurrently)

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

CREATE INDEX idx_employee_salaries_department ON employee_salaries (department);

Jetzt können wir die materialisierte View gleichzeitig aktualisieren:

REFRESH MATERIALIZED VIEW CONCURRENTLY employee_salaries;

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

SELECT * FROM employee_salaries;

Die richtige Aktualisierungsmethode wählen

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

Wichtige Überlegungen

• Das Aktualisieren einer materialisierten View kann eine ressourcenintensive Operation sein, insbesondere bei großen Datensätzen.
• Erwägen Sie, regelmäßige Aktualisierungen mit einem Tool wie cron zu planen, um die Daten in der materialisierten View auf dem neuesten Stand zu halten.

Denken Sie daran, die psql-Shell mit der Eingabe von \q und Drücken der Eingabetaste zu verlassen.

Zusammenfassung

In diesem Lab haben Sie gelernt, wie Sie eine einfache View (Ansicht) in PostgreSQL definieren. Sie begannen mit der Erstellung einer employees-Tabelle mit Beispieldaten, einschließlich Spalten für Mitarbeiter-ID, Vorname, Nachname, Abteilung und Gehalt. Anschließend definierten Sie eine View namens employee_info, die nur den Vornamen, Nachnamen und die Abteilung aus der employees-Tabelle auswählt. Dies demonstriert, wie Views Abfragen vereinfachen und eine bestimmte Perspektive auf die zugrunde liegenden Daten bieten können.

Sie haben auch gelernt, wie Sie Daten über Views abfragen und ändern sowie wie Sie materialisierte Views (materialized views) erstellen und aktualisieren. Materialisierte Views speichern das Ergebnis einer Abfrage als Tabelle, wodurch die Leistung bei komplexen Abfragen verbessert wird. Sie haben verschiedene Methoden zum Aktualisieren materialisierter Views untersucht, einschließlich der gleichzeitigen Aktualisierung (concurrent refreshing), um Unterbrechungen zu minimieren.