Spaltentyp ändern

Ich habe eine Tabelle, auf dieser basieren einige Views.
Nun möchte ich in meiner Tabelle den Typ einer Spalte ändern.
Welche Möglichkeiten gibt es ohne alle Views neu erstellen
zu müssen. :confused:

Server: Postgresql 8.2

Keine, IMHO.

Andreas

Jo so scheint es zu sein habe die ersten gemacht und ändern ist nicht.
Kommt des vielleicht in einer neueren Version auch noch?

Unwahrscheinlich. Unter Umständen müßte man dann ja auch Funktionen komplett umschreiben, die von einem bestimmten Datentyp ausgehen. Überleg mal, du hast eine INT-Spalte und änderst das auf TEXT. In einem VIEW nimmt Du aber den Wert und rechnest 5 dazu. Und das ist noch ein einfaches Beispiel.

Andreas

Ja das macht sinn ich hab jetzt nur im PG-Admin getestet.
Das erklärt aber nicht warum ich keine Spalte im View hinzufügen kann und nicht mal die Bezeichnung der Spalten ändern kann.
Aber ich kann den ganzen SQL umbauen das er auf ganz andere Tabellen geht.
Ist das nur nen Problem vom PG-Admin?

Ich sehe daran kein Problem.

Andreas

Ein VIEW ist nix weiter als ein irgendwo hinterlegtes SELECT

So ich dachte der View ist ne Art Temporäre Tabelle die mittels meinen Select nur gefüllt wird und das eben DB seitig.

Was sollte mir der Code eigentlich genau zeigen???
Ich meinte bei allen den SQL auf den der View aufsetzt den SQL der View Definition.

Aber wahrscheinlich hat mir die die Vorstellung der virtuellen “Tabelle = View” die Sicht auf die dinge verbaut.

Aber wenn der View nur eine “gespeicherte Definitions-SQL” ist verstehe ich noch weniger warum ich die nicht ändern kann bzw die Ergebnisspalten nicht .
:confused: verwirrt

Change: Verstanden steht ja auch nichts anderes in der Beschreibung einen Geschwindigkeitsvorteil bekomme ich also auch nicht durch einen View oder doch?

Nein. Intern ist ein VIEW eine Tabelle mit einer RULE auf SELECT, die sich “_RETURN” nennt.

test=# create table foo (i int);
CREATE TABLE
test=*# create rule "_RETURN" as on select to foo do instead select 1 as i;
CREATE RULE
test=*# select * from foo;
-[ RECORD 1 ]
i | 1

test=*# insert into foo values (2);
ERROR:  cannot insert into a view
HINT:  You need an unconditional ON INSERT DO INSTEAD rule.



Aber wenn der View nur eine “gespeicherte Definitions-SQL” ist verstehe ich noch weniger warum ich die nicht ändern kann bzw die Ergebnisspalten nicht .
:confused: > verwirrt

Schau obigen Code.

Andreas

Hallo zusammen,
ich muss dieses Uralt-Thema nochmal aufgreifen, da ich momentan vor einem ähnlichen Problem stehe:

Ich müsste eine Spalte verlängern. So weit, so gut… aaaber:
Von dieser Tabelle, in der sich diese Spalte befindet hängen ingesamt ca. 20 Views ab.
PG mault, er könne die Spöate nicht ändern, weil Objekte davon abhingen.
Nach dem, was ich oben gelesen habe, scheint es in PostgreSQL tatächlich nicht möglich zu sein, Spalten zu verändern, die Teil einer Views sind, ohne die View vorher zu löschen und neu anzulegen

Das kann doch nicht sein, oder? Ist das auch in PG 9.x immer noch so?
Das macht die ganze DB schon ein bisschen statisch…denn, wenn ich mir mein Beispiel (das ist eine relativ kleine DB) noch ein bisschen ausmale…

Bei Oracle werden die Views bei Änderungen an den zugrundeliegenden Tabellen zunächst mal “ungültig”, dann muss man sie neu kompilieren und gut, das ist aber deutlich weniger fehleranfällig, als eine View komplett zu löschen und neu zu erstellen.

Ein workaround, der mir einfiele, hieße, für jede Tabelle eine view zu generieren und alle weiteren views basieren nur noch darauf… dann müsste man genau diese eine view ändern…allerdings müsste man das bei Design bereits berücksichtigen.

Hat vielleicht noch jemand eine Idee?

Schon mal Danke und viele Grüße

