PostgreSQL über ODBC

[ukulele]

Ich bastel hier an einem größeren Projekt bei dem ich viele kleine Dateien (HTML) aus einem Pfad auf der Festplatte in eine MSSQL DB importiere, dort zu validem XML forme und den Inhalt in relationale Daten zerlege (zumindest zerlegen möchte, fertig ist das alles noch nicht). Am Ende soll ein Teil der Daten mit einem bestehenden Bestand verküpft werden, der aus einer auf MSSQL basierenden Anwendung stammt. Das Zielsystem ist also MSSQL damit die Anwendung darauf zugreifen kann.

Jetzt habe ich schon einige wichtige Schritte umgesetzt und es läuft recht gut. Ich stoße aber mit dem SQL Express an Grenzen, vor allem weil die Daten die 10 GB Größe knacken. Ich arbeite schon über 5 MSSQL DBs wegen dieses Limits, das ist nicht so angenehm und nicht effizient. Meine Idee daher: Die Dinge die ich schon gut gelöst habe und die Dinge die mit PG schwer zu lösen sind mache ich in MSSQL. Die Daten schiebe ich über eine ODBC Verbindung auf eine Postgres DB. Den ein oder anderen Schritt kann ich in PG umsetzen. Am Ende hole ich mir die Daten, die ich in MSSQL mit dem Bestand verbinde wieder zurück, soweit die Theorie.

Nun sind meine Postgres Erfahrungen begrenzt aber ich habe mir schon ein paar Sachen angesehen. Allerdings hängt viel von dem ODBC Treiber ab und der macht schon ziemliche Zicken.

CREATE TABLE "public"."files"(
   "pk" UUID NOT NULL,
   "file" VARCHAR(1000) NULL,
   "file_date" TIMESTAMP NOT NULL,
   "file_type" CHAR(4) NOT NULL,
   "url" VARCHAR(1000) NOT NULL,
   "data" VARCHAR(10485760) NULL,
   "data_xml" XML NULL
   );
  
ALTER TABLE "public"."files" ADD CONSTRAINT files_pk PRIMARY KEY(pk);
ALTER TABLE "public"."files" ALTER COLUMN "data" SET STORAGE EXTENDED;
ALTER TABLE "public"."files" ALTER COLUMN "data_xml" SET STORAGE EXTENDED;

Problem: Spalten mit XML-Datentyp kann ich über ODBC nicht abfragen (und vermutlich auch nicht befüllen).

Der OLE DB-Anbieter 'MSDASQL' für den Verbindungsserver 'POSTGRESQL' hat inkonsistente Metadaten für eine Spalte bereitgestellt. Für die data_xml-Spalte (Kompilierzeit-Ordnungszahl 7) des "register"."public"."files"-Objekts wurde für 'DBCOLUMNFLAGS_ISLONG' der Wert 128 zur Kompilierzeit und 0 zur Laufzeit gemeldet.

Ich suche schon eine Weile nach einer Idee das Problem zu lösen oder zu umgehen. Elegante Vorschläge?

Das Equivalent zu VARCHAR(MAX) scheint TEXT zu sein. Spricht etwas gegen oder für VARCHAR(10485760)?

Tipps oder Ideen was man bei einer solchen Konstelation (MSSQL schreibt und liest aus PG) beachten oder verbessern sollte?

 

[ukulele]

Der verwendete ODBC Driver ist übrigens psqlodbc_11_00_0000-x64

Über OPENQUERY() kann ich die Tabelle aus MSSQL komplett lesen.

 

[akretschmer]

schon mal versucht, statt des XML-Types schlicht TEXT zu nehmen? Dann hast halt keine XML-Verifikation mehr. Ob varchar(max) oder text als Typ ist relativ egal übrigens. Die expliziete Angabe von storage extended würde ich auch mal weglassen, aber das sollte prinzipiell egal sein.

 

[ukulele]

Ich habe mit oder ohne STORAGE EXTENDED getestet, das scheint keine Probleme über ODBC zu machen. (Bin noch neu in der Materie, hat mir unter MSSQL aber Geschwindigkeit gebracht daher expirimentiere ich damit.)

Tatsächlich entspricht "data" am Ende "data_xml" nur eben nicht im XML Format. Ich kann natürlich XML auch zwischenzeitlich verifizieren und zur Anwendung von XML Funktionen zur Laufzeit konvertieren, das werde ich wohl erstmal tun. Aber: Ich habe mit OPENQUERY unter MSSQL mit INSERT, UPDATE, SELECT keine Probleme gehabt. Außer das er XML (in PG) als NVARCHAR ausgibt / behandelt. Ich kann auch NVARCHAR in die XML Spalte schreiben. Das geht aber nur wenn der String maximal 316 Zeichen lang ist. Danach bekomme ich einen Überlauffehler a la

