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

On Wed, Aug 03, 2011 at 03:19:06PM +0200, Petro Meier wrote:
> Normal        0                        21                        false      
>   false        false                DE        X-NONE        X-NONE          
>                                                                             
>                           MicrosoftInternetExplorer4                        
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                                                                             
>                 
> 
> Please let me clarify the bug:        
> 
>          CREATE TABLE "testtable"        
> 
> (        
> 
>   "ID" integer NOT NULL,        
> 
>   "BinaryContents" bytea        
> 
> );        
> 
>          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.         
> 
>          When calling the function for a PG 9.0.1 server, then the result 
> (correctly) is '\\xea2abd8ef3' (with a double-backslash!), and then the 
> insert works fine, both, with PG9.1 Beta3 and PG9.0.1        
> 
>          It is a serious issue, as it will break all existing PostgreSQL 
> applications that deal with binary contents and use PQescapeByteaConn().    
>     
> 
>          Best regards        
> 
> Petro    

That looks correct for the new default for SQL conforming strings set to
true in 9.1+. The command you should be using is:

INSERT INTO "testtable" ("ID", "BinaryContents") values (1, '\xea2abd8ef3');

Regards,
Ken