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.rissler@freenet.de
mobil: +49 1520 9191759
- may the source be with you -