Re: [Spacewalk-devel] PGPORT Initial porting guidelines.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 15 Jan 2009 15:36:55 +0530 "Gurjeet Singh" wrote: > Hi All, > > Just finished Wikifying the initial guidelines on how to go about > porting to Postgres. You can find it here: > > https://fedorahosted.org/spacewalk/wiki/PostgresPortingGuidelines > > These are my opinions on the issues I could see directly (in > schema/ or through grepping). There are many places where some debate > is needed, and then there are some places where core developers can > help out making decisions based on their knowledge. > > I am attaching the text file here. Lets discuss the issues here, > and finalize them, before going ahead with the actual modifications. > > Best regards, Great guidelines Gurjeet, We'll have to get Orafce packages into Fedora/EPEL, will we need some work done to the code itself to run on PostgreSQL 8.1? I think we discussed that it may require 8.2 currently in some past meetings but I can't see this clearly indicated in the Orafce webpage. I'm not really the best qualified to comment on some of the changes you propose to the existing Oracle schema, but in general I like the idea of making adjustments if it aids in portability provided they're not too invasive and wide-sweeping. The rest looks good to me, nice guidelines. Hopefully others on the team with more schema background will be able to chime in with some more feedback. Thanks, Devan -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.9 (GNU/Linux) iEYEARECAAYFAklvNWQACgkQAyHWaPV9my5C0QCdGAJoD7lJ5C1U/9yF2kSjtZ6k AfgAoMnAHg413FtTUi3lE3psMposCx2U =clUu -END PGP SIGNATURE- ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] PGPORT Initial porting guidelines.
Devan Goodwin wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 15 Jan 2009 15:36:55 +0530 "Gurjeet Singh" wrote: Hi All, Just finished Wikifying the initial guidelines on how to go about porting to Postgres. You can find it here: https://fedorahosted.org/spacewalk/wiki/PostgresPortingGuidelines These are my opinions on the issues I could see directly (in schema/ or through grepping). There are many places where some debate is needed, and then there are some places where core developers can help out making decisions based on their knowledge. I am attaching the text file here. Lets discuss the issues here, and finalize them, before going ahead with the actual modifications. Best regards, Great guidelines Gurjeet, We'll have to get Orafce packages into Fedora/EPEL, will we need some FYI: It is already in Package Review process, but failed to pass several times. -- Miroslav Suchy RHN Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] PGPORT Initial porting guidelines.
Miroslav Suchý wrote: FYI: It is already in Package Review process, but failed to pass several times. I forgot to say BZ number https://bugzilla.redhat.com/show_bug.cgi?id=251805 -- Miroslav Suchy RHN Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] PGPORT Initial porting guidelines.
Devan Goodwin wrote: > Great guidelines Gurjeet, > > We'll have to get Orafce packages into Fedora/EPEL, will we need some > work done to the code itself to run on PostgreSQL 8.1? I think we > discussed that it may require 8.2 currently in some past meetings but I > can't see this clearly indicated in the Orafce webpage. I am in contact with the Orafce author and will do the work necessary to port Orafce to Postgres 8.1 and perhaps 7.4; there is already a wiki TODO for this, G14: https://fedorahosted.org/spacewalk/wiki/PostgresTasks I will then need help getting it into Fedora/EPEL. -- 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] PGPORT Initial porting guidelines.
On Thu, Jan 15, 2009 at 03:36:55PM +0530, Gurjeet Singh wrote:
> I am attaching the text file here. Lets discuss the issues here, and
> finalize them, before going ahead with the actual modifications.
Thank you for the writeup. Here are my observations.
> == Open questions relevant to porting efforts ==
> 1. Which versions of Oracle are to be supported by Spacewalk?
10g and later.
> 2. Which versions of Postgres are to be supported by Spacewalk?
8.1.11 (the one in RHEL 5) and later.
> 6. Oracle treats {quote}{quote} (empty string) as null, but PG does not.
> So either make the application aware of it, or attach a BEFORE EACH ROW
> trigger on every table which has a char/varchar column, and set 'if
> new.char_col = {quote}{quote}then new.char_col = null'.
>
> Triggers will not help in cases where app uses results of dynamically
> computed strings.
>
> occurrences in schema: 17; find ./ -name "*.sql" | xargs grep \'\' | grep
> -iv values | grep -v rhnFAQ_satdata.sql
>
Wouldn't it be better to create check that no varchar column in
PostgreSQL has value ''? That way both the application would not be
able to insert those.
> 7. Convert DECODE calls to CASE expressions.
> Although Orafce provides DECODE support, suggestion here is again to modify
> the Oracle code to use CASE expressions for the reason mentioned in point 2.
-1. If Orafce supports this, let's not do more changes than necessary.
> 11. ROWID is not supported by Postgres.
> Fortunately, ROWID keyword is used in very few places across the code, so it
> can be easily ported to Postgres by using the result of expression
> 'tableoid::varchar || ctid::varchar' as a row identifier. Better still, using
> a Primary or Unique Key instead is recommended.
>
+1 for the primary / unique keys instead of using rowid.
> 12. ROWNUM is not supported by Postgres.
> But LIMIT ... OFFSET clause can be used to achieve the same result.
That however will not work on Oracle. How do you propose handling
these situations where no common syntax exists?
> 13. Subquery in SET clause of UPDATE command which updates multiple columns
> is not supported in Postgres.
> Syntax supported:
> {{{
> UPDATE tbl SET col = (SELECT col from tbl2);
> }}}
>
> Syntax not supported:
> {{{
> UPDATE tbl SET ( col1, col2 ) = (SELECT col1, col2 from tbl2);
> }}}
How many occurences of these do we have in the schema?
> 14. TODO : Mention port from sysdate to CURRENT_TIMESTAMP, and research their
> differences (formatting etc.).
Well, sysdate and current_timestamp are completely different -- they
are of different types. Could you elaborate on the "port from"
suggestion? Do you want to remove the use of sysdate in the Oracle
code? Can't we just emulate sysdate on PostgreSQL?
> = Tablespaces =
>
> Talespaces are supported in Postgres, but they do not mean exactly the same
> thing as in Oracle. There is a GUC variable in Postgres that allows us to set
> a global tablespace (at system or session level), and any object being
> created that does not have a TABLESPACE clause gets created in that
> {quote}global{quote} tablespace.
>
> So we have two options.
> 1. If having per-object tablespace is important, then we can keep these
> usages intact for Postgres.
> 2. Or, strip off the TABLESPACE clause from all table and index creation
> scripts, and use the default_tablespace Postgres GUC variable.
>
> Note: Tablespace feature was introduced in Postgres 8.0, and hence not
> available in Postgres version 7.4.
>
We have (or at least, had) templated tablespace definition in the
schema, which could eventually be used to store different types of
objects in different tablespaces, based on their properties -- tables
that change a lot but end up being deleted often, small tables that
do not change much, large tables that only grow, etc.
It should be pretty easy to keep the TABLESPACE clause unchanged
in the source and just strip it when PostgreSQL's SQL creation script
is generated, right?
> = Porting tables =
>
> * Port the triggers to pl/pgsql language (created in the same script as the
> table).
>
I'd like to stop here -- the "created in the same script" hit my eye.
We have problems like
https://www.redhat.com/archives/spacewalk-devel/2008-September/msg00136.html
in our schema sources. Also, some triggers are in the same file
as the CREATE TABLE, but we also have some *_triggers.sql files there.
It might be very benefitial to have some "schema sanity checks" which
would enforce the schema definition to be done one way or another.
> * TABLESPACE clause in PRIMARY KEY clause needs to be changed if porting to
> 7.4 because 7.4 does not support tablespaces.
>
> * Strip out NOLOGGING clause. (incompatible)
> * Strip out ENABLE ROW MOVEMENT. (incompatible)
> * Strip out STORAGE clause. (incompatible)
Similar to the TABLESPACE point above -- I assume we can leave them in
the primary sources and just strip them when generating PostgreSQL
sources.
Re: [Spacewalk-devel] PGPORT Initial porting guidelines.
Jan Pazdziora wrote:
On Thu, Jan 15, 2009 at 03:36:55PM +0530, Gurjeet Singh wrote:
I am attaching the text file here. Lets discuss the issues here, and
finalize them, before going ahead with the actual modifications.
Thank you for the writeup. Here are my observations.
== Open questions relevant to porting efforts ==
1. Which versions of Oracle are to be supported by Spacewalk?
10g and later.
2. Which versions of Postgres are to be supported by Spacewalk?
8.1.11 (the one in RHEL 5) and later.
Yes, this was confirmed by Satellite Roadmap alignment.
It has been mentioned that there are advantages to using 8.2. Are there
differences from a porting standpoint? Are there features that would
make the porting easier/different? Can you summarize the differences
between 8.1 and 8.1 as it applies to the spacewalk schema/queries and
our porting effort?
6. Oracle treats {quote}{quote} (empty string) as null, but PG does not.
So either make the application aware of it, or attach a BEFORE EACH ROW
trigger on every table which has a char/varchar column, and set 'if
new.char_col = {quote}{quote}then new.char_col = null'.
Triggers will not help in cases where app uses results of dynamically computed
strings.
occurrences in schema: 17; find ./ -name "*.sql" | xargs grep \'\' | grep -iv
values | grep -v rhnFAQ_satdata.sql
Wouldn't it be better to create check that no varchar column in
PostgreSQL has value ''? That way both the application would not be
able to insert those.
Yeah, a trigger to correct this seems too heavy handed.
After running the above find, it seems that most of these (17) really
don't seem to be affected by different handling of ('') by oracle and pg.
1) ./views/rhnChannelTreeView.sql: '' || c.name
padded_name,
2) ./views/rhnUserChannelTreeView.sql: '' || c.name
padded_name,
3) ./views/rhnHistoryView.sql: '') ||
4) ./views/rhnHistoryView.sql: '' details,
5) ./views/rhnHistoryView.sql: '') ||
6) ./views/rhnHistoryView.sql: '' details,
7) ./views/rhnHistoryView.sql: '') ||
8) ./views/rhnHistoryView.sql: '' details,
9) ./views/rhnSharedChannelTreeView.sql:''||C.NAME AS PADDED_NAME,
10) ./views/rhnOrgChannelTreeView.sql: '' || c.name
padded_name,
(1-10) - contrived values and would be the same regardless of which DB,
right?
11) ./views/rhnActionOverview.sql:(SELECT COUNT(*) FROM
rhnServerAction WHERE action_id = A.id AND status = 2), -- XXX: don''t
hard code status here :)
13) /tables/rhnChannel_triggers.sql: -- this is a really bad way
of saying "if all we''re
14) ./tables/rhnPackage.sql:--
possible'' easier
(11-14) - This is just in a comment, doesn't matter.
15) ./procs/label_join.sql: ret := '';
16) ./procs/name_join.sql: ret := '';
17) ./procs/id_join.sql:ret := '';
18) ./procs/channel_name_join.sql: ret := '';
May need to look into these further but doesn't seem to be affected by
pg having different handling of ''.
7. Convert DECODE calls to CASE expressions.
Although Orafce provides DECODE support, suggestion here is again to modify
the Oracle code to use CASE expressions for the reason mentioned in point 2.
-1. If Orafce supports this, let's not do more changes than necessary.
+1, let's not do more changes than necessary.
11. ROWID is not supported by Postgres.
Fortunately, ROWID keyword is used in very few places across the code, so it
can be easily ported to Postgres by using the result of expression
'tableoid::varchar || ctid::varchar' as a row identifier. Better still, using a
Primary or Unique Key instead is recommended.
+1 for the primary / unique keys instead of using rowid.
+1.
12. ROWNUM is not supported by Postgres.
But LIMIT ... OFFSET clause can be used to achieve the same result.
That however will not work on Oracle. How do you propose handling
these situations where no common syntax exists?
13. Subquery in SET clause of UPDATE command which updates multiple columns is
not supported in Postgres.
Syntax supported:
{{{
UPDATE tbl SET col = (SELECT col from tbl2);
}}}
Syntax not supported:
{{{
UPDATE tbl SET ( col1, col2 ) = (SELECT col1, col2 from tbl2);
}}}
How many occurences of these do we have in the schema?
14. TODO : Mention port from sysdate to CURRENT_TIMESTAMP, and research their
differences (formatti etc.).
Well, sysdate and current_timestamp are completely different -- they
are of different types. Could you elaborate on the "port from"
suggestion? Do you want to remove the use of sysdate in the Oracle
code? Can't we just emulate sysdate on PostgreSQL?
The plan is to port the queries to be more ANSI SQL compliant so they
will work on both databases. We don't want to have a separate set of
queries for each database. However, for queries that can't be modified
to work for either, we'll need to have a "one off" but we want these to
be kept
Re: [Spacewalk-devel] PGPORT Initial porting guidelines.
On Mon, Jan 19, 2009 at 12:12:20PM -0500, Jeff Ortel wrote: > >> 8.1.11 (the one in RHEL 5) and later. > > Yes, this was confirmed by Satellite Roadmap alignment. > > It has been mentioned that there are advantages to using 8.2. Are there The target is 8.1, not 8.2. PostgreSQL in RHEL 5 won't be rebased. And we do not want to ship our own version of PostgreSQL. > Even easier, since the plan is to have a separate schema directory tree > containing manually maintained .sql files for managing the pg schema, we > can just strip syntax not applicable to pg in these files. I still hope that we can reach a situation when source SQL files that do not need to be different will not be duplicated in the repository. Having two trees and remembering to do every change in both sounds like a mantinenance nightmare to me. >> +1. Especially the monitoring schema makes heavy use of synonyms. >> Getting the schema cleaned up will be a big win. > > I didn't think we still used synonyms (even in the monitoring schema), > do we? We do. -- Jan Pazdziora | adelton at #satellite*, #brno Satellite Engineering, Red Hat ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] PGPORT Initial porting guidelines.
Jan Pazdziora wrote: On Mon, Jan 19, 2009 at 12:12:20PM -0500, Jeff Ortel wrote: 8.1.11 (the one in RHEL 5) and later. Yes, this was confirmed by Satellite Roadmap alignment. It has been mentioned that there are advantages to using 8.2. Are there The target is 8.1, not 8.2. PostgreSQL in RHEL 5 won't be rebased. I didn't suggest it was 8.2. Read above ^^, I agreed that it was 8.1. Also, I posted on the wiki that it was 8.1 last week. Further, I wouldn't expect RHEL5 to be rebased. However, It was suggested on IRC: Jan 14 11:54:39 jortel: 8.1 has alot of issues Jan 14 11:54:58 8.2 is available for RHEL5 in one of teh add on channels Jan 14 11:55:12 in fedora we had to uses 8.3 for koji Jan 14 11:55:49 autovaccumm works ok in 8.2 and pretty much properlly in 8.3 The 8.2 might be available for RHEL5 in add on channels and wanted to be sure we left this door open and understand the differences between 8.1 and 8.2. And we do not want to ship our own version of PostgreSQL. Yes (of course) we don't want to ship our own. I never suggested that. Even easier, since the plan is to have a separate schema directory tree containing manually maintained .sql files for managing the pg schema, we can just strip syntax not applicable to pg in these files. I still hope that we can reach a situation when source SQL files that do not need to be different will not be duplicated in the repository. Having two trees and remembering to do every change in both sounds like a mantinenance nightmare to me. Agreed, having 2 separate trees for oracle DDL and postgres DDL will be a real pain. Hopefully, after the EDB team creates the pg files, we can revisit this having both sets of files for comparison. But for now, the consensus is that the syntax is too different. +1. Especially the monitoring schema makes heavy use of synonyms. Getting the schema cleaned up will be a big win. I didn't think we still used synonyms (even in the monitoring schema), do we? We do. ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
Re: [Spacewalk-devel] PGPORT Initial porting guidelines.
On Mon, Jan 19, 2009 at 05:19:35PM -0500, Jeff Ortel wrote: > >> I still hope that we can reach a situation when source SQL files that >> do not need to be different will not be duplicated in the repository. >> Having two trees and remembering to do every change in both sounds >> like a mantinenance nightmare to me. > > Agreed, having 2 separate trees for oracle DDL and postgres DDL will be > a real pain. Hopefully, after the EDB team creates the pg files, we can > revisit this having both sets of files for comparison. But for now, the > consensus is that the syntax is too different. Well, it seems to me that taking (for example) CREATE TABLE source SQLs, copying them into PostgreSQL tree, fixing them to match PostgreSQL syntax / semantics expectations, and then trying to consolidate it back (which from previous communication and from reading the above is something which is being proposed to do) will make things harder in the long run (expecially the comparison and consolidation stage, which I fear will never be done) than taking CREATE TABLE source SQLs and writing enough of syntax checking / syntax munging scripts to generate PostgreSQL source SQL on the fly. We should try to generate as much of the PostgreSQL as possible. Of course, for triggers, packages, etc., separate sources are called for. But I'd really love to see directory structure of schema/common schema/oracle-specific schema/pg-specific where files in common will certainly have Oracle syntax for now because if will be populated with our current source. The PostgreSQL port should then try to generate as much of PostgreSQL SQL from common, and only if it will seen as unfeasable, move the file in question to oracle-specific to clearly mark that there is a (non-generated) PostgreSQL counterpart in pg-specific. Part of the work will certainly be kicking out trigger definitions from CREATE TABLE files and moving them to _trigger.sql files or even to separate subdirectory in the directory structure. Hmm, maybe, things like create trigger rhn_solaris_p_mod_trig before update on rhnSolarisPatch for each row begin :new.modified := sysdate; end; could even stay in the common directory because they should be pretty easy to match and identify, either with regular expression or full parser, and replace with PostgreSQL equivalent. -- 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] PGPORT Initial porting guidelines.
Jan Pazdziora wrote: On Mon, Jan 19, 2009 at 05:19:35PM -0500, Jeff Ortel wrote: I still hope that we can reach a situation when source SQL files that do not need to be different will not be duplicated in the repository. Having two trees and remembering to do every change in both sounds like a mantinenance nightmare to me. Agreed, having 2 separate trees for oracle DDL and postgres DDL will be a real pain. Hopefully, after the EDB team creates the pg files, we can revisit this having both sets of files for comparison. But for now, the consensus is that the syntax is too different. Well, it seems to me that taking (for example) CREATE TABLE source SQLs, copying them into PostgreSQL tree, fixing them to match PostgreSQL syntax / semantics expectations, and then trying to consolidate it back (which from previous communication and from reading the above is something which is being proposed to do) will make things harder in the long run (expecially the comparison and consolidation stage, which I fear will never be done) than taking CREATE TABLE source SQLs and writing enough of syntax checking / syntax munging scripts to generate PostgreSQL source SQL on the fly. We should try to generate as much of the PostgreSQL as possible. Yes, I am a proponent of this approach as well. Although, the general consensus (so far) has been against it. So, I figured, let the EDB team manually create the pg schema files as they are suggesting. They indicated it would not take long and would start with a copy of the oracle tree. Then we have concrete examples to discuss. Further, we would have (2) specific cases from which to generalize and sample output that can be used to validate a scripting/template approach. Of course, for triggers, packages, etc., separate sources are called for. Yes. But I'd really love to see directory structure of schema/common schema/oracle-specific schema/pg-specific where files in common will certainly have Oracle syntax for now because if will be populated with our current source. The PostgreSQL port should then try to generate as much of PostgreSQL SQL from common, and only if it will seen as unfeasable, move the file in question to oracle-specific to clearly mark that there is a (non-generated) PostgreSQL counterpart in pg-specific. Part of the work will certainly be kicking out trigger definitions from CREATE TABLE files and moving them to _trigger.sql files or even to separate subdirectory in the directory structure. Hmm, maybe, things like create trigger rhn_solaris_p_mod_trig before update on rhnSolarisPatch for each row begin :new.modified := sysdate; end; could even stay in the common directory because they should be pretty easy to match and identify, either with regular expression or full parser, and replace with PostgreSQL equivalent. This would be nice but I'm not as optimistic that the return would be worth the effort. ___ Spacewalk-devel mailing list Spacewalk-devel@redhat.com https://www.redhat.com/mailman/listinfo/spacewalk-devel
