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 to a minimum.
We need to look at the columns that correspond to the usage of sysdate()
and understand exactly how these columns are used in Spacewalk. We need
to categorize them as either:
1) Column used blindly as a timestamp (used for sorting etc.) and would
not impact application if sysdate() was replaced with current_timestamp().
2) Not #1 and need to develop a strategy for this.
Date columns in spacewalk will required a special amount of analysis and
handling. Especially queries that do date arithmetic. This is a
broader topic then discussed here.
= 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.
Since we don't need to support 7.4, I don't see any reason to change
this from the way we do this for oracle.
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?
+1
= 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.
+1
* 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. Is that the plan?
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.
= Porting Sequences =
Sequences are available in both Oracle and Postgres, with minor differences.
* Instead of seq_name.nextval, in Postgres we should do nextval( 'seqname' ).
* Same applies to curval too.
So, what is the proposal?
Bruce, can you comment on this?
* COMMIT/ROLLBACK inside PL/PGSQL.
Transaction boundaries inside PL/PGSQL are not supported; need to handle this
on per-case basis.
COMMIT/ROLLBACK inside PL/SQL code means that the calling application relies
heavily on the fact that the DB can do that. Get the developer's POV on getting
rid of such use (even on Oracle PL/SQL).
We should move away from commit/rollback in PL/SQL wherever possible.
+1
* Autonomous transactions... A BIG TODO.
Use dblink in conjunction with views to get over this.
Aren't autonomous transactions used in the Oracle schema primarily to
get around the mutating table issue, something which does not happen
on PostgreSQL?
= Porting Synonyms =
Synonyms are not supported in Postgres.
It seems that synonyms are being used as a convenience (after all, thats what
they are!), so we propose that the calling code be changed to reference the
objects directly. (Need to vet it out with the community).
+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?
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
