[pgpool-general: 2099] Re: standard_conforming_strings broken by PgPool?

[Tatsuo Ishii]

> 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
> ______________________________________________________________________