Umgang mit dem Problem der nicht änderbaren Spalten wegen Verwendung in Views

Anwendungen & Erweiterungen
1

Hallo zusammen,

wir sind in unserer Datenbank jetzt erstmalig über das Problem gestolpert, dass sich Tabellen, welche in Views verwendet werden, nicht ändern lassen. Jetzt überlege ich, wie ich damit am besten umgehe. Die Views, um die es geht, werden von uns oder auch von Kunden angelegt, um Daten lesbar bereit zu stellen. Das heißt, ich habe keinen Einfluss auf diese Views und weiß auch nicht, welche Tabellen involviert sind.

Wie kann man das elegant lösen?

  • Alle Views pauschal löschen und neu erstellen? (Ignorieren wir mal für den Moment das Problem der geschachtelten Views). Dann müsste ich mir vorher bei jeder View das “CREATE”-Skript merken, ja?
  • Könnte man die Views in einer anderen Datenbank ablegen und die benötigten Tabellen dort hineinverknüpfen? Sowas habe ich früher mit Access gemacht, da gab es eine DB für die Tabellen und eine andere für Abfragen oder Code und die Tabellen waren nur verknüpft. Aber ist das noch zeitgemäß? Access ist ja auch oft nicht vorhanden…
  • Irgendeine andere elegante Idee?

Gruß, Karsten.

2

Wir erstellen alle Views über ein Schema-Migrationstool (Liquibase). Somit müssen wir uns die Skripte nicht “merken” da sie Teil des Sourcecode (git) sind.

Und ja, dort machen wir genau das: am Anfang einfach pauschal alle Views droppen und nachdem alle Tabellenänderungen durch sind, alle wieder anlegen (in der richtigen Reihenfolge).

Ein größeres Problem sind allerdings Materialized Views, da die zum Anlegen unter Umständen sehr lange dauern.

Wenn Du natürlich keine Kontrolle über alle Views hast, dann funktioniert dieser Ansatz aber leider nicht so gut. Da wirst Du wohl nicht um ein “alle merken Skript” rumkommen.

Abfragen über Datenbankgrenzen hinweg sind in Postgres eher umständlich (und auch langsamer als alles in einer DB zu halten)

3

Wenn die Views nicht unter eurer Kontrolle sind, würde ich die Viewdefinitionen exportieren, dann alle Views löschen, die Änderungen durchführen und die Views wieder erzeugen.

So kann man sich einen Export aller Viewdefinitionen erstellen:

pg_dump --format=custom --schema-only --file=dumpfile datenbankname

pg_restore --list dumpfile | grep -e '[0-9]\+; [0-9]\+ [0-9]\+ VIEW' > viewlist

pg_restore --clean --if-exists --use-list=viewlist --file=views.sql dumpfile

Dann hat man in views.sql einen Export aller Views.

Die Instruktionen sind für Unix; auf Betriebssystemen ohne grep muß man eben irgendwie anders filtern.

Die „störenden” Views kann man eingach mit DROP VIEW ... CASCADE löschen.

Nach der Änderung spielt man views.sql ein, das löscht alle Views und erzeugt sie neu.

4

Ah, das sieht mir nach einem vielversprechenden Ansatz aus. Die Kommandos führen wir unter Windows aus, aus einer .NET-Anwendung heraus, aber das wird sich sicher anpassen lassen.

Danke schön, Gruß, Karsten.

5

Rein theoretisch könnte man das auch über SQL automatisieren da die Definition der VIEWs ja ausgelesen werden kann (und dann z.b. in einer Tabelle gesichert).

Wenn man richtig viel Zeit investiert, könnte man auch die richtige Reihenfolge ermitteln (über pg_depend) - aber letztendlich macht pg_dump das ja auch. Die Mühe lohnt sich vermutlich nicht, wenn man das über die Kommandozeile auch hinbekommt.

6

