Grokbase › Groups › PostgreSQL › pgsql-general › July 2006
FAQ
Tom and folks,

Will it be possible to set this more standard backslash handling
behavior -- and possibly similar conformance modes... in a way similar
to how mysql allows? They allow one to issue commands on the connection
like:

SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES,IGNORE_SPACE,ANSI'

The advantage to this is that ISPs, etc can, by default, run the
database with the old/incorrect mode (which is more compatible with the
correspondingly legacy/broken apps.. and for newer apps to issue that
command to make the DB act more standards compliant..

I personally have no need for the old backslash behavior (currently
using databases/queries that don't use it), but sometimes one may have
to coexist in a shared server environment.

(for those who may not know,) the actual overhead of issuing that
command (on the client side at least) before each exec is low, since
connection pools for example usually already need to issue a 'ping'
command (e.g SELECT 1) to test the connection before executing on it. So
if the pool/api allows the user to define connection-setup DML, that
conveniently takes the place of the ping anyway (if the API supports).
And, given that the last user of a pooled connection may have tweaked
the settings, it's prob. best to reset them before each exec.

I seem to have lost the URL that describes the upcoming string
conformance mode.. can't find the 4.1.2.1 section that John Gunther
mentioned.. does anyone have that handy?

Thank you,

ken

Search Discussions

  • Alvaro Herrera at Jul 27, 2006 at 8:29 pm ⇧

    Ken Johanson wrote:
    Tom and folks,

    Will it be possible to set this more standard backslash handling
    behavior -- and possibly similar conformance modes... in a way similar
    to how mysql allows? They allow one to issue commands on the connection
    like:

    SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES,IGNORE_SPACE,ANSI'
    Is this something you cannot do with ALTER ROLE SET foo=bar ?

    --
    Alvaro Herrera http://www.CommandPrompt.com/
    PostgreSQL Replication, Consulting, Custom Development, 24x7 support
  • Stefan Kaltenbrunner at Jul 27, 2006 at 8:38 pm ⇧

    Ken Johanson wrote:
    Tom and folks,

    Will it be possible to set this more standard backslash handling
    behavior -- and possibly similar conformance modes... in a way similar
    to how mysql allows? They allow one to issue commands on the connection
    like:

    SET SESSION sql_mode = 'NO_BACKSLASH_ESCAPES,IGNORE_SPACE,ANSI'

    The advantage to this is that ISPs, etc can, by default, run the
    database with the old/incorrect mode (which is more compatible with the
    correspondingly legacy/broken apps.. and for newer apps to issue that
    command to make the DB act more standards compliant..
    postgresql can do that in an even more powerful way - but people tend to
    not notice much of it in your case that would be:

    ALTER ROLE foo SET standard_conforming_strings='off'

    or even:

    ALTER DATABASE bar SET standard_conforming_strings='off'

    you can do that for nearly all GUCs (like
    logging,client_encoding,search_path,....)


    Stefan
  • Ken Johanson at Jul 27, 2006 at 8:52 pm ⇧

    Stefan Kaltenbrunner wrote:


    postgresql can do that in an even more powerful way - but people tend to
    not notice much of it in your case that would be:

    ALTER ROLE foo SET standard_conforming_strings='off'

    or even:

    ALTER DATABASE bar SET standard_conforming_strings='off'

    you can do that for nearly all GUCs (like
    logging,client_encoding,search_path,....)


    Stefan
    Stefan and Alvaro,

    Thank you!!! Yes, that is the feature I'd like... and yes, setting it on
    a per role or per database level is something I personally would prefer
    over the connection level. But, is there also a way to set it on the
    connection? Just because, one can imagine scenarios where two APIs share
    the same role & database, but one API forces backslashes 'on' during its
    statement-prepare.... just playing devil's advocate :-)

    So is this 'standard_conforming_strings' variable already set-able in a
    recent build, at the role or db level? Or will that need to wait for 8.2?

    Thanks again!!!!!!

    ken
  • Stefan Kaltenbrunner at Jul 27, 2006 at 9:02 pm ⇧

    Ken Johanson wrote:
    Stefan Kaltenbrunner wrote:
    postgresql can do that in an even more powerful way - but people tend to
    not notice much of it in your case that would be:

    ALTER ROLE foo SET standard_conforming_strings='off'

    or even:

    ALTER DATABASE bar SET standard_conforming_strings='off'

    you can do that for nearly all GUCs (like
    logging,client_encoding,search_path,....)


    Stefan
    Stefan and Alvaro,

    Thank you!!! Yes, that is the feature I'd like... and yes, setting it on
    a per role or per database level is something I personally would prefer
    over the connection level. But, is there also a way to set it on the
    connection? Just because, one can imagine scenarios where two APIs share
    the same role & database, but one API forces backslashes 'on' during its
    statement-prepare.... just playing devil's advocate :-)
    foo=# create table backslash(baz text);
    CREATE TABLE
    foo=# set standard_conforming_strings to on;
    SET
    foo=# insert into backslash values ('\\');
    INSERT 0 1
    foo=# set standard_conforming_strings to off;
    SET
    foo=# insert into backslash values ('\\');
    WARNING: nonstandard use of \\ in a string literal
    LINE 1: insert into backslash values ('\\');
    ^
    HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
    INSERT 0 1
    foo=# select * from backslash;
    baz
    -----
    \\
    \
    (2 rows)


    like that ? :-)

    So is this 'standard_conforming_strings' variable already set-able in a
    recent build, at the role or db level? Or will that need to wait for 8.2?
    it's already in -HEAD and will therefor be in 8.2 when that gets released.


    Stefan
  • Ken Johanson at Jul 27, 2006 at 9:15 pm ⇧

    Stefan Kaltenbrunner wrote:
    foo=# create table backslash(baz text);
    CREATE TABLE
    foo=# set standard_conforming_strings to on;
    SET
    foo=# insert into backslash values ('\\');
    INSERT 0 1
    foo=# set standard_conforming_strings to off;
    SET
    foo=# insert into backslash values ('\\');
    WARNING: nonstandard use of \\ in a string literal
    LINE 1: insert into backslash values ('\\');
    ^
    HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
    INSERT 0 1
    foo=# select * from backslash;
    baz
    -----
    \\
    \
    (2 rows)


    like that ? :-)

    Yes - that is the eye candy I'm looking for. Anxiously looking forward
    to 8.2 :-)

    ken

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJul 27, '06 at 6:17p
activeJul 27, '06 at 9:15p
posts6
users3
websitepostgresql.org
irc#postgresql

3 users in discussion

Ken Johanson: 3 posts Stefan Kaltenbrunner: 2 posts Alvaro Herrera: 1 post

Content

People

Support

Translate

site design / logo © 2024 Grokbase