Ich will eine größere Datenbank von 15.4 in 16.0 importieren (Blobs, 4 Schemen). Der Dump in 15.4 läuft ohne Probleme. Dieses Dump-File kann ich in eine andere 15.4 Datenbank ohne Probleme einspielen.
Beim Versuch diesen in 16.0 zu importieren werden unzählige Fehlermeldungen “Fehler: ungültige Anweisung \N” geworfen und viele Tabellen nicht populiert.
Ich habe im Web gesucht aber keinen Hinweis gefunden, dass es eine Änderung im SQL-Befehl “COPY INTO … FROM STDIN; …” gegeben hat, welches beim Dump im Textformat von pg_dump geschrieben wird (Einlesen mit psql).
Ich habe alternative die Version pg_dump --format=c und pg_restore ausprobiert, mit dem gleichen Ergebnis.
Interessant ist, dass ich dieses File in 15.4 ohne Fehlermeldung einlesen kann (alle Tabellen+Blobs ordnungsgemäß gefüllt).
Wenn Du pg_dump/pg_restore für die Migration einer Datenbank verwenden wills, musst Du pg_dump der Zielversion verwenden. In Deinem Fall also pg_dump von der Version 16.
Ich verwende die Kombination pg_dump mit plain-SQL und import mit psql.
Ich habe testweise die DB auf 16.0 neu angelegt und populiert. Dann habe ich die DB mit pg_dump version 16.0 in plain-SQL Inserts exportiert. Der Import dieses Dumps in einer anderen “leeren” DB (auch Version 16.0) ist auch an der gleichen Stelle mit der gleichen Fehlermeldung fehlgeschlagen.
Eventuell den Bug gleich an die PostgreSQL Community stellen.
Siehe PostgreSQL: Support unter Bug Reporting.
Die Leute dort möchten in jedem Fall ein kleines reproduzierbares Beispiel.
“ungültige Anweisung \N” kann manchmal auch an Encoding Problemen liegen, vor allem wenn psql auf unterschiedlichen Betriebssystemen (Windows vs. Linux) verwendet wird. Du kannst mal versuchen mit --encoding=... ein anderes Encoding zu verwenden.
Ich habe den Grund für das Problem gefunden: Postgres lässt eine m.E. nicht sinnvolle Spaltendefinition zu. Diese führt im Dump/PSL Import zu dem Problem.
In meiner DB waren einige Spalten mit name charcter varying(xx) DEFAULT NULL::character::varying NOT NULL, … definiert.
Dies macht m.E. keinen Sinn, eine Spalte die den Defaultwert NULL hat, aber nicht NULL sein darf.
Postgresql hat das angelegt und gespeichert. pg_dump hat diese Definition ins SQL-Dump-File geschrieben (Version 15.4 sowie 16.0).
Versucht man mit psql das SQL-Dummpfile ein eine leere DB einzuspielen, so wird bei 15.4 und 16.0 die Tabelle mit der Spaltendefinition erfolgreich angelegt.
Das nachfolgenen COPY INTO … FROM STDIN; in diese Tabelle funktionert bei 15.4 erzeugt bei 16.0 einen Fehler. Dieser führt dazu, dass das Skript total außer Tritt kommt und tausende von Folgefehlern erzeugt.
Wenn man die m.E. unsinnige Spaltendefinition ändert (z.B. in name charcter varying(xx) NOT NULL, also den DEFAULT NULL value entfernt), dann lässt sich auch mit 16.0 das SQL-Dumpfile ohne jegliche Fehlermeldung in eine leere DB einlesen.
Für mich stellt sich die Frage ob hier in postgres ein Fehler vorliegt (Spalte mit DEFAULT NULL aber mit NOT NULL definiert) oder ob es sich um ein gewolltes Feature handelt (Sinn kann ich nicht erkennen).
Offenbar hat in der Version 16.0 zudem eine Korrektur/Anpassung beim COPY INTO Befehl gegeben.
Hier ein SQL-Dump der nicht eingelesen werden kann (generiert mit 16.0 von einer 15.4 DB, schlegt fehl beim einlesen mit psql (16.0)):
– Dumped from database version 15.4 (Ubuntu 15.4-1.pgdg22.04+1)
– Dumped by pg_dump version 16.0 (Ubuntu 16.0-1.pgdg20.04+1)
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;
SET default_tablespace = ‘’;
SET default_table_access_method = heap;
CREATE TABLE public.region (
name character varying(25) DEFAULT NULL::character varying NOT NULL,
id character varying(5) DEFAULT ‘NULL::character varying’::character varying NOT NULL
);
COPY public.region (name, id) FROM stdin;
Berlin 10
Dortmund 11
bundesweit 99
\.
ALTER TABLE ONLY public.region ADD CONSTRAINT region_pkey PRIMARY KEY (id);
psql zeigt folgenden Fehlermeldungen:
FEHLER: Wert zu lang für Typ character varying(5)
Fehler: ungültige Anweisung \.
FEHLER: Syntaxfehler bei »Berlin«
Ändert man die Definition der Tabelle in
CREATE TABLE public.region (
name character varying(25) NOT NULL,
id character varying(5) NOT NULL
);
dann lässt sich das gleiche Dump-File (mit geänderter Tabellendefinition) in 16.0 einlesen.
Hmm, 'NULL::character varying'::character varying sieht falsch aus (das setzt ja die Zeichenkette NULL::character varying als Default Wert). Wenn Du pg_dump von der 15.4 nimmst, wird dann das Gleiche generiert? Wenn nicht, klingt das wirklich nach einen Bug in der 16er. Wenn man das zu dem sinnvolleren DEFAULT NULL::character varying ändert, dann klappt der Import auch mit den Default Werten.
Aber eigentlich sollte der Default Wert gar nicht verwendet werden, da ja für alle Spalten Werte in dem COPY Befehl angegeben sind.
Von der Logik her sollen die Spalten nicht leer (also NOT NULL) sein. Ich weiß nicht woher der DEFAULT NULL stammt. Die Tabellendefinitionen sind schon ein paar Jahre alt und haben unter postgres 13/14/15 keine Probleme bereitet. Deshalb ist diese fragwürdige Spaltendefinition nicht aufgefallen.
Ich habe natürlich auch den Dump von pg_dump(15.4) auf die DB und einlesen unter 16.0 (psql) ausprobiert.Das zeigt die gleiche Fehlermeldung, weil pg_dump (15.4) die gleiche Tabellendefinition wie 16.0 produziert.
Wie gesagt: wenn man keinen Wert für DEFAULT angibt, dann ist das automatisch NULL - aber die Spalte bei der es das Problem gibt, hat eben keinen NULL Wert als Default.
Ich habe mir den Dump angeschaut, und er ist fehlerhaft.
Zuerst einmal stehen da “curly quotes” (‘und ’) statt Apostroph ('). Der Dump ist also irgendwie verstümmelt worden, und zwar nicht von PostgreSQL.
Wenn ich den Dump repariere (also die Apostrophe ersetze und in den COPY-Daten einen Tabulator zwischen die Werte schreibe) kann ich den Fehler nachvollziehen. Er wird ausgelöst durch den seltsamen DEFAULT-Wert 'NULL::character varying', weil dieser String länger ist als das Längenlimit der Spalte id (5 Zeichen).
Tatsächlich ist das ein PostgreSQL-Bug, der in Version 16 eingeführt wurde. Ich habe einen Thread auf der Hackers-Liste gestartet; schauen wir, ob es wert ist, den Bug zu fixen.
Der Workaround ist jedenfalls leicht: den sinnlosen DEFAULT-Wert anders setzen.
Aber der DEFAULT Wert sollte doch beim COPY keine Rolle spielen, da er gar nicht verwendet wird?
Ich kriege diese Fehlermeldung (mit dem korrigierten Skript - ich vermute die Fehler sind beim Copy & Paste hier im Forum entstanden)
psql:test.sql:8: ERROR: value too long for type character varying(5)
psql:test.sql:12: error: invalid command \.
psql:test.sql:12: ERROR: syntax error at or near "Berlin"
Ein “normales” INSERT funktioniert, obwohl der DEFAULT Wert zu lang ist.
Offensichtlich scheint COPY zuerst zu versuchen ein insert mit NULL Werten zu machen bei dem dann der default Wert verwendet wird. Was ich irgendwie merkwürdig finde.
Vor PostgreSQL v16 wurden die Defaultwerte nur berücksichtigt, wenn die Spalte im COPY-Statement ausgeschlossen war. Seit v16 gibt es die COPY-Option DEFAULT 'default_string', also könnte der Defaultwert auch für Spalten benötigt werden, die in der Liste vorkommen.
Ich habe einen Patch eingeschickt, um die Defaultwerte nur dann auszuwerten, wenn die COPY-Option DEFAULT angegeben war.
