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