Re: Differences in Escaped bytea's when creating a plain pg_dump

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