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

[Tatsuo Ishii]

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