Обсуждение: Backslash as ordinary char vs. not; set via a connection/session variable

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

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

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

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

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

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