Re: [Spacewalk-devel] Re: PGPORT: Orafce
On Thu, Jan 29, 2009 at 11:45:42PM -0500, Bruce Momjian wrote: > > Sorry, maybe I wasn't clear. On this page: > > https://fedorahosted.org/spacewalk/wiki/PostgresTechnicalApproach > > we list the four query types: > >1. work unchanged in both Oracle and Postgres >2. can be rewritten to work on both databases, e.g. ANSI joins, > CURRENT_TIMESTAMP, CASE instead of decode(), COALESCE instead of NVL() >3. (same) but requires db changes, e.g. add compatibility functions > from Orafce >4. need to create a Postgres-specific version of the query > > Previously I mentioned decode() in #3, but I have now updated the wiki > to mention the use of CASE intead of decode, etc. I think we should be > doing #3 only if we can't easily rewrite the query to be a #2. Any change which requires #2 will need to be QA'ed in Oracle / Satellite as well. While it is a noble goal to have the codebase in pure ANSI syntax, milestone-wise it will be much more feasible to start with compatibility layer (#3) first and not depend on #2. Including for DECODE. -- Jan Pazdziora Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Re: PGPORT: Orafce
Jan Pazdziora wrote: > On Thu, Jan 29, 2009 at 11:45:42PM -0500, Bruce Momjian wrote: > > > > Sorry, maybe I wasn't clear. On this page: > > > > https://fedorahosted.org/spacewalk/wiki/PostgresTechnicalApproach > > > > we list the four query types: > > > >1. work unchanged in both Oracle and Postgres > >2. can be rewritten to work on both databases, e.g. ANSI joins, > > CURRENT_TIMESTAMP, CASE instead of decode(), COALESCE instead of NVL() > >3. (same) but requires db changes, e.g. add compatibility functions > > from Orafce > >4. need to create a Postgres-specific version of the query > > > > Previously I mentioned decode() in #3, but I have now updated the wiki > > to mention the use of CASE intead of decode, etc. I think we should be > > doing #3 only if we can't easily rewrite the query to be a #2. > > Any change which requires #2 will need to be QA'ed in Oracle / > Satellite as well. While it is a noble goal to have the codebase in > pure ANSI syntax, milestone-wise it will be much more feasible to > start with compatibility layer (#3) first and not depend on #2. > > Including for DECODE. Well, the wiki states we should try to make queries be the lowest numbered item possible: https://fedorahosted.org/spacewalk/wiki/PostgresTechnicalApproach If we want to avoid #2 and make make more #3 and #4s, fine, but we need to decide this as a group. Keep in mind if we decide we want no #2, we are going to have a lot more #4 duplicate queries, which I thought we wanted to avoid. Basically, if we make any #2s, the Oracle port is going to have to be retested, so why try to minimize #2 changes? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Re: PGPORT: Orafce
On Fri, Jan 30, 2009 at 08:26:58AM -0500, Bruce Momjian wrote: > > > > Any change which requires #2 will need to be QA'ed in Oracle / > > Satellite as well. While it is a noble goal to have the codebase in > > pure ANSI syntax, milestone-wise it will be much more feasible to > > start with compatibility layer (#3) first and not depend on #2. > > > > Including for DECODE. > > Well, the wiki states we should try to make queries be the lowest > numbered item possible: > > https://fedorahosted.org/spacewalk/wiki/PostgresTechnicalApproach > > If we want to avoid #2 and make make more #3 and #4s, fine, but we need > to decide this as a group. That's why I've sent it as a proposal to the mailing list. > Keep in mind if we decide we want no #2, we > are going to have a lot more #4 duplicate queries, which I thought we > wanted to avoid. My proposed rule is: If #2 can be also done via #3, start with #3, before doing #1. > Basically, if we make any #2s, the Oracle port is going to have to be > retested, so why try to minimize #2 changes? It's retest and it's retest. Retesting one query and retesting wholesale change of syntax are different matters, I assume. -- Jan Pazdziora Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Re: PGPORT: Orafce
Bruce Momjian wrote: Jan Pazdziora wrote: On Thu, Jan 29, 2009 at 11:45:42PM -0500, Bruce Momjian wrote: Sorry, maybe I wasn't clear. On this page: https://fedorahosted.org/spacewalk/wiki/PostgresTechnicalApproach we list the four query types: 1. work unchanged in both Oracle and Postgres 2. can be rewritten to work on both databases, e.g. ANSI joins, CURRENT_TIMESTAMP, CASE instead of decode(), COALESCE instead of NVL() 3. (same) but requires db changes, e.g. add compatibility functions from Orafce 4. need to create a Postgres-specific version of the query Previously I mentioned decode() in #3, but I have now updated the wiki to mention the use of CASE intead of decode, etc. I think we should be doing #3 only if we can't easily rewrite the query to be a #2. Any change which requires #2 will need to be QA'ed in Oracle / Satellite as well. After the pg port, we'll always have to QA satellite on both databases anyway ... While it is a noble goal to have the codebase in pure ANSI syntax, milestone-wise it will be much more feasible to start with compatibility layer (#3) first and not depend on #2. Including for DECODE. Well, the wiki states we should try to make queries be the lowest numbered item possible: https://fedorahosted.org/spacewalk/wiki/PostgresTechnicalApproach If we want to avoid #2 and make make more #3 and #4s, fine, but we need to decide this as a group. Keep in mind if we decide we want no #2, we are going to have a lot more #4 duplicate queries, which I thought we wanted to avoid. Yes, we really want to avoid having duplicate queries as this will be a maintenance nightmare. Whether this is done via 1) compatibility layer or 2) migrating the queries to ANSI SQL or both is another question. The advantage of doing it in the compatibility layer is (maybe) lower risk when running on Oracle. But, since we know there are many queries that must be rewritten (for example: queries with the oracle (+) syntax for outer joins) anyway, why not just do the right thing now and get it over with while we're committed to spending the $$ and time to do this? If satellite is going to become a multi-database application, shouldn't the application code be as database agnostic as possible? After the tagging process we need to count the #2 vs #3 queries (as planned) and then decide which strategy make sense for us. Without these numbers, we'll just be guessing the risk/reward of taking either approach. Basically, if we make any #2s, the Oracle port is going to have to be retested, so why try to minimize #2 changes? ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Re: PGPORT: Orafce
On Fri, Jan 30, 2009 at 09:17:39AM -0500, Jeff Ortel wrote: > > Whether this is done via 1) compatibility layer or 2) migrating the > queries to ANSI SQL or both is another question. The advantage of doing > it in the compatibility layer is (maybe) lower risk when running on > Oracle. But, since we know there are many queries that must be > rewritten (for example: queries with the oracle (+) syntax for outer > joins) anyway, why not just do the right thing now and get it over with > while we're committed to spending the $$ and time to do this? If > satellite is going to become a multi-database application, shouldn't the > application code be as database agnostic as possible? Agreed with the intention. But why is this part of the PostgreSQL effort. Shouldn't this be completely independent goal, with its own requirements and plan and test plan? > After the tagging process we need to count the #2 vs #3 queries (as > planned) and then decide which strategy make sense for us. Without > these numbers, we'll just be guessing the risk/reward of taking either > approach. Agreed. -- Jan Pazdziora Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Re: PGPORT: Orafce
Jan Pazdziora wrote: On Fri, Jan 30, 2009 at 09:17:39AM -0500, Jeff Ortel wrote: Whether this is done via 1) compatibility layer or 2) migrating the queries to ANSI SQL or both is another question. The advantage of doing it in the compatibility layer is (maybe) lower risk when running on Oracle. But, since we know there are many queries that must be rewritten (for example: queries with the oracle (+) syntax for outer joins) anyway, why not just do the right thing now and get it over with while we're committed to spending the $$ and time to do this? If satellite is going to become a multi-database application, shouldn't the application code be as database agnostic as possible? Agreed with the intention. But why is this part of the PostgreSQL effort. Shouldn't this be completely independent goal, with its own requirements and plan and test plan? Maybe. I guess it depends on difference in effort required. After the tagging process we need to count the #2 vs #3 queries (as planned) and then decide which strategy make sense for us. Without these numbers, we'll just be guessing the risk/reward of taking either approach. Agreed. ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Re: PGPORT: Orafce
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 30 Jan 2009 15:26:36 +0100 Jan Pazdziora wrote: > On Fri, Jan 30, 2009 at 09:17:39AM -0500, Jeff Ortel wrote: > > > > Whether this is done via 1) compatibility layer or 2) migrating > > the queries to ANSI SQL or both is another question. The advantage > > of doing it in the compatibility layer is (maybe) lower risk when > > running on Oracle. But, since we know there are many queries that > > must be rewritten (for example: queries with the oracle (+) syntax > > for outer joins) anyway, why not just do the right thing now and > > get it over with while we're committed to spending the $$ and time > > to do this? If satellite is going to become a multi-database > > application, shouldn't the application code be as database agnostic > > as possible? > > Agreed with the intention. But why is this part of the PostgreSQL > effort. Shouldn't this be completely independent goal, with its own > requirements and plan and test plan? I don't see why it should be separated in the first place but if it were, it should probably be done before the PostgreSQL effort (otherwise there'd be some wasted effort involved) implying that we (a) need to put that on hold and (b) find people to do it. We've already got people with a strong background in both databases and are already prepping for a big test/QA impact, I say hit it while we're in there. Devan - -- Devan Goodwin Software Engineer Spacewalk / RHN Satellite Halifax, Canada 650.567.9039x79267 -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.9 (GNU/Linux) iEYEARECAAYFAkmDE70ACgkQAyHWaPV9my4PfgCfWuasLd86THFxPiakQleGiWNr gUYAoMLO18gxJQHrOgKEvVLl6pbjUw0a =q/aK -END PGP SIGNATURE- ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] Re: PGPORT: Orafce
Devan Goodwin wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Fri, 30 Jan 2009 15:26:36 +0100 > Jan Pazdziora wrote: > > > On Fri, Jan 30, 2009 at 09:17:39AM -0500, Jeff Ortel wrote: > > > > > > Whether this is done via 1) compatibility layer or 2) migrating > > > the queries to ANSI SQL or both is another question. The advantage > > > of doing it in the compatibility layer is (maybe) lower risk when > > > running on Oracle. But, since we know there are many queries that > > > must be rewritten (for example: queries with the oracle (+) syntax > > > for outer joins) anyway, why not just do the right thing now and > > > get it over with while we're committed to spending the $$ and time > > > to do this? If satellite is going to become a multi-database > > > application, shouldn't the application code be as database agnostic > > > as possible? > > > > Agreed with the intention. But why is this part of the PostgreSQL > > effort. Shouldn't this be completely independent goal, with its own > > requirements and plan and test plan? > > I don't see why it should be separated in the first place but if it > were, it should probably be done before the PostgreSQL effort > (otherwise there'd be some wasted effort involved) implying that we (a) > need to put that on hold and (b) find people to do it. We've already got > people with a strong background in both databases and are already > prepping for a big test/QA impact, I say hit it while we're in there. In addition, there is more risk of bugs in using a compatibility layer than in rewriting the queries to be ANSI-compliant. We have already tripped over a number of Orafce bugs, while odds are Oracle and Postgres implement ANSI just fine. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