Ja, es geht aber auch um die Zugriffsrechte, die sollen natürlich hinterher wieder das sein, ich denke, da ist das “dump/restore”-Pärchen besser aufgestellt…

7

Es ist wirklich schade, dass man weder bei pg_dump noch bei pg_restore einen Filter basierend auf dem Typ der Objekte definieren kann. Cool wäre etwas in der Art wie pg_dump --object-types=view,function,procedure ... oder pg_restore --object-types=view,function, dann müsste man sich nicht auf ein grep mit regex verlassen.

8

höre ich da ein Feature-Request? :wink:

9

Na, wenn schon ein neues Feature, dann würde ich mir wünschen, dass man über eine Option o.ä. dem Server mitteilen kann, dass er die Änderung von Tabellen trotz vorhandener Views zulässt. Eventuell kann das System ja die geänderten Spalten beim nächsten Aufruf der View erkennen. Dem MS-SQL ist das ja auch völlig egal. Das wäre natürlich eine bequeme Sache.

So richtig klar ist mir auch nicht, warum ich eine View entfernen muss, nur weil eine Spalte zum Beispiel von 100 auf 250 Stellen erweitert wird.

10

Ja, Postgres arbeitet im Grunde wieh SQL Server wenn man with schema binding angibt. Leider kann man das nicht abstellen.

Ich denke das zu Grunde liegende Problem ist, dass Postgres die Abhängigkeit nur auf Objektebene speichert (“View X ist von Tabelle Y abhängig”) aber nicht auf Spaltenebene. Wenn also in Tabelle Y was geändert wird, “weiß” Postgres nicht, ob die Änderung eine Auswirkung hat oder nicht und lehnt das sicherheitshalber ab.

In Oracle ist das so gelöst, dass die Views auf “unusable” gesetzt werden, und man diese dann explizit wieder “kompilieren” muss (im Grunde wird wohl das eigentliche SELECT nochmal geprüft). Wenn das klappt ist alles wieder in Ordnung, wenn nicht muss die View neu angelegt werden.

Nach meinem sehr laienhaften Verständnis der Postgres Architektur wäre das in Postgres vermutlich einfacher zu realisieren als die Abhängigkeiten der Spalten zu registrieren. Naiv könnte ich mir das so vorstellen, dass Postgres zusätzlich zum geparsten Statement auch das original Statement speichert. Wenn eine Tabelle geändert wird, wird das geparste Statement gelöscht (damit wird die View “unusable”) und eine “compile” würde es wieder erzeugen wenn das noch möglich. Falls nicht, muss die View neu angelegt werden.

11

