<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7654.12">
<TITLE>Re: [pgpool-general: 2089] Re: standard_conforming_strings broken by PgPool?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Ah, interesting. It looks like changing the parameter requires a DB<BR>
restart (simply reloading the config appears not to work despite the log<BR>
file indicating the parameter change)...<BR>
<BR>
I'll report back after I get permission to restart the back-ends<BR>
(unfortunately, the servers are in production)...<BR>
<BR>
----------<BR>
2572 2013-09-02 10:25:27.944 BST LOG: received SIGHUP, reloading<BR>
configuration files<BR>
2572 2013-09-02 10:25:27.945 BST LOG: parameter<BR>
"standard_conforming_strings" changed to "off"<BR>
----------<BR>
<BR>
Thanks for the pointer.<BR>
<BR>
Regards,<BR>
<BR>
Andy Bruce<BR>
<BR>
-----Original Message-----<BR>
From: Tatsuo Ishii <ishii@postgresql.org><BR>
To: andrew.bruce@hslmobile.com<BR>
Cc: pgpool-general@pgpool.net<BR>
Subject: Re: [pgpool-general: 2089] Re: standard_conforming_strings<BR>
broken by PgPool?<BR>
Date: Sat, 31 Aug 2013 06:51:19 +0900 (JST)<BR>
<BR>
> Hi Ishii san,<BR>
><BR>
> You mentioned "turn off standard_confirm_string in postgresql.conf". I<BR>
> take it you mean the parameter "standard_conforming_strings"?<BR>
<BR>
Yes.<BR>
<BR>
> If so, I already have that set to off in the postgresql config on each<BR>
> server in the cluster. this works when connecting directly to a database<BR>
> in the cluster, but not through PgPool-II.<BR>
<BR>
Strange. Workes for me...<BR>
<BR>
[t-ishii@localhost test]$ psql -p 11001 test<BR>
Pager usage is off.<BR>
psql (9.2.4)<BR>
Type "help" for help.<BR>
<BR>
test=# show pool_version;<BR>
pool_version <BR>
---------------------<BR>
3.3.0 (tokakiboshi)<BR>
(1 row)<BR>
<BR>
test=# show standard_conforming_strings;<BR>
standard_conforming_strings<BR>
-----------------------------<BR>
off<BR>
(1 row)<BR>
<BR>
test=# SELECT '\'';<BR>
WARNING: nonstandard use of \' in a string literal<BR>
LINE 1: SELECT '\'';<BR>
^<BR>
HINT: Use '' to write quotes in strings, or use the escape string syntax (E'...').<BR>
?column?<BR>
----------<BR>
'<BR>
(1 row)<BR>
<BR>
test=#<BR>
--<BR>
Tatsuo Ishii<BR>
SRA OSS, Inc. Japan<BR>
English: <A HREF="http://www.sraoss.co.jp/index_en.php">http://www.sraoss.co.jp/index_en.php</A><BR>
Japanese: <A HREF="http://www.sraoss.co.jp">http://www.sraoss.co.jp</A><BR>
<BR>
> Regards,<BR>
><BR>
> Andy Bruce<BR>
><BR>
> -----Original Message-----<BR>
> From: Tatsuo Ishii <ishii@postgresql.org><BR>
> To: andrew.bruce@hslmobile.com<BR>
> Cc: pgpool-general@pgpool.net<BR>
> Subject: Re: [pgpool-general: 2089] Re: standard_conforming_strings<BR>
> broken by PgPool?<BR>
> Date: Fri, 30 Aug 2013 16:31:31 +0900 (JST)<BR>
><BR>
> This is a known problem.<BR>
><BR>
> pgpool-II currently fails to forward "parameter status" packet to<BR>
> clients, including psql (actually libpq, the clibrary for PostgreSQL<BR>
> protocol linked to psql).<BR>
><BR>
> Libpq does a special hack inside to deal with<BR>
> standard_confirming_strings. If backend reply back a parameter status<BR>
> packet describing "standard_confirming_strings is now off" (this could<BR>
> happen when you send command "set standard_confirming_strings to<BR>
> off"), it remembers it and behaves differently later on. Unfortunately<BR>
> pgpool-II does not forward the packet to psql. So libpq doesn't know<BR>
> that.<BR>
><BR>
> A workaround for this is, turn off standard_confirm_string in<BR>
> postgresql.conf.<BR>
><BR>
> Another workaround is, using PGOPTIONS environment variable.<BR>
><BR>
> PGOPTIONS="-c standard_conforming_strings=off" psql...<BR>
><BR>
> The reason why those workarounds work is, pgpool-II can forward the<BR>
> packet in the connection phase.<BR>
> --<BR>
> Tatsuo Ishii<BR>
> SRA OSS, Inc. Japan<BR>
> English: <A HREF="http://www.sraoss.co.jp/index_en.php">http://www.sraoss.co.jp/index_en.php</A><BR>
> Japanese: <A HREF="http://www.sraoss.co.jp">http://www.sraoss.co.jp</A><BR>
><BR>
>> Hi Folks,<BR>
>><BR>
>> Can anyone else confirm or deny the issue I outlined below?<BR>
>><BR>
>> I should add that this is on PgPool-II Version 3.3 with PostgreSQL 9.2.4 backends...<BR>
>><BR>
>> I'd really like a solution (or work-around) - other than rewriting the client applications! ;-)<BR>
>><BR>
>> Regards,<BR>
>><BR>
>> Andy Bruce<BR>
>><BR>
>> Date: Wed, 21 Aug 2013 16:27:49 +0100<BR>
>> From: "Andrew Bruce" <andrew.bruce@hslmobile.com><BR>
>> To: <pgpool-general@pgpool.net><BR>
>> Subject: [pgpool-general: 2061] standard_conforming_strings broken by<BR>
>> PgPool?<BR>
>><BR>
>> Hi folks,<BR>
>><BR>
>> I'm looking at an interesting problem:<BR>
>><BR>
>> I have client software that needs to use the backslash character as an<BR>
>> escape character. For a regular PostgreSQL database, this is not a<BR>
>> problem as the parameter 'standard_conforming_strings' can be turned<BR>
>> 'off' and queries such as:<BR>
>><BR>
>> SELECT '\'';<BR>
>><BR>
>> will correctly return:<BR>
>><BR>
>> ?column?<BR>
>> ----------<BR>
>> '<BR>
>> (1 row)<BR>
>><BR>
>> However, if I try to do this on the cluster, through PgPool, despite<BR>
>> turning off standard_conforming_strings, the backslash is still treated<BR>
>> as a literal string character, so the result of the above query becomes:<BR>
>><BR>
>> postgres=# select '\'';<BR>
>> postgres'#<BR>
>><BR>
>> indicating that the CLI suspects that I have failed to close the single<BR>
>> quotes...<BR>
>><BR>
>> To reproduce:<BR>
>><BR>
>> Connect to the DB cluster using the PgPool IP address:<BR>
>> --- Begin Output from PgPool Connection ---<BR>
>> sampledb=# select '\'';<BR>
>> sampledb'# ^C<BR>
>> sampledb=# set standard_conforming_strings = 'off';<BR>
>> SET<BR>
>> sampledb=# select '\'';<BR>
>> sampledb'# ^C<BR>
>> sampledb=# \q<BR>
>> --- End Output from PgPool Connection ---<BR>
>><BR>
>><BR>
>> Connect directly to one of the PostgreSQL database servers in the<BR>
>> cluster, bypassing PgPool:<BR>
>> --- Begin Output from Dedicated DB Connection ---<BR>
>> sampledb=# select '\'';<BR>
>> sampledb'# ^C<BR>
>> sampledb=# set standard_conforming_strings = 'off';<BR>
>> SET<BR>
>> postgres=# select '\'';<BR>
>> WARNING: nonstandard use of \' in a string literal<BR>
>> LINE 1: select '\'';<BR>
>> ^<BR>
>> HINT: Use '' to write quotes in strings, or use the escape<BR>
>> string syntax (E'...').<BR>
>> ?column?<BR>
>> ----------<BR>
>> '<BR>
>> (1 row)<BR>
>> sampledb=# \q<BR>
>> --- End Output from Dedicated DB Connection ---<BR>
>><BR>
>> Is this a known issue, or is there something I'm missing here?<BR>
>><BR>
>> Thanks in advance of any helpful advice!<BR>
>><BR>
>> Regards,<BR>
>><BR>
>> Andy Bruce<BR>
><BR>
> ______________________________________________________________________<BR>
> This email has been scanned by the Symantec Email Security.cloud service.<BR>
> For more information please visit <A HREF="http://www.symanteccloud.com">http://www.symanteccloud.com</A><BR>
> ______________________________________________________________________<BR>
<BR>
______________________________________________________________________<BR>
This email has been scanned by the Symantec Email Security.cloud service.<BR>
For more information please visit <A HREF="http://www.symanteccloud.com">http://www.symanteccloud.com</A><BR>
______________________________________________________________________<BR>
</FONT>
</P>
</BODY>
</HTML>