Dieter

Ja, leider.

Das ist auch eines der Dinge die mich etwas stören, aber da wir Schema-Änderungen grundsätzlich über automatisierte Scripte aus der Versionsverwaltung ausliefern hält sich der Schmerz in Grenzen.

Als Workaround kann man vor dem Ändern der Tabellen den Source aller Views sichern, dann die Views droppen, die Tabellen ändern und die Views wieder einspielen. Das lässt sich auch automatisieren.

Zum Sichern aller Views:

create table view_defs 
(
   view_schema text,
   view_name text,
   source text
);
insert into view_defs (view_schema, view_name, source)
SELECT n.nspname, 
       c.relname, 
       rtrim(pg_get_viewdef(c.oid, true),';')
FROM pg_class c
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind in ('v')
  AND n.nspname not in ('pg_catalog', 'information_schema');

Achtung: solltest Du Views mit “CHECK OPTION” verwenden, muss man das SELECT ein wenig anpassen, da pg_get_viewdef das nicht berücksichtigt.

Zum Droppen aller (gesicherten) Views kann man dann das verwenden:

do
$do$
declare
  vrec record;
  stmt text;
begin
  for vrec in (select * from view_defs) loop
    stmt := format('drop view if exists %I.%I cascade', vrec.view_schema, vrec.view_name);
    execute stmt;
  end loop;
end;
$do$
;

Dann kann man die Tabellen beliebig ändern und danach die Views wieder anlegen:

do
$do$
declare
  vrec record;
  stmt text;
begin
  for vrec in (select * from view_defs) loop
    stmt := format('create view %I.%I as ', vrec.view_schema, vrec.view_name)||vrec.source;
    execute stmt;
  end loop;
end;
$do$
;

Danke für die superschnelle Antwort auch noch mit dem (fast) fertigen Workaround :wink:
Leider habe ich auch views, die von views abhängen und somit muss ich mir noch was wegen der Reihenfolge einfallen lassen, in der die views wieder aufgebaut werden (sonst gibts da ja auch Probleme)

Ist ja schön, zu wissen, dass ich nicht der Einzige bin, den diese viewproblematik stört.

Wie gesagt: das Ganze verliert seinen Schrecken wenn man die Scripte vernünftig verwaltet. Wir verwenden z.B. Liquibase aber es gibt auch andere Tools die das verwalten können. Wenn man das richtig aufsetzt ist die Einschränkung eigentlich gar kein Problem.

Die richtige Reihenfolge müsste man über information_schema.view_table_usage auslesen können. Da steht drin, welche View von welcher Tabelle bzw. View abhängt.

Ich verwende das Statement von hier: http://mwenus.blogspot.de/2014/04/db-view-dependencies-in-postgresql.html

Nochmals einen Riesendank… Das Statement ist ja geradezu genial.

Mein “Problem” ist halt, dass die DB und die Anwendung eigentlich relativ klein ist und der Verwaltungsoverhead sich in Grenzen halten sollte.
tat er bis jetzt ja auch… ich dachte nur nicht, dass die Änderung von zwei Spalten tatsächlich so einen Ärger machen würde.

Eine andere Möglichkeit die Definitionen in der richtigen Reihenfolge zu extrahieren ist pg_dump.

Dazu kann man sich die Liste der Views als “command line parameter” generieren lassen:

select 'pg_dump '||string_agg( '-t ' || format('%I.%I', n.nspname, c.relname), ' ')||' > views.sql'
from pg_class c 
  join pg_namespace n on n.oid = c.relnamespace
WHERE c.relkind in ('v')
  AND n.nspname not in ('pg_catalog', 'information_schema');

Das Skript z.B. als get_view_list.sql abspeichern.

Die Ausgabe davon kann man dann in eine (batch/shell) Datei umlenken. Damit erhält man ein SQL script welches die Views in der richtigen Reihenfolge anlegt. Für Windows z.B.

psql -X -f get_view_list.sql --quiet --tuples-only dbname > dump.cmd
dump.cmd

Dann alle Views droppen (siehe meine erste Antwort), die Tabellen ändern und danach views.sql laufen lassen.

Das “generierte” views.sql kann man natürlich durch die entsprechenden Optionen für pg_dump noch “tunen”.

Das erscheint mir als weniger aufwändig, zumal auch die comments und rules “mitgenommen” werden.
Auf jeden Fall hab ich heute eine Menge gelernt. :wink:

Danke nochmal

Dieter