So, ich habe mit jetzt in die Lösung eingefuchst. Ich kann jetzt mit den obigen Befehlen eine Datei meiner Views erzeugen. Jetzt muss ich diese Views noch alle löschen und kann sie dann anschließend wiederherstellen lassen. Ziemlich trickreich, das Ganze :slightly_smiling_face: , vor allem, wenn man diese Befehle per Code ausführen lassen will (C#). Da muss man erst den Befehl in eine Batchdatei speichern und kann diese dann ausführen, sonst klappt das mit dem “|” nicht ( too many arguments kommt dann).

Für Windows geht statt “grep” übrigens “findstr”:

findstr /R "[0-9]+; [0-9]+ [0-9]+ VIEW"

Vielen Dank für die wie immer professionelle und schnelle Hilfe…

12

PostgreSQL speichert die Abhängigkeiten auf Spaltenebene:

test=> CREATE TABLE t (a integer, b integer);
CREATE TABLE
test=> CREATE VIEW v AS SELECT a FROM t;
CREATE VIEW
test=> ALTER TABLE t DROP a;
ERROR:  cannot drop column a of table t because other objects depend on it
DETAIL:  view v depends on column a of table t
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
test=> ALTER TABLE t DROP b;
ALTER TABLE
13

Ah, danke.

Aber dann ist es ein bisschen verwunderlich, warum man nicht zumindest Spalten zu der Tabelle hinzufügen kann.

14

Man kann ohne Probleme Spalten zu der zugrundeliegenden Tabelle hinzufügen:

test=> CREATE TABLE t (a integer, b integer);
CREATE TABLE
test=> CREATE VIEW v AS SELECT a FROM t;
CREATE VIEW
test=> ALTER TABLE t ADD c boolean;
ALTER TABLE
15

Danke. Hat sich das mal geändert? Irgendwie hatte ich abgespeichert, dass auch das nicht ging…

16

Hallo, ich muss nochmal was fragen, habe irgendwie ein Brett vorm Kopf:

Ich habe ja jetzt mein wunderschönes Scriptfile, welches alle Abfragen löscht und wiederherstellt. Wie rufe ich denn das jetzt auf? Per C# und “IdbCommand” gehen ja nur einzelne Komandos…

Das wäre das Skript:

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.6
-- Dumped by pg_dump version 16.4

-- Started on 2024-11-06 15:35:41

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

DROP VIEW IF EXISTS public."qryTest";
--
-- TOC entry 1383 (class 1259 OID 2493465)
-- Dependencies: 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 878 5
-- Name: qryTest; Type: VIEW; Schema: public; Owner: postgres
-- Data Pos: 0
--

CREATE VIEW public."qryTest" AS
 SELECT md_kto.bilpossaldoabh,
    md_kto.nurauskstebene_ww,
    md_kto.kontoanbuzugaenge,
    md_kto.kontowertpapier,
    md_kto.buchenmitkstpflicht,
    md_kto.kontozugangschabid,
    md_kto.kontoanbuabgaenge,
    md_kto.skontoteilnahme,
    md_kto.zuwendungsart,
    md_kto.kstebeneid_ww,
    md_kto.buchenmitktrpflicht,
    md_kto.nichtkoppeln,
    md_kto.vztsaldoabh,
    md_kto.mandantid,
    md_kto.nummer,
    md_kto.bezeichnung,
    md_kto.suchbegriff,
    md_kto.kommentar,
    md_kto.kontofibu,
    md_kto.kontoanbu,
    md_kto.kontokore,
    md_kto.kontopeab,
    md_kto.kontowawi,
    md_kto.stammkostenstelleid,
    md_kto.artid,
    md_kto.mengeneinheitid,
    md_kto.vorzeichenumkehr,
    md_kto.protuseraenderung,
    md_kto.protdatumaenderung,
    md_kto.planverfahrenid,
    md_kto.steuerschluesselaenderbar,
    md_kto.skontooriginalkto,
    md_kto.klasseid,
    md_kto.buchenmitkst,
    md_kto.buchenmitmengen,
    md_kto.kontoartfibu,
    md_kto.fremdwaehrungid,
    md_kto.protuserneuanlage,
    md_kto.steuerschluesselid,
    md_kto.protdatumneuanlage,
    md_kto.stammvariator,
    md_kto.buchensteuer,
    md_kto.zinsschluesselid,
    md_kto.kkkontenartid,
    md_kto.stammkostentraegerid,
    md_kto.gruppeid,
    md_kto.buchenmitktr,
    md_kto.skontooriginalkstktr,
    md_kto.istkostensatz,
    md_kto.nurauskstebene,
    md_kto.kstebeneid,
    md_kto.verweisid,
    md_kto.id,
    md_kto.status
   FROM public.md_kto;


ALTER VIEW public."qryTest" OWNER TO postgres;

-- Completed on 2024-11-06 15:35:44

--
-- PostgreSQL database dump complete
--
17

Ah, die “psql.exe” isses… mit diesen Parametern hier:

“-h b4db0122 -p 5432 -U postgres -d b4it_staging -a -f b4it_staging_Views.sql”

1 „Gefällt mir“
18

Das war so, seit ich zurückdenken kann.