Thread: Possible to run the server with ANSI/ISO string escapeing instead of C-style escapes?

Greetings,

Does anyone know if it will be possible to run the server with ANSI/ISO
string escaping instead of C-style escapes? The C style escaping is a
shoot-down for our adoption of postgres, since its non-standard.

Thanks,
ken

More specifically, I'd like to disable the non-standard backslash
escaping behavior of the server, so that backslashes aren't discarded, like:

insert into table values ('This won''t work when inserting a \
character'); -> 'This won't work when inserting a  character'

Instead I'd like for only single quotes (apostrophes) to server as the
escape character (for themselves), as in the iso/ansi recommendations
-so that the server behaves the same as Oracle, ms, and most other
databases in this regard.

I am living testament to all the users who switched from the 'big'
databases to pg and had the unpleasant surprise of having lost all the
backslashes chars -as well as having to tweak apps to get them to work.

If nothing else, a VERY prominently placed warning on the docs front
page should warn new users of the nonstandard behavior, imo. This can be
a life saver.

ken

Ken Johanson wrote:
> Greetings,
>
> Does anyone know if it will be possible to run the server with ANSI/ISO
> string escaping instead of C-style escapes? The C style escaping is a
> shoot-down for our adoption of postgres, since its non-standard.

Not yet, but we have a TODO item:

    * Allow backslash handling in quoted strings to be disabled for
      portability

      The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not
      SQL-spec compliant, so allow such handling to be disabled.

Uh, what is ANSI/ISO escaping actually?  I assume you mean only
supporting '' for literal quotes rather than \' too.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote: <blockquote cite="mid200502250053.j1P0rDm09001@candle.pha.pa.us" type="cite"><pre wrap="">Ken
Johansonwrote: </pre><blockquote type="cite"><pre wrap="">Greetings,
 

Does anyone know if it will be possible to run the server with ANSI/ISO 
string escaping instead of C-style escapes? The C style escaping is a 
shoot-down for our adoption of postgres, since its non-standard.   </pre></blockquote><pre wrap="">
Not yet, but we have a TODO item:
* Allow backslash handling in quoted strings to be disabled for  portability  The use of C-style backslashes (.e.g. \n,
\r)in quoted strings is not  SQL-spec compliant, so allow such handling to be disabled.
 

Uh, what is ANSI/ISO escaping actually?  I assume you mean only
supporting '' for literal quotes rather than \' too.
 </pre></blockquote> Yes Sir. Being able to disable the backslash-escaping is the desired operation. In circles of
businessdeciding wether to move to opensource databases this is the silently used excuse (by the critics) to dismiss pg
sinceits arguably so fundamental and somewhat dangerous (data loss). Getting pg to behave 'normally' would silence this
excuse.<br/><br /> Is there anything I can do to help move this up the todo list?<br /><br /> Thanks,<br /> ken<br /> 

> Does anyone know if it will be possible to run the server with ANSI/ISO
> string escaping instead of C-style escapes? The C style escaping is a
> shoot-down for our adoption of postgres, since its non-standard.
>
>
> Not yet, but we have a TODO item:
>
>         * Allow backslash handling in quoted strings to be disabled for
>           portability
>
>           The use of C-style backslashes (.e.g. \n, \r) in quoted strings is not
>           SQL-spec compliant, so allow such handling to be disabled.
>
> Uh, what is ANSI/ISO escaping actually?  I assume you mean only supporting
> '' for literal quotes rather than \' too.
>
> Yes Sir. Being able to disable the backslash-escaping is the desired
> operation. In circles of business deciding wether to move to opensource
> databases this is the silently used excuse (by the critics) to dismiss
> pg since its arguably so fundamental and somewhat dangerous (data loss).
> Getting pg to behave 'normally' would silence this excuse. Is there
> anything I can do to help move this up the todo list?

Uh, yea, this is going to require quite a bit of discussion in the
group, and I am concerned how it will affect other apps using
PostgreSQL.  (The mode isn't going to be useful if it breaks plug-in
extensions and stuff.)

I think COPY is always going to need to use backslashes.  There is no
other good way to handle special characters and stuff, but I don't see
people complaining that has to be portable.

I think most of it is done in parser/scan.l if you want to hack in there
and get a test implementation working and send in a patch.  We can work
on the infrastructure to turn it on and off.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Does anyone know if it will be possible to run the server with ANSI/ISO
>> string escaping instead of C-style escapes? The C style escaping is a
>> shoot-down for our adoption of postgres, since its non-standard.

> Uh, yea, this is going to require quite a bit of discussion in the
> group, and I am concerned how it will affect other apps using
> PostgreSQL.  (The mode isn't going to be useful if it breaks plug-in
> extensions and stuff.)

The hard part of this isn't turning off backslash quoting; the code
changes to do that would be pretty trivial.  The hard part is not
breaking vast quantities of existing client code.  After our experience
with autocommit, no one is going to want to solve it with a GUC variable
that can be flipped on and off at random.  That would make the
compatibility problems that autocommit caused look like a day at the
beach :-(

I don't actually know a way to solve this that wouldn't impose
impossible amounts of pain on our existing users, and I'm afraid that
I rank that consideration higher than acquiring new users who won't
consider changing their own code.

If you can show me a way to provide this behavior without risk of
breaking existing code, I'm all ears.

            regards, tom lane

>>Uh, yea, this is going to require quite a bit of discussion in the
>>group, and I am concerned how it will affect other apps using
>>PostgreSQL.  (The mode isn't going to be useful if it breaks plug-in
>>extensions and stuff.)
>>
>>
>
>The hard part of this isn't turning off backslash quoting; the code
>changes to do that would be pretty trivial.  The hard part is not
>breaking vast quantities of existing client code.  After our experience
>with autocommit, no one is going to want to solve it with a GUC variable
>that can be flipped on and off at random.  That would make the
>compatibility problems that autocommit caused look like a day at the
>beach :-(
>
>I don't actually know a way to solve this that wouldn't impose
>impossible amounts of pain on our existing users, and I'm afraid that
>I rank that consideration higher than acquiring new users who won't
>consider changing their own code.
>
>If you can show me a way to provide this behavior without risk of
>breaking existing code, I'm all ears.
>
>            regards, tom lane
>
>
I feel somewhat confident (very actually) that a config option that
disabled the backslash behavior globally(*) would be acceptable, BUT
leave the current backslash behavior turned on by default so that
current users are not impacted at all. Only a conscientious decision by
the db admin to turn it on could cause problems, but _only_ if he/she
didn't warn all his/her users beforehand of the impending change and its
consequences (rtm).

(*Or if it's possible, provide the no-backslash config on a per-catalog
basis perhaps? -or even per-user/group?, --that would allow individuals
to use the legacy mode until they choose otherwise)

I can say, that I for one would enable the no-backslash config option
out of the box -globally -so that we can start using pg now without any
more upper managerial concerns/excuses about language/interface
compliance..I can also say that (what we already know) the longer we
wait to provide the 'right' option, the *more* legacy apps (and
interfaces) will be built around it and consequently suffer when the
need for change eventually comes (almost wholly caused by interop
concerns). And market gain is being hurt now by this incompatibility
with commercial offerings; that's an unfortunate fact.

Better to nip it in the bud sooner than later, imo.

thoughts,
ken

On Sun, 2005-02-27 at 18:25 -0700, Ken Johanson wrote:
> >>Uh, yea, this is going to require quite a bit of discussion in the
> >>group, and I am concerned how it will affect other apps using
> >>PostgreSQL.  (The mode isn't going to be useful if it breaks plug-in
> >>extensions and stuff.)
> >>
> >>
> >
> >The hard part of this isn't turning off backslash quoting; the code
> >changes to do that would be pretty trivial.  The hard part is not
> >breaking vast quantities of existing client code.  After our experience
> >with autocommit, no one is going to want to solve it with a GUC variable
> >that can be flipped on and off at random.  That would make the
> >compatibility problems that autocommit caused look like a day at the
> >beach :-(
> >
> >I don't actually know a way to solve this that wouldn't impose
> >impossible amounts of pain on our existing users, and I'm afraid that
> >I rank that consideration higher than acquiring new users who won't
> >consider changing their own code.
> >
> >If you can show me a way to provide this behavior without risk of
> >breaking existing code, I'm all ears.
> >
> >            regards, tom lane
> >
> >
> I feel somewhat confident (very actually) that a config option that
> disabled the backslash behavior globally(*) would be acceptable, BUT
> leave the current backslash behavior turned on by default so that
> current users are not impacted at all. Only a conscientious decision by
> the db admin to turn it on could cause problems, but _only_ if he/she
> didn't warn all his/her users beforehand of the impending change and its
> consequences (rtm).
>

I'm a little worried about PostgreSQL having the same problems as PHP.
In PHP, every time you want to download an application, you never see
"This application works on php 4+". Instead, you see "This application
works on php4+ with the following config options set <long list>".
Sometimes these applications have conflicting requirements. From an
administrator's standpoint, it's a mess.

In PostgreSQL I think it would actually be much worse. Right now many
applications build a PostgreSQL layer, but will they build two? I think
this would cause a divide in the application support (some for config
option A some for config option B) in the already smaller-than-we'd-like
set of software that supports PostgreSQL.

Regards,
    Jeff Davis

Martijn van Oosterhout wrote: <blockquote cite="mid20050228091645.GA27668@svana.org" type="cite"><pre wrap="">On Sun,
Feb27, 2005 at 06:25:18PM -0700, Ken Johanson wrote: </pre><blockquote type="cite"><pre wrap="">I feel somewhat
confident(very actually) that a config option that 
 
disabled the backslash behavior globally(*) would be acceptable, BUT 
leave the current backslash behavior turned on by default so that 
current users are not impacted at all. Only a conscientious decision by 
the db admin to turn it on could cause problems, but _only_ if he/she 
didn't warn all his/her users beforehand of the impending change and its 
consequences (rtm).   </pre></blockquote><pre wrap="">
It's not just a question of warning the users, all interfaces to the
database will instantly break. For example: JDBC, Perl DBI, PHP PEAR
etc. They will continue to send queries with the backslashes embedded.
These interfaces would need to be modified to handle both situations
and detect which situation they're dealing with. </pre></blockquote> All interfaces will NOT break IF the legacy db
behaviorstays its default. This means NONE of the current users would be hurt until they start experimenting with the
newoption. Yes, the built in prepared stmt components of those interfaces will still add the backslash by default and
breakqueries for legacy drivers, but this is not an issue for the straight-through query/update exec(s) calls, and
preparedstmt users can hack the Prepared stmts behavior until the same option is officially supported in the driver
also(probably by auto-detecting what the DB expects its backslashes to look like).<br /><br /> Like I said, users
shouldbe warned beforehand, that they need to get a hacked or official driver update, if the dbadmin decides to turn on
the'new' mode. Seems prudent to me.<br /><blockquote cite="mid20050228091645.GA27668@svana.org" type="cite"><pre
wrap="">
The thing is all these interfaces handle the quoting transparently for
you, so the code is portable already. What you're complaining about is
that you have your own query marshalling and it is not portable. </pre></blockquote> As you say, the portability you
describeREQUIRES the use of prepared stmts type queries - but one CANNOT issue a portable query, say,
jdbc:stmt.execQuery()or execute update. Those "lower-level" calls need to be portable where string escaping is
concerned,and they are not. They arguably break both the JDBC spec and SQL spec since some additional, nonstandard
stringpreprocessing is REQUIRED for them to work. And not to repeat what you already know, but Prepared stmts are not
suitableor available for certain query types (performance sometimes better w/o PS, dynamic query building, batch
queries,etc) and drivers (small foot print ones).<br /><blockquote cite="mid20050228091645.GA27668@svana.org"
type="cite"><prewrap="">
 
Incidently, if you disable the backslash quoting, how does one enter
raw binary data including NUL (\0) characters?  </pre></blockquote> I'm not sure if you're talking about API interfaces
orshell, or both. If shell, a piped sql-compliant escape processor would clearly be needed.<br /><br /> As for APIs, I
suspectthe current design of each driver handles nulls and the like, stems from how the underlying db protocol was
originallybuilt --so they use either run length encoding (chunking), boundary-delimiting, or are null terminated
(whicheverPG uses natively, I don't know). In any case the very lowest level of the db interface can translate as
necessary(doubling the backslashes if necessary), but queries should be able to issue any byte, including nulls, with
theonly requirement that apostrophes are the string-escape character, for themselves. In other words nulls should need
nospecial treatment from the query interface layer.<br /><blockquote cite="mid20050228091645.GA27668@svana.org"
type="cite"><prewrap="">
 
The only viable solution I can think of is that it is set at
*connection* time (maybe extra parameters), and unchangable for the
rest of the session. This means that unmodified client interfaces won't
see a difference.
 </pre></blockquote> Yes, sessions (connections) could work, and also perhaps per-user or group, wherein the db the
escapehanding is handled the old or new way on a per-user basis.<br /><blockquote
cite="mid20050228091645.GA27668@svana.org"type="cite"><pre wrap=""></pre><blockquote type="cite"><pre wrap="">I can
say,that I for one would enable the no-backslash config option 
 
out of the box -globally -so that we can start using pg now without any 
more upper managerial concerns/excuses about language/interface 
compliance..I can also say that (what we already know) the longer we 
wait to provide the 'right' option, the *more* legacy apps (and 
interfaces) will be built around it and consequently suffer when the 
need for change eventually comes (almost wholly caused by interop 
concerns). And market gain is being hurt now by this incompatibility 
with commercial offerings; that's an unfortunate fact.   </pre></blockquote><pre wrap="">
Even if PostgreSQL implements this now, you will have to wait for new
versions of any client libraries before it's usable. See the autocommit
disaster for an example why people are not rushing into this...
 </pre></blockquote> I fully agree. I can see waiting at LEAST 1-3 months before the db itself has changes committed
foralpha testing, but that SURE BEATS procrastination --which means years worth of more apps and interfaces being built
aroundthe 'backslash' (again, not everyone uses prepared statement - its not required and not suitable for all
situations).Conversely, the very day the server has an alpha build supporting the no-backslash mode is the very _first_
daythat the jdbc/perl driver developers can start testing against the changes. Until then all parties are just sitting
still.<br/><br /> Incidentally, I believe PG is now one of the last widely used DBs to not offer the standard escape
behavior;the other popular OS db now has this option (albeit buggy), at the request of a very large software company
thatwanted to adopt their db no less...<br /><br /> Let me just finish by saying that I'm uncomfortable and sorry to be
makingwaves like I am, but I feel it's in everyones best interest long term to start making this change now and teach
theusers sooner than later - this topic is has been a real handicap for my own adoption of the database (peer-wise),
andthe problem will only fester if nothing is done. Pain now or pain later.<br /><br /> ken<br /> 

>I'm a little worried about PostgreSQL having the same problems as PHP.
>In PHP, every time you want to download an application, you never see
>"This application works on php 4+". Instead, you see "This application
>works on php4+ with the following config options set <long list>".
>Sometimes these applications have conflicting requirements. From an
>administrator's standpoint, it's a mess.
>
>In PostgreSQL I think it would actually be much worse. Right now many
>applications build a PostgreSQL layer, but will they build two? I think
>this would cause a divide in the application support (some for config
>option A some for config option B) in the already smaller-than-we'd-like
>set of software that supports PostgreSQL.
>
>Regards,
>    Jeff Davis
>
>
There's certainly two perspectives to this. The one you present is
certainly valid, but consider the bigger picture...

"This application requires the following databases: Oracle versionX, MY
SQL version X, Mysql version 5.2 with the no-backslashes option, UltraDB
version x"

Notice the lack of PG - some apps - most notably commercial ones - will
automatically shoot it down if it cant meet certain language
requirements. The database itself could meet the latest SQL03 (or
whatever we're up to) specs for Object Relational stuff, etc to the tee.
The JDBC driver could meet the JDBC spec to the tee for transaction
support, etc - but this one low level problem is a total show stopper,
because it plainly breaks queries sent through various interfaces in
various drivers.

Besides, the version-deprecation / version requirements you mention
exists in every piece of software I've even seen. Sometime they're okay
with a really old version, sometime only the newest will do. This is the
very argument for getting PG to offer an (use-optional) escape behavior
inline with the rest - to mitigate these version requirements down the road.

Thoughts,
ken

On Sun, Feb 27, 2005 at 06:25:18PM -0700, Ken Johanson wrote:
> I feel somewhat confident (very actually) that a config option that
> disabled the backslash behavior globally(*) would be acceptable, BUT
> leave the current backslash behavior turned on by default so that
> current users are not impacted at all. Only a conscientious decision by
> the db admin to turn it on could cause problems, but _only_ if he/she
> didn't warn all his/her users beforehand of the impending change and its
> consequences (rtm).

It's not just a question of warning the users, all interfaces to the
database will instantly break. For example: JDBC, Perl DBI, PHP PEAR
etc. They will continue to send queries with the backslashes embedded.
These interfaces would need to be modified to handle both situations
and detect which situation they're dealing with.

The thing is all these interfaces handle the quoting transparently for
you, so the code is portable already. What you're complaining about is
that you have your own query marshalling and it is not portable.

Incidently, if you disable the backslash quoting, how does one enter
raw binary data including NUL (\0) characters?

The only viable solution I can think of is that it is set at
*connection* time (maybe extra parameters), and unchangable for the
rest of the session. This means that unmodified client interfaces won't
see a difference.

> I can say, that I for one would enable the no-backslash config option
> out of the box -globally -so that we can start using pg now without any
> more upper managerial concerns/excuses about language/interface
> compliance..I can also say that (what we already know) the longer we
> wait to provide the 'right' option, the *more* legacy apps (and
> interfaces) will be built around it and consequently suffer when the
> need for change eventually comes (almost wholly caused by interop
> concerns). And market gain is being hurt now by this incompatibility
> with commercial offerings; that's an unfortunate fact.

Even if PostgreSQL implements this now, you will have to wait for new
versions of any client libraries before it's usable. See the autocommit
disaster for an example why people are not rushing into this...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Mon, Feb 28, 2005 at 10:13:00 -0700,
  Ken Johanson <pg-user@kensystem.com> wrote:
>
> Besides, the version-deprecation / version requirements you mention
> exists in every piece of software I've even seen. Sometime they're okay
> with a really old version, sometime only the newest will do. This is the
> very argument for getting PG to offer an (use-optional) escape behavior
> inline with the rest - to mitigate these version requirements down the road.

Shouldn't this data be being passed through some standard code that checks if
escaping is needed? If so, is that the right place to handle whether or not
backslashes need to be escaped in addition to single quotes?

Bruno Wolff III wrote:
> On Mon, Feb 28, 2005 at 10:13:00 -0700,
>   Ken Johanson <pg-user@kensystem.com> wrote:
>
>>Besides, the version-deprecation / version requirements you mention
>>exists in every piece of software I've even seen. Sometime they're okay
>>with a really old version, sometime only the newest will do. This is the
>>very argument for getting PG to offer an (use-optional) escape behavior
>>inline with the rest - to mitigate these version requirements down the road.
>
>
> Shouldn't this data be being passed through some standard code that checks if
> escaping is needed? If so, is that the right place to handle whether or not
> backslashes need to be escaped in addition to single quotes?
>
>
>

Ideally yes, but its not a requirement in any driver's spec that I'm
familiar with. In fact the driver specs expect or 'claim' some (possibly
implicit) level of sql language compliance -- so that the same query
sent to a different database yields the same result.

insert into tbl (path) values ('c:\test')

The above query *could* and "should* be sent through an escape
preprocessor (PreparedStatement interface) but it is *not* required.
It's also not fair to say that a user can *expect* the above to not work
with PG even though it does with another DB, imo. The user coming from
another DB *won't* expect it to be broken. (I know from experience :-)

Martijn van Oosterhout wrote:
> Incidently, if you disable the backslash quoting, how does one enter
> raw binary data including NUL (\0) characters?

The bytea type has its own internal quoting/escaping mechanism (which
overlaps with the lexer's mechanism in some ways), so entering binary
data is not an issue.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Mon, 2005-02-28 at 10:13 -0700, Ken Johanson wrote:
> >I'm a little worried about PostgreSQL having the same problems as PHP.
> >In PHP, every time you want to download an application, you never see
> >"This application works on php 4+". Instead, you see "This application
> >works on php4+ with the following config options set <long list>".
> >Sometimes these applications have conflicting requirements. From an
> >administrator's standpoint, it's a mess.
> >
> >In PostgreSQL I think it would actually be much worse. Right now many
> >applications build a PostgreSQL layer, but will they build two? I think
> >this would cause a divide in the application support (some for config
> >option A some for config option B) in the already smaller-than-we'd-like
> >set of software that supports PostgreSQL.
> >
> >Regards,
> >    Jeff Davis
> >
> >
> There's certainly two perspectives to this. The one you present is
> certainly valid, but consider the bigger picture...
>
> "This application requires the following databases: Oracle versionX, MY
> SQL version X, Mysql version 5.2 with the no-backslashes option, UltraDB
> version x"
>
> Notice the lack of PG -
[snip]

A valid point: that's certainly the issue we're dealing with here.

I think most people agree that being SQL compliant is good. The question
is: is it worth the pain for existing users?

A configurable option does not make the pain disappear. Admins are
forced to choose one side (either sql compliant or c style) and exclude
the other applications. Any app developer that wants to support pre-8.1
apps will have to have a c-style app available. So even if you nip it in
the bud, it's not really gone yet because app developers want to support
old versions of postgres.

I know if we added the option and deprecated the old style, I would be
forced to choose between using deprecated syntax that may not be
supported for long, or doing a lot of work to convert and retest
applications.

> Besides, the version-deprecation / version requirements you mention
> exists in every piece of software I've even seen. Sometime they're okay
> with a really old version, sometime only the newest will do. This is the
> very argument for getting PG to offer an (use-optional) escape behavior
> inline with the rest - to mitigate these version requirements down the road.


I think you may have misunderstood what I meant. I am not suggesting
that we don't change the database at all between versions, my argument
was showing the difficulties when one version has many different shapes
due to many incompatible options.

Regards,
    Jeff Davis

[snip]
>
> I think most people agree that being SQL compliant is good. The question
> is: is it worth the pain for existing users?

My guess is that it is worth it, if the users are given the discretion
of treading that water.. and to save them future pain by encouraging
them to migrate toward 'other-db' compatibility (or merely to migrate to
PreparedStatement to eliminate worry and *insure* interop).

But where things are right now, I *know* allot of apps specially coded
for PG (or mysql) --- using functions like PHPs escapeCslashes()... so
they are NOT compatible apps with other DBs. So making the change would
at least raises author awareness to use PreparedStatements instead (half
the battle is won then because when a PS admin turns on the new escape,
their apps still works correctly), or stop using escapeCslashes in favor
of a sql-escape function (yes, not 'old pg' compatible, but be able to
claim interop with other dbs).

>
> A configurable option does not make the pain disappear. Admins are
> forced to choose one side (either sql compliant or c style) and exclude
> the other applications. Any app developer that wants to support pre-8.1
> apps will have to have a c-style app available. So even if you nip it in
> the bud, it's not really gone yet because app developers want to support
> old versions of postgres.

As was mentioned earlier, this may not be too much of an issue if the
new drivers supported an option in the getConnection call that turned on
the new escape, otherwise leaving old escape turned on by default. Sort
of like the jdbc version/conformance level that jdbc drivers can report
through function calls. In fact PG could forever use the old style
escapes by default, except when a modern driver connected to it and they
both agree to use the new style.

>
> I know if we added the option and deprecated the old style, I would be
> forced to choose between using deprecated syntax that may not be
> supported for long, or doing a lot of work to convert and retest
> applications.
>

Yes - and your app would be inter operable with Oracle, Sybase, etc and
have a wider audience (moot point if you use prepared statements
obviously) especially in the enterprise... Very worthwhile, imo.

>
>>Besides, the version-deprecation / version requirements you mention
>>exists in every piece of software I've even seen. Sometime they're okay
>>with a really old version, sometime only the newest will do. This is the
>>very argument for getting PG to offer an (use-optional) escape behavior
>>inline with the rest - to mitigate these version requirements down the road.
>
>
>
> I think you may have misunderstood what I meant. I am not suggesting
> that we don't change the database at all between versions, my argument
> was showing the difficulties when one version has many different shapes
> due to many incompatible options.

Sorry, I misunderstood. Your point is well taken, and I agree.

Thank you,
ken

The latest mysql build (5.0.3) now supports standard backslash behavior,
using the below config option.

set-variable=sql-mode=PIPES_AS_CONCAT,ANSI_QUOTES,NO_BACKSLASH_ESCAPE

PG seems to be the last holdout. :-)

Ken

Ken Johanson wrote:
> [snip]
>
>>
>> I think most people agree that being SQL compliant is good. The question
>> is: is it worth the pain for existing users?
>
>
> My guess is that it is worth it, if the users are given the discretion
> of treading that water.. and to save them future pain by encouraging
> them to migrate toward 'other-db' compatibility (or merely to migrate to
> PreparedStatement to eliminate worry and *insure* interop).
>
> But where things are right now, I *know* allot of apps specially coded
> for PG (or mysql) --- using functions like PHPs escapeCslashes()... so
> they are NOT compatible apps with other DBs. So making the change would
> at least raises author awareness to use PreparedStatements instead (half
> the battle is won then because when a PS admin turns on the new escape,
> their apps still works correctly), or stop using escapeCslashes in favor
> of a sql-escape function (yes, not 'old pg' compatible, but be able to
> claim interop with other dbs).
>
>>
>> A configurable option does not make the pain disappear. Admins are
>> forced to choose one side (either sql compliant or c style) and exclude
>> the other applications. Any app developer that wants to support pre-8.1
>> apps will have to have a c-style app available. So even if you nip it in
>> the bud, it's not really gone yet because app developers want to support
>> old versions of postgres.
>
>
> As was mentioned earlier, this may not be too much of an issue if the
> new drivers supported an option in the getConnection call that turned on
> the new escape, otherwise leaving old escape turned on by default. Sort
> of like the jdbc version/conformance level that jdbc drivers can report
> through function calls. In fact PG could forever use the old style
> escapes by default, except when a modern driver connected to it and they
> both agree to use the new style.
>
>>
>> I know if we added the option and deprecated the old style, I would be
>> forced to choose between using deprecated syntax that may not be
>> supported for long, or doing a lot of work to convert and retest
>> applications.
>>
>
> Yes - and your app would be inter operable with Oracle, Sybase, etc and
> have a wider audience (moot point if you use prepared statements
> obviously) especially in the enterprise... Very worthwhile, imo.
>
>>
>>> Besides, the version-deprecation / version requirements you mention
>>> exists in every piece of software I've even seen. Sometime they're
>>> okay with a really old version, sometime only the newest will do.
>>> This is the very argument for getting PG to offer an (use-optional)
>>> escape behavior inline with the rest - to mitigate these version
>>> requirements down the road.
>>
>>
>>
>>
>> I think you may have misunderstood what I meant. I am not suggesting
>> that we don't change the database at all between versions, my argument
>> was showing the difficulties when one version has many different shapes
>> due to many incompatible options.
>
>
> Sorry, I misunderstood. Your point is well taken, and I agree.
>
> Thank you,
> ken
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>
>