Re: Writing oracle/postgress generic SQL

On Mon, Feb 26, 2007 at 08:01:52AM -0800, Richard Troy wrote:
> On Fri, 23 Feb 2007, David Fetter wrote:
> > On Fri, Feb 23, 2007 at 08:28:06AM -0800, Richard Troy wrote:
> > > On Fri, 23 Feb 2007, David Fetter wrote:
> > > > On Fri, Feb 23, 2007 at 10:23:56AM +0100, Ben Edwards wrote:
> > > > > Anyone know of any guidelines for writing SQL which works under
> > > > > Oracle witch will also work under postgress. This is to ensure that
> > > > > SQL written for an Oracle database can be migrated to postgress
> > > > > later.
> > > >
> > > > You've just bumped into the problem that while standard SQL exists,
> > > > only Mimer and possibly DB2 implement it. The presentation below
> > > > outlines your main choices for supporting more than one DB back-end,
> > > > and they're all expensive and troublesome to maintain.
> > > >
> > > > http://www.powerpostgresql.com/Downloads/database_depends_public.swf
> > >
> > > With all due respect to Josh's presentation, there's a lot more
> > > to the story than those couple of slides.
> >
> > With all due respect, the presentation was if anything an
> > understatement.
>
> Yes; it didn't say very much. I'm sure Josh, as speaker, articulated
> what wasn't in those slides, but we didn't get the benefit of that
> on the web.

The presentation understated the problems with trying to support more
than one DBMS back-end.

> > Unless, as with rare beasties like Science Tools, the major
> > purpose of the application is to support multiple DBMS back-ends,
> > it's just too expensive. Even in those rare cases, it's
> > expensive.
>
> I guess anything you have to pay for is too expensive. (Sounds like
> dogma to me. And you know what dogma makes - just don't step in it.)

If you're determined to take offense, especially after your phone call
wherein I thought we had discussed this rationally, I can't stop you.

I can tell you that I've tried many times over the years and seen
plenty of other efforts to make database-independent code, and in no
case was it cheap even to attempt. Either it pushes lots of work from
the database out into application land, or it's duplicating database
code that essentially does the same thing for each back-end RDBMS.

The first is expensive because the applications are now doing things
that the database is good at, and the second is expensive because
maintaining parallel code bases where the design criterion is that
they must behave identically is never going to be cheap. The first
piles on the second one's cost as soon as there is more than one
application.

None of this has anything to do with the business model. It has to do
with essential qualities of software development.

> > > Are there things it misses? Yes, but not much. I'll take the
> > > wild guess that more than 80% of applications are completely and
> > > adequately served.
> >
> > That says something about the applications you've seen, and not
> > about the adequacy of such a library.
>
> That remark is uninformed and arrogantly presumptuous about both me
> and the library, and uninsightful regarding the implementation of
> applications. It's also needlessly offensive, if you'll forgive the
> pun.

Since Science Tools is not in the business of selling SQL translators,
you'll of course be delighted to show just exactly how it works and
for what cases. The "treat the DBMS as a dumb data store" model is
one that's been widely tested and proven inadequate from the viewpoint
of the organization that has to maintain said data store. That model
can be quite lucrative for vendors, and more power to them.

> > What point is there in using a powerful tool like an RDBMS and
> > then hobbling yourself by only using 10% of the available
> > features? It's certainly a bad thing to do by default.
>
> 10%? Whatever. I never said anything of the kind - and I'm reminded
> that an unsupported argument can be dismissed without support. But
> there ARE good reasons. We read on this very list about two weeks
> ago a long treatise on the subject by an obviously long-in-the-tooth
> DBA type who articulately took at least four pages to tell us why it
> was his practice and advice to always be able to move to another
> RDBMS. Perhaps read the archives and become informed...

I'm informed. I am aware that some of the cute tricks DBMS vendors
used to play by making it expensive to switch back-ends weren't
terribly ethical, just as the cute tricks Unix vendors used to play
weren't. That was the late 1980s and early 1990s, and the situation
now is different. Without needing to introduce intentional
incompatibilities, RDBMSs are so different from one another that it's
just about impossible to make code that's exactly identical, one to
the other. I'd contend that it's impossible without pushing work out
into the application layers, which is that "dumb data store" model.

> > > It has pass-through capability so you can still get at
> > > engine-specific features, though it does completely side-step
> > > stored procedures
> >
> > Oops! There went 60% of the code in some of the databases I've
> > seen in production. 80% in at least one case I've seen in the
> > past year.
>
> Lots of people use stored procedures and some people over-use them
> while some others under-utilize them in their architectures. It
> should be no surprise that some people follow dogma while others
> consider every arrow in their quiver.

You keep saying this word, "dogma." I prefer the term, "best
practices," and since I'm in the business of helping people who have
to manage the data, my "best practice" is to put their interests ahead
of vendors'. Attempts at database independence really serve
vendors--free software or otherwise--first, and people managing
databases a distant second.

> Yet I detect a certain flippant bigottry in your response - Oops!
> Perhaps a more considered argument would be effective than just an
> attack - that is, presuming there's a considered argument to be
> made.
>
> The short of it is that Science Tools is surely not alone in having
> developed an SQL dialect translator, though we may be the only ones
> to offer it to customers. Either way, automated dialect
> translation, whether by us otherwise, is another useful choice
> whether _you_ like it or not.

Some day, and maybe that day is today with Science Tools, SQL dialect
translators will be so good at what they do that we'll only write
DBMS-specific SQL for a subset of code that is to SQL what
hand-tooled machine-specific assembler code is to C. If that day has
arrived, great! Until it does, though, we're kinda stuck with what we
have, and should deal with it that way.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!
http://www.postgresql.org/about/donate