Zeichenfolgen- oder Binärdaten würden abgeschnitten.

Den bekomme ich nur bei XML mit einer Zeichenfolge von mehr als 316 Zeichen. Warum 316 aber nicht 317? Ich komme mir vor wie in per Anhalter durch die Galaxis...

 

[akretschmer]

das EXTENDED macht PG eh automatisch, wenn es sich lohnt. Dabei wird transparent komprimiert und in TOAST-Tabellen das abgelegt. Das ist aber auf die Anwendung völlig transparent. Das andere scheinen Bugs im Treiber oder so zu sein ...

 

[akretschmer]

Allerdings hängt viel von dem ODBC Treiber ab und der macht schon ziemliche Zicken.

eine Alternative wäre vielleicht ein Foreign Data Wrapper, guggst Du hier:

• tds-fdw/tds_fdw
• PostgreSQL to Microsoft SQL Server Using TDS Foreign Data Wrapper
• Welcome to my PostgreSQL blogs: PostgreSQL foreign data wrapper for MS-SQL-Server "tds_fdw"

ODBC ist vermutlich schon mit 'ner Schicht Staub bedeckt, das FDW-Zeugs ist aktiv in Entwicklung. FDW zwischen PostgreSQL geht super, auch mit CSV-Dateien. Den Maintainer für den Oraggle-FDW kenn ich persönlich ganz gut, wohnt in Wien. Mit dem MS-Zeugs hatte ich (bis jetzt) aber noch keinen Kontakt.

 

[Dukel]

Von MSSQL gibt es die kostenlose Developer Edition ohne Einschränkungen. Dann musst du keinen Workaround mit Postgresql machen.

SQL Server 2017 Developer is a full-featured free edition, licensed for use as a development and test database in a non-production environment.

SQL Server Downloads | Microsoft

 

[ukulele]

FDW sieht interessant aus aber ist eher nicht mein Ziel. Ich mache ja fast immer nur MSSQL und muss das auch aus Funktionen heraus aufrufen können.

Developer ist nicht drin, die DB an die ich später anbinden will ist produktiv.

 

[Dukel]

Ist das eine Entwicklung für dich selbst oder für einen Kunden?
Wenn dir die MSSQL Editionen alle zu eingeschränkt sind (was Features oder Preis angeht), dann nutze doch generell etwas anderes (PG)?

 

[castorp]

und die Dinge die mit PG schwer zu lösen sind mache ich in MSSQL.

Was genau sind das für Dinge? So auf die Schnelle kann ich mir so gut wie nichts vorstellen, was in Postgres schwieriger wäre als in MSSQL.

Ich würde ehrlich gesagt ein hin- und herschieben zwischen zwei Systemen vermeiden, da das die Dinge nur unnötig verkompliziert. Wenn das Projekt später bei MSSQL bleiben muss, dann ist es mit Sicherheit einfacher zu versuchen, alles in MSSQL zu lösen.

 

[ukulele]

Zu Zeiten von PG 9.5 hatte ich schonmal versucht aus SQL heraus Dateien zu importieren, bin dabei aber auf Schwierigkeiten gestoßen (z.B. sollte man alles in einen Pfad legen, ich hatte aber Ordnerstrzukturen zu durchsuchen). Alles in allem bin ich aber einfach nur sehr tief drin in MSSQL, weniger in PG. Ich werde jetzt erstmal sehen was ich davon leicht nach PG portiert kriege, das ist nicht das Ding. Vielleicht geht ja nach dem Import alles weitere sinnvoller in PG aber dazu muss ich ne Menge portieren.

 

[ukulele]

Ich hab ein bischen weiter getestet, auch witizig irgendwie:
Wenn ich einen großen Insert über ODBC mache (Laufzeit ca. 1 Minute oder mehr), kackt der ganze SQL Server Dienst ab und muss neu gestartet werden.

Fehler auf Übertragungsebene beim Empfang von Ergebnissen vom Server. (provider: TCP Provider, error: 0 - Der angegebene Netzwerkname ist nicht mehr verfügbar.)

 

[akretschmer]

kackt der ganze SQL Server Dienst ab und muss neu gestartet werden.

MSSQL? Nun ja, ...

 

[Dukel]

Ich würde in einer Plattform bleiben. Egal ob SQL Server oder Postgresql.

Wieso darfst du nicht mit der DEV Edition entwickeln?

 

[ukulele]

Die Daten die dabei entstehen sollen mit produktiven Daten abgeglichen werden bzw. diese anreichern. Entwickeln ginge, aber nutzen müsste ich die Daten dann wieder auf einer Express DB. Außerdem verarbeite ich die Daten ja zum Zweck des produktiven Einsatzes, ich denke mal nach Lizenzbestimmungen ist das nicht gedeckt.