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. Is that the plan?
> = 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?
> * 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.
> * 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.
--
Jan Pazdziora
Satellite Engineering, Red Hat
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
