Differences in Escaped bytea's when creating a plain pg_dump

From: WR <wolle321(at)freenet(dot)de>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Differences in Escaped bytea's when creating a plain pg_dump
Date: 2022-06-23 15:13:50
Message-ID: 4d2ad6be-560b-64de-ea12-f4e5f1b71992@freenet.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

--
May the source be with you

Responses

Browse pgsql-general by date

  From Date Subject
Next Message WR 2022-06-23 16:21:37 Re: Differences in Escaped bytea's when creating a plain pg_dump
Previous Message Alvaro Herrera 2022-06-23 12:43:56 Re: Support logical replication of DDLs