Am 23.06.2022 um 17:13 schrieb WR:
Hello community,
I've some trouble in restoring a plain text pg_dump.
Postgres version is 13 x64 running on Windows10x64 installed from EDB
package.
The database has bytea_output = 'escape' option because of some
processing software needs it for historical reasons.
Dump command is:
pg_dump --file=mydump.sql --format=plain --verbose --encoding=UTF8
--schema=public --host=localhost --username=myuser --inserts dbname
We have two tables that have a bytea-row.
But when I look at the dumpfile there is a difference between the
escaped bytea-string. (note: both INSERT's from the same run of pg_dump
in the dumpfile)
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;
[snip]
INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476,
2000, 2400, 2400,
'\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000&\001\334\001\334\001&\001\000\000\332\376$\376$\376\332\376\000\000',
500, 0, 'sinus(0|0|0;30;5;0;0,5;0)', '2021-08-31 11:53:22.442801', 0, 1);
[snip]
INSERT INTO public.profiles VALUES (1, 1, 's', 152,
'\\037\\003\\000\\000\\000\\037\\003\\000\\000\\000\\037\\003\\000\\000\\000\\037\\003\\000\\000\\000!\\003\\000\\000\\000!\\003\\000\\000\\000!\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000\\000\\000"\\003\\000',
'2016-08-25 00:00:00+02');
[snip]
When I restore them via pgadmin4 query tool, both INSERTS do work.
But when I read them with my c++ written software from file and send
them with pqxx-Library as a transaction, the first bytea-string
generates a fault 21020. (0x00 is not a valid utf8 sequence). I also
checked the read string in c++ debugger, the single backslashes in the
one case and the double backslashes in the other case are there.
So my questions are:
Why do we get one bytea-string with double backslashes (which works) and
another one with single backslashes (which actually not works with
pqxx-transactions)?
Can I convince pg_dump somehow, to generate double backslashes in all
bytea-strings?
Why does pgadmin understand both formats. pqxx-transaction does not?
Thank you for this great database-system. I really like it.
Wolfgang
Hello again,
I've found one mistake in the data of the second table
(public.profiles). They seem to be really "double escaped" somehow. So
they are not valid anymore.
Now I know pg_dump doesn't make any difference between the two tables.
The only valid data is from table (public.oned_figures) with one
backslash. That was my fault, sorry.
But one question is left.
When I read this valid data into a c++ std::string (and I checked that
the single backslashes are still there). Why can't I put this
SQL-command to a pqxx-transaction and execute it. It looks like the
pqxx-transaction unescapes the bytea-string and then it finds the 0x00
bytes, which are not allowed in text-strings but should be in bytea-strings.
--
May the source be with you
