PQescapeByteaConn - returns wrong string for PG9.1 Beta3

On Jul27, 2011, at 08:51 , Petro Meier wrote:

If I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server, this function returns (e.g.) "\xea2abd8ef31...(and so on.)...".
Here the problem: there should be a second backslash in the prefix. The SQL Statement which uses this string (INSERT statement in my case) returns with an error ("Invalid byte sequence..."). If I add the second backslash manually everything works fine.
When connecting to a PG9.0 server and using this function, the return value is correct (with two backslashes): "\\xea2abd8ef31...( and so on.)...".
This should be a bug in PG9.1 Beta3

Sounds as if PQescapeByteaConn() is confused about whether standard_conforming_strings is on or off. What value does that setting have in your 9.0 and 9.1 instances?

BTW, I think 9.1 is the first release where that settings defaults to "on", so maybe that adds to PQescapeByteaConn()'s confusion. In theory it shouldn't since PQescapeByteaConn() should simply detect the server's setting and react accordingly, but who knows...

best regards,
Florian Pflug

Excerpts from Petro Meier's message of mié jul 27 02:51:22 -0400 2011:

If  I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server,
this function returns (e.g.) "\xea2abd8ef31...(and so on.)...".

Here the problem: there should be a second backslash in the prefix.
The SQL Statement which uses this string (INSERT statement in my case)
returns with an error ("Invalid byte sequence..."). If I add the second
backslash manually everything works fine.

You're just being bitten by the fact that the
standard_conforming_strings setting changed its default from false to
true. If you want the old behavior, you can just flip the switch, but
the recommended action is to change your expectations. You can use E''
if you want backslashes to continue working without changing the switch.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

Excerpts from Petro Meier's message of mié jul 27 02:51:22 -0400 2011:

If  I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server,
this function returns (e.g.) "\xea2abd8ef31...(and so on.)...".

Here the problem: there should be a second backslash in the prefix.

You're just being bitten by the fact that the
standard_conforming_strings setting changed its default from false to
true.

Well, the question is why is it actually failing for him. AFAICS the
value being emitted is correct for the 9.1 server. Perhaps we need to
see a complete example...

regards, tom lane

On Jul27, 2011, at 20:05 , Alvaro Herrera wrote:

Excerpts from Petro Meier's message of mié jul 27 02:51:22 -0400 2011:

If I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server,
this function returns (e.g.) "\xea2abd8ef31...(and so on.)...".

Here the problem: there should be a second backslash in the prefix.
The SQL Statement which uses this string (INSERT statement in my case)
returns with an error ("Invalid byte sequence..."). If I add the second
backslash manually everything works fine.

You're just being bitten by the fact that the
standard_conforming_strings setting changed its default from false to
true. If you want the old behavior, you can just flip the switch, but
the recommended action is to change your expectations. You can use E''
if you want backslashes to continue working without changing the switch.

Hm, but PQescapeByteaConn() shouldn't produce a literal that the server
later rejects, no matter what standard_conforming_strings is set to.

It looks like PQescapeByteaConn() does the right thing here, though -
it doesn't escape the backslash in it's result when dealing with 9.1,
presumably because that server has wstandard_conforming_strings set to on.
But why then is the server rejecting the result?

The only way I can see that make that happend would be to prefix the
string returned by PQescapeByteaConn() with 'E'.

@OP: Could you post the code fragment that causes the error?

best regards,
Florian Pflug

On 07/27/2011 02:05 PM, Alvaro Herrera wrote:

Excerpts from Petro Meier's message of mié jul 27 02:51:22 -0400 2011:

If I use PQescapeByteaConn() for a conenction to a PG9.1 Beta3 server,
this function returns (e.g.) "\xea2abd8ef31...(and so on.)...".

Here the problem: there should be a second backslash in the prefix.
The SQL Statement which uses this string (INSERT statement in my case)
returns with an error ("Invalid byte sequence..."). If I add the second
backslash manually everything works fine.

You're just being bitten by the fact that the
standard_conforming_strings setting changed its default from false to
true. If you want the old behavior, you can just flip the switch, but
the recommended action is to change your expectations. You can use E''
if you want backslashes to continue working without changing the switch.

Or even better don't interpolate it into SQL at all, but use a statement
placeholder.

cheers

andrew