GeODin zu PostGIS, aber wie?

Puh ok, euch beiden danke.

Diesmal etwas weniger konfus:

  1. @ akretschmer: danke für das ding mit dem FDW. Hab ich sogar soweit verstanden. Nur dachte ich der View wird nicht automatisch geupdatet? Oder ist der immer 1:1 zur entsprechenden Quelle (also wenn ich was in die GeodinTAbelle eingebe ist es im View auch so?) Falls ja wäre das ein super Option.

Und ja ich hätte vor es mit 2 DB auf einem Server zu machen.

  1. @ akretschmer: die anderen Lösungen habe ich auch schon mal “in den Händen gehabt”. Wobei mich das logical Streaming Replication und Slony interessieren würden, falls du mir dazu mehr sagen kannst.

  2. @ Gismen: wie du 2 Posts weiter vorne siehst habe ich das mit dem Insert Trigger schon mehrfach auf verschiedene Art und Weisen versucht, jedoch das Problem mit der GID(Serial)-Spalte gehabt und es dann mit OLD/NEW probiert, wo akretschmer aber meinte es würde noch was fehlen… habe ja eine entsprechende Fehlermeldung beim Insert von neuen Daten in der Urtabelle bekommen.

Zusammenfassung: Wenn der View wirklich immer sofort das Live-Abbild der Geodin-Tabelle ist (also bei jedem Insert oder ähnlichem in die Urtabelle das sofort so abbildet) und dies dann per FDW in eine andere DB-Tabelle übernommen wird, ist es wohl die sinnvollste Lösung.

Frage zu deinem FDW-Beispiel: Ich möchte auf dem selben Server eine zweite DB machen. Nehme ich dann anstatt create server, create db?

Danke euch schon mal. Ich verabschiede mich schon heute in die Ferien und melde mich somit erst Anfang des neuen Jahres wieder.

Schöne Weihnachten und bis nächstes Jahr.

Gruß Stefan

Slony ist etwas, ähm, nicht mehr zeitgemäß. logical Streaming Replication ist eine sehr junge Entwicklung, die aber In-Code ist, und die Grundlage für BDR (Bi-Directional Replication) bildet. Das wird (hoffentlich) in 10 In-Core sein. Einige unserer Kunden nutzen das schon, produktiv, insbesondere in Anwendungen, wo man 2 oder mehr Rechenzentren geographisch verteilt hat, also z.B. New York, Singapure und Sydney. Die Architektur läßt (derzeit) bis zu 48 Master zu.

Es gibt einige Anleitungen im Internet, wie man das nutzt, z.B. bei uns:

https://2ndquadrant.com/de/resources/pglogical/

Du kannst dafür auch professionellen Support bekommen. Z.B. bei uns ;-). Ist nicht ganz trivial alles, z.B. mußt Du aufpassen bei DDL-Befehlen, also z.B. ALTER TABLE etc., und natürlich muß man sich ausreichend Gedanken machen, wie man Konflikte möglichst vermeided. (also z.B. globale Sequencen)


Zusammenfassung: Wenn der View wirklich immer sofort das Live-Abbild der Geodin-Tabelle ist (also bei jedem Insert oder ähnlichem in die Urtabelle das sofort so abbildet) und dies dann per FDW in eine andere DB-Tabelle übernommen wird, ist es wohl die sinnvollste Lösung.

Ja. Ein VIEW ist nix weiter als ein Alias auf das Select, auf dem der View basiert. Es wird also in Wahrheit nur das Select ausgeführt. Etwas anders ist das bei materialized Views, die benötigen ein Refresh.

Ok cool, danke.

Und anstatt Server kann ich auch einfach eine DB im FDW createn oder?

jupp. Siehe die Options im Befehl.

Ok.

Super, dann nun endgültig Danke und bis zum nächsten Jahr.

Gruß

Hallo im Neuen Jahr,

ich habe gerade die GEschichte mit dem FDW versucht… Alle Sachen wurden successfully ausgeführt… SOweit so gut… Nun finde ich aber diese foreign Tabelle nirgends ^^ auch der Foreign Server ist nicht auf zu finden… ich bin dabei im PG_Admin… Wo finde ich denn nun diese ZielTabelle?

Ich habe folgende Schritte ausgeführt:

In der Quelldatenbank “Testobjekt”

create view view_select as select prj_id, xcoord, ycoord, zcoordb, longname from geodin_loc_ssgkrzt1;
commit;

In der Zieldatenbank “FDW-Server” habe ich dann folgende Schritte ausgeführt:

CREATE SERVER "FDW-Server" FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (host '192.168.31.239', dbname 'Testobjekt', port '5432');

create user mapping for postgres server "FDW-Server" options 
(user 'postgres', password '********');

