Re: Possible to run the server with ANSI/ISO string escapeing

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 />