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

Am 27.06.2022 um 12:12 schrieb Daniel Verite:
> WR wrote:
>
>> First run worked.
>> Second run worked.
>> Then I changed to SET standard_conforming_strings = off;
>> Third run worked.
>> Fourth run throw the error
>> Then I changed back to SET standard_conforming_strings = on;
>> Fifth run throw the error too.
>> And only adding E and second backslash helped.
>
> This kind of unstable behavior can be seen if the SET may not be
> executed by the same session (same connection to the server) as the
> subsequent queries.
> SET affects only the session it's being run in.
>
> For instance a connection pooler configured in statement mode may
> produce that behavior. The solution in the case of a connection pooler
> is to group related statements into a transaction.
>
> Maybe pgAdmin has a pooler like that, but if you're issuing the statements
> in the same SQL window, I would find it quite surprising that it doesn't
> run them by the same session.
> Or maybe you're mixing queries from different SQL windows that each
> have their own connection, and in some cases you do the SET
> in a window and the INSERT in another window.
> Or maybe it's a pgAdmin bug.
>
>

Ok, thank you Daniel,

in all tests I put the SET statement before the INSERT in the same query
tool and run it as a whole. (no statement was marked by mouse for single
execution). So I agree with you, that one transmission is used by
pgAdmin to run SET and INSERT.
I always had a SET before each INSERT, so there could be no unknown
state of standard_conforming_strings, no matter if pdAdmin uses
different connections for each run of the script or the same again.

The supplement of the second backslash in the c++ code costs me some
nerves. Sending an statement std::string without doublebackslashes to
pqxx::transaction results in the "0x00 is not a valid UTF8 sequence"
error 21020 (as I wrote already). I'm afraid, I can't simply replace all
backslashes in the whole dumptext with two of them.
Since I found out now, that psql works nice with my dumpfiles, maybe its
easier, to call psql from my code and not to use a pqxx::transaction. So
I can also save myself loading the dump from the sql-file. The more I
think about it, the more I love this idea.

Thanks a lot, Wolfgang

--

Wolfgang Rißler
mailto: wolfgang(dot)rissler(at)freenet(dot)de
mobil: +49 1520 9191759
- may the source be with you -