create foreign table kopie_stammdaten 
	(prj_id character varying(6), 
	xcoord double precision, 
	ycoord double precision, 
	zcoordb double precision, 
	longname character varying(40)) 
server "FDW-Server" options(table_name 'view_select');

All diese Schritte wurden ohne Probleme ausgeführt.
Aber wo finde ich jetzt diese foreign table kopie_stammdaten?.
In der ZielDB “FDW-Server” ist nix.

Oder hab ich was falsch verstanden?

dort, wo Du grad bist. Möglicherweise ‘sieht’ PGAdmin das nur nicht, schaue mit psql nach.

du hast recht, die tabelle war da… pgadmin zeigt sie mir nur nicht an. habe bisher pgadmin3 benutzt. Gerade habe ich gesehen dass es schon die 4 gibt… damit lassen sich nun auch super easy diese FDW-Geschichten machen. dort wird mir die tabelle auch angezeigt und entsprechend aktualisiert…
super sache. danke schon mal für diese hilfe.

kann ich in dieser foreign tabelle nun auch spalten anhängen und trigger verwenden? ich würde nämlich gerne noch die postgis geometrie mit reinbringen.
oder sollte ich das lieber in dem view machen!?
ich frage lieber vorher eh ich was an der tabelle kaputt mache.

probiere es doch mal aus :wink:

Du kannst ja eine Testtabelle erstellen, das tut nicht weh.

Jo hab ich.
Ich kann zwar Spalten der foreign Tabelle hinzufügen, aber dann meckert es, dass diese Spalten nicht gibt. Trigger gehen gleich gar nicht.
Könnte ich das in einem weiteren view machen? Also würde das Sinn ergeben, mal abgesehen von der Machbarkeit!?
Ich bräuchte in der Zieltabelle mindestens 3 neue Spalten welche mit Funktionen ausgestattet werden sollen, welche durch Trigger ausgelöst werden sollen. Eine der Spalten soll außerdem ein Serial sein.

hm ich kann mit dieser foreign tabelle absolut nix machen… nicht mal ne spalte anfügen.
aaahhhh… bis dahin ist das ne super sache mit der aktualisierung, aber ich kann nicht wirklich weiter dami arbeiten oder gibts da eine möglcihkeit die ich noch nicht gefunden habe?

kann keiner mehr helfen?

Mach dir doch einen View oder Materialized View aus der ForeignTable, der die zusätzlichen Spalten enthält. Das Serial kannst du auch simulieren mit row_number() over ().

Gruß Uwe

Das würde ich gerne, aber weder in der documentation noch im netz finde ich irgendwas darüber, wie man spalten einem view anhängt welche nicht in der entsprechenden tabelle sind. ich möchte einfach eine spalte srid dazu als integer und eine bedingung dafür… keine Ahnung wie ich diese spalte im selben view erzeuge, in der die daten aus der foreign table angezeigt werden. habe leider nix dazu im netz gefunden.
scheinbar ist eine automatisierung der aktualisierung mit der db nicht so einfach. zumindest nicht wenn man die urtabelle nicht verändern möchte und am ende ein paar bedingungen und spalten anhängen möchte.
hm… naja ich probier mal weiter rum.
danke euch

Mach doch den View aus der Foreigntable. Wenn du da dann was änderst kannst du dies mit einer Rule an die Foreigntable weitergeben, die das dann in der Urtabelle ändern sollte.
Wenn du das nicht möchtest, dann wird dir nichts anderes übrig bleiben, als die aus der Foreigntable eine eigene Tabelle zu erstellen, die deine Felder enthält… Diese kannst du dann ändern und musst eben bei Änderungen der Urtabelle diese nachführen.

Gruß Uwe

Das ist ja genau das PRoblem… Ich möchte eine Automatisierung der ganzen GEschichte… Und du weißt ja dass ich auch schon alles über Trigger gemacht habe und damit eine Tabelle in einem anderen Schema erstellt habe. Leider hat er dabei immer alle Einträge neu geinsert wenn etwas in der Urtabelle geändert wurde. Und das hat die serial-spalte sofort ins unermessliche getrieben.

Also nochmal kurz zusammengefasst:

Ich habe eine Urtabelle mit vielen Spalten. Diese Urtabelle darf nicht geändert werden, weil diese von einem Programm (Geodin) “betrieben” wird. Über Geodin werden dort Daten implementiert, geupdatet oder gelöscht… Ich brauche nun bestimmte Spalten der Urtabelle in einer anderen Zieltabelle in der ich arbeiten kann (Postgisgeometiren zufügen, andere Spalten hinzufügen). Diese Zieltabelle soll automatisch immer auf dem Stand der Urtabelle sein.
Heißt: Wenn ich was über Geodin in die Urtabelle eingebe, lösche oder update, soll dies automatisch an die Zieltabelle weitergegeben werden.
Diese Zieltabelle sollte dabei aber möglichst nicht im selben Schema liegen wie die Urtabelle. Das war es schon.

Nun habe ich das wie schon gesagt alles über trigger versucht in eine Zieltabelle zu führen. Leider wurde dabei nie nur die entsprechende Zeile eingefügt, geupdatet oder gelöscht, sondern immer alle Einträge der Urtabelle wurden einfach kopiert. Bei unser geringen Anzahl von Zeilen (ca. 45000 im Moment) habe ich einfach einen delete-befehl reingeschrieben welcher immer vor dem Insert ausgeführt wurde… ist nicht gerade perfomant, aber funktionierte… da war aber dann das problem mit der serial-spalte…
Die andere elegantere Lösung war bisher der FDW… hat super geklappt… nur leider kann ich dann damit nicht weiter arbeiten.

Im Prinzip muss ich es wohl doch über Trigger in ein anderes Schema auf der selben DB probieren. Ich muss irgend eine Funktion bauen die nur die Zeilen in der Zieltabelle ändert, die auch in der Urtabelle geändert werden. Dabei kommt wohl dieses .old und .new zum Einsatz, oder? Falls ja wäre ich für ein erklärendes Beispiel dankbar.

Dies ist wohl meine letzte möglichkeit. Es wäre schön wenn ihr euch noch ein letztes mal dieser Problematik annehmt =) dann lass ich euch in Ruhe und geh weinen xD

Gruß

Habe mich nun nochmal durch die ganze Trigger-Thematik durchgearbeitet und bin wesentlich weiter gekommen. Folgende Funktion hat sich bisher ergeben:

CREATE OR REPLACE FUNCTION public.insert()
  RETURNS trigger AS
$BODY$
      BEGIN
	IF (TG_OP = 'DELETE') THEN
            DELETE FROM postgis.stammdaten WHERE locid = OLD.locid and xcoord = OLD.xcoord and ycoord = OLD.ycoord;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE postgis.stammdaten 
            SET locid = NEW.locid, xcoord = NEW.xcoord, ycoord = NEW.ycoord
            FROM public.geodin_loc_ssgkrzt1;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO postgis.stammdaten SELECT NEW.locid, NEW.xcoord, NEW.ycoord;    		
		RETURN NULL;
      	END IF;
        RETURN NULL;
      END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.insert()
  OWNER TO postgres;

Der INSERT und DELETE Teil funktioniert einwandfrei. Der UPDATE-Teil funktioniert leider noch nicht wie gewünscht.
Im Moment passiert folgendes: ich ändere den wert einer Zeile in der Spalte locid per update in der urtabelle (public.geodin_loc_ssgkrzt1).
Daraufhin ändert mir der Trigger alle Werte in allen Zeilen und SPalten in der Zieltabelle auf die Werte der betreffenden Zeile aus der Urtabelle. Irgendwie auch logisch bei dem was ich da geschrieben habe.
Ich tu mich sowieso mit der UPDATE-Funktion etwas schwer und wäre über einen ANstoß was ich ändern muss, damit nur der eine entsprechende Wert in der Zieltabele geupdatet wird, sehr dankbar. Wenn ich das habe, habe ich das gesamte Problem gelöst.

Ja, ich denke mal, ich schenke Dir ein WHERE.

So, das WHERE hatte ich schon mal drin, aber an der falschen Stelle und das FROM muss raus… Nun nach 5 Seiten und 42 Beiträgen ist es endlich geschafft xD Falls irgendwann auch mal jemand sowas machen muss hier noch die endgültige Beispielfunktion für einen AFTER Trigger zwischen zwei Schematas für UPDATE, DELETE und INSERT in einem:

CREATE OR REPLACE FUNCTION public.insert()
  RETURNS trigger AS
$BODY$
      BEGIN
	IF (TG_OP = 'DELETE') THEN
            DELETE FROM postgis.stammdaten WHERE locid = OLD.locid and xcoord = OLD.xcoord and ycoord = OLD.ycoord;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE postgis.stammdaten 
            SET locid = NEW.locid, xcoord = NEW.xcoord, ycoord = NEW.ycoord
            WHERE locid = OLD.locid and xcoord = OLD.xcoord and ycoord = OLD.ycoord;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO postgis.stammdaten SELECT NEW.locid, NEW.xcoord, NEW.ycoord;    		
		RETURN NULL;
      	END IF;
        RETURN NULL;
      END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.insert()
  OWNER TO postgres;

Danke an euch beide… GISMAN werd ich bestimmt noch wegen der PostGIS-Geometrie nerven, aber das Grundproblem ist gelöst.