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