Re: PQescapeByteaConn - returns wrong string for PG9.1 Beta3

On Aug4, 2011, at 22:54 , Tom Lane wrote:
> "Petro Meier" <Petro85(at)gmx(dot)de> writes:
>> INSERT INTO "testtable" ("ID", "BinaryContents") values (1,
>> E'\xea2abd8ef3');
>> returns "invalid byte sequence".
>
>> '\xea2abd8ef3' is the string delivered by the PG 9.1 Beta3 server
>> when calling PQescapeByteaConn(). It cannot be further processed by the
>> server itself afterwards! There is a leading '\' missing.
>
> No, there isn't. What you are doing wrong is prepending an E to the
> literal. You should not be doing that, neither in 9.1 nor any previous
> version.

Just to clarify what's going on here, in case the OP is still puzzled.

Postgres supports both a legacy mode where backslashes serve as an escape
character in single-quotes strings, and an SQL standard-compliant mode where
they don't. The mode is chosen by setting the GUC standard_conforming_strings
to either on of off. Independent of the current standard_conforming_strings
setting, once can always force a strings to be interpreted with legacy
semantics (i.e. with backslash as an escape character) by prefixing the string
literal with E.

Thus, assuming that standard_conforming_strings is set to on, a string containing
exactly one backslash can be written as either
'\' or
E'\\',
while with standard_conforming_strings set to off, you'd have to use
'\\' or
E'\\'

PQescapeByteaConn() emits one backslash if it detects that
standard_conforming_strings is set to "on" for the given connection, and two if
it detects "off". The string is thus always correctly interpreted by the backend as
long as you *don't* prefix it with E. If you do, you force the backend to always
interpret it with legacy semantics. Which of course causes trouble if
standard_conforming_strings is set to "on", because then PQescapeByteAConn()'s
expectation of the backend's behaviour (standard mode) and it's actual behaviour
(legacy mode) no longer match.

The reason that things appeared to work for you on 9.0 is that all versions before
9.1 have standard_conforming_strings set to "off" by default. If you try your code
on 9.0, but with standard_conforming_strings set to "on", you'll observe the same
breakage you observe on 9.1

Exactly the same is true for PQescapeStringConn().

best regards,
Florian Pflug