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

On Sunday, June 26, 2022, WR <wolle321(at)freenet(dot)de> wrote:

>
> I made some test with pgadmin. Pgadmin (5.2) also reports this error now.
> And it doesn't matter if standard_conforming_strings is on or off.
>
>
> SET standard_conforming_strings = off;
>
> INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476,
> 2000, 2400, 2400, '\000', 500, 0, 'sinus(0|0|0;30;5;0;0,5;0)', '2021-08-31
> 11:53:22.442801', 0, 1);
>
> And
>
> SET standard_conforming_strings = on;
>
> INSERT INTO public.oned_figures VALUES (1, 'Figure_Wolle1', 476, -476,
> 2000, 2400, 2400, '\000', 500, 0, 'sinus(0|0|0;30;5;0;0,5;0)', '2021-08-31
> 11:53:22.442801', 0, 1);
>
>
> Both do report:
>
> WARNUNG: nicht standardkonforme Verwendung von Escape in
> Zeichenkettenkonstante
> LINE 8: ...(1, 'Figure_Wolle1', 476, -476, 2000, 2400, 2400, '\000\000&...
> ^
> HINT: Verwenden Sie die Syntax für Escape-Zeichenketten, z.B. E'\r\n'.
>
> ERROR: FEHLER: ungültige Byte-Sequenz für Kodierung »UTF8«: 0x00
>
>
I’m not in a position to test/experiment on any guaranteed timeframe but
your observation that the outcome of those two commands is independent of
value of standard_conforming_strings is either a straight up bug or you’ve
made a mistake somewhere. The warning is fully conditioned on that setting
being off.

E’\\000’ and ‘\000’ passed as string literals to the bytea input routine
are defined to be equivalent inputs under standard_conforming_strings and
neither can produce the warning in that case.

I suggest doing self-contained examples that demonstrate the documented
behavior not working as documented (or not being functional even if
intended) to pinpoint any bug that might be lurking here. With only
fragments and statements that seem impossible we are left to assume
operator error. pg_dump is completely correct in what it is producing
(non-escape literal \000).

I also suggest using psql and pg_dump directly, and not pgAdmin, to
demonstrate a core PostgreSQL bug.

David J.