Thread: Writing oracle/postgress generic SQL
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. Ben -- Ben Edwards - Brussels, Belgium & Bristol, UK If you have a problem emailing me use http://www.gurtlush.org.uk/profiles.php?uid=4 (email address this email is sent from may be defunct)
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 The cheapest, highest-quality thing to do is to choose one DB back-end and then use everything it has to offer. Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
"Ben Edwards" <funkytwig@gmail.com> writes: > Anyone know of any guidelines for writing SQL which works under Oracle > witch will also work under postgress. The only thing that means anything is testing on both :-(. Yeah, there is a SQL standard, but there is no DBMS anywhere in the world that implements all and only what is in the spec. Exhibit A in this regard is that the standard refuses to specify any user-visible index manipulation; so the moment you write anything like CREATE INDEX you are on implementation-dependent ground. regards, tom lane
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. (They were meant to be given, I'm sure, along with a talk in which the speaker provided most of the value.) And I don't think launching an attack on MySql is helpful to this dialogue, though I do understand the point Josh is making... There are other choices. For example, Science Tools, back in 1997, faced with the similar but slightly different problem of being a vendor supporting multiple RDBMSes for client data, could have taken the typical choice of managing different code branches for each of the RDBMSes it supports. Instead, we wrote an SQL dialect translator that presently supports five (and soon six) RDBMS platforms - and could probably support all the rest if only someone cared enough to configure them - and this translator is available to customers, not just embeded for the exclusive use of Science Tools' applications. You link your user-application code to our library and you can send it any version of SQL, either statically or dynamically, and it automatically translates into the correct dialect for the database engine you're connected to. It does both DDL and DML and it has command-line tools available, too, so you don't have to link your apps if you don't want to. Presently supported are: Postgres (of course!), Informix, DB2, Sybase, and also Oracle - yes, of course, them, too. (OpenIngres is undergoing testing right now for certification sometime this spring.) 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. It has pass-through capability so you can still get at engine-specific features, though it does completely side-step stored procedures as these are vastly harder to automate conversion of - we just do the SQL. When calling a DBMS from our library, we handle error recovery, database reconnection, optional DBMS independent journaling and even important aspects of security. When parsing DDL, it (optionally) throws warnings of incompatability, though, as a practical matter, most engines have now removed most of their older limitations that made this vital in their earlier versions. (We support versions of all five since about 1997 and, as there were so many small changes along the way, we provide a configuration mechanism where you can tell it the limitations of your version such as attribute length, maximum length of varchar, etc.) Regards, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 rtroy@ScienceTools.com, http://ScienceTools.com/
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. I converted a fairly complex data collection application from Oracle to PG about 2 yrs ago. I was pleasantly surprised at how little DML I had to change, and some of it had deeply nested subqueries. Here are the snags I hit: (1) Stored procedures had to be rewritten by hand. You might want to look at EnterpriseDB, as they've added on to PG to enhance Oracle compatibility. (2) I had to change all the stored procedure invocations that used Oracle's "call myproc()" syntax. If we had used JDBC standard calling conventions, this would not have been necessary. (Just realized you didn't say which language you are using.) (3) Stay away for Oracle proprietary SQL features, like their use of (+) for outer joins. This was a version 8 oddity, and they support standard outer join syntax now. (4) We had significant use of Oracle dblinks in our SQL, and of course that doesn't translate. PG has a dblink capability in contrib, but it is not as complete an implementation as Oracle's. Hope that helps. -- Guy Rouillier
I've converted stuff from PostgreSQL to Oracle before, and some of the biggest pains were "OFFSET ... LIMIT ..." in PostgreSQL vs. ROWNUM or ROW_NUMBER in Oracle (depending on version of Oracle, including having to wrap the query with ROWNUM/ROW_NUMBER in a subselect - I greatly prefer OFFSET and LIMIT, especially for web applications), and sequence NEXTVAL syntax. There may be some date type conversion / formatting issues, too. Temporary tables were somewhat different, too, as I recall.
You might be able to hide some of the internal differences by creating database-specific views and functions, and using simpler queries from the views at a higher level of the application. Of course, there is that weird Oracle thing where you have to say "SELECT .... from DUAL" instead of just "SELECT xxx" to get simple function return values.
Some of the Oracle stuff may differ, depending on version.
Susan Cassidy
"Ben Edwards" <funkytwig@gmail.com> Sent by: pgsql-general-owner@postgresql.org 02/23/2007 01:27 AM |
|
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.
Ben
--
Ben Edwards - Brussels, Belgium & Bristol, UK
If you have a problem emailing me use
http://www.gurtlush.org.uk/profiles.php?uid=4
(email address this email is sent from may be defunct)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------
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. 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. [sales pitch elided ;)] > 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. 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. > 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. Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! http://www.postgresql.org/about/donate
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. > 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.) > > 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. > 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... > > 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. 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. Ciao, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 rtroy@ScienceTools.com, http://ScienceTools.com/
>> 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.) *cough* There really isn't a good argument in general for abstracting out database access to support multiple platforms. The only argument I ever see is: We want our product to support as many databases as possible. Which is certainly a valid business argument but certainly not a good technical argument. > >>> 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. > I am not sure why you would be offended by another's experience. I am offended that you are offended that he wasn't offended. Good lord, take a breath. I would agree that in my experience most applications that choose to abstract their database usage generally make bad choices in how they do it and thus have a negative impact on not only the survivability of existing code but the maintainability of said code. Are their apps out there that do it right? Oh probably, I have never seen one though. > > 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. useful not always == good. Windows is useful. Windows is not good. Joshua D. Drake > > Ciao, > Richard > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
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@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! http://www.postgresql.org/about/donate