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

[Andrew Bruce]

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 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20130829/b29c9370/attachment.htm>