Oracle Database 12c EEからPostgreSQLへ、またはUltimate IEMプラットフォーム向けに開発する場合のPostgreSQL間の主な違い

前の記事で、 PostgreSQL用のIEMプラットフォームのバージョンであるSolid12のリリースについて説明しました。 約束どおり、開発者が直面しなければならないこと（および移行中に遭遇したこと）について詳しく説明します。



このレビューは網羅的なものではなく、PostgreSQLでの作業を開始するOracle開発者にとって最低限の入門書と見なすべきです。



そこで、最も重要なものとして認識した相違点のリストを次に示します。

• テーブル、列、ストアドプロシージャの名前は、既定では小文字です（引用符で囲まれた識別子を除く）。 これは、SQLクエリの結果の列名にも影響します。
• パッケージはサポートされていません。 Solid12カーネルは、回路を使用して機能をパッケージにグループ化するため、いくつかのコア回路が使用されます。
• パッケージ変数の代わりに、セッション変数が使用されます。 それらのセマンティクスは似ていますが、いくつかの違いがあります。
• PostgreSQLの一時テーブルは常にローカルであり、Oracleとは異なり、スキーマにバインドできません。 Solid12には、Oracleスタイルのグローバル一時テーブルをエミュレートする機能セットがあり、グローバル一時テーブルを操作するための標準構文を完全にサポートしています。
• PL / PgSQLストアドプロシージャ言語は似ていますが、Oracle PL / SQLとは多くの詳細が異なります。
• ストアドプロシージャは常に動的バインディングを使用します（つまり、文字は実行時に解釈されます）。 たとえば、ストアドプロシージャが「SELECT * FROM USERS」などのクエリを実行する場合、「USERS」という名前のテーブル検索は、コンパイル中ではなく実行時に実行されます。 動的バインディングにより、PostgreSQLの手順は非常に柔軟になりますが、Oracleよりもはるかに多くのランタイムエラーが発生しやすくなります。
• デフォルトでは、PostgreSQL関数は現在のユーザーのパーミッションセットで実行されます（Oracleでは、関数の作成者のパーミッションがデフォルトで使用されます）。 このオプションは、必要な各関数で明示的に再定義できます。
• 空行はNULLと同じではありません。 Oracleとは異なり、PostgreSQLはvarcharまたはテキストをNULLと連結すると常にNULLを返します。 結果が誤って無効にされないように、ローカル変数は常に空白行で初期化してください。
• PostgreSQL DDL操作はトランザクションです。 テーブルと関数の作成、列タイプの変更、テーブルのクリア（TRUNCATE）などをコミットする必要があります。
• 関数のシグネチャが元のものと異なる場合、関数を新しいバージョンに置き換えることは失敗します。 この状況では、「関数の作成または置換」を実行するだけでは不十分です。古いバージョンの関数を削除（ドロップ）して、再作成する必要があります。
• データベースエラーが発生すると、現在のトランザクションがエラーとしてマークされるため、トランザクションはROLLBACK以外のコマンドを実行できません。 ただし、このようなトランザクションは、エラーが発生する前に完了した最後の名前付きセーブポイント（SAVEPOINT）にロールバックできます。 セーブポイントへのロールバック後、トランザクションは引き続き動作およびコミットできます（この手法は統合テストで使用され、エラーに関係なく実行を継続する必要があります）。
• 複数の同時にアクティブなDataReaderは、データベースへの同じ接続ではサポートされていません（いわゆるMARSモード-複数のアクティブな結果セット）。 複数のデータセットを読み取るには、各データセットに1つずつ、複数のデータベース接続を開くか、クエリを順番に実行する必要があります。

アプリケーションコード内の特定のデータベースの機能から完全に抽象化することは常に可能とは限りません。 多くの場合、コマンドまたはサービスでは、動的SQLクエリを作成して実行したり、ストアドプロシージャを呼び出したりする必要があります。 アプリケーションにはトリガー、ビュー、制約、またはインデックスが必要な場合があるため、Oracleアプリケーション開発者は少なくとも基本的なPostgreSQLプロパティを理解する必要があります。



以下は、説明したいくつかの困難に対処するのに役立ついくつかの指示です。

PL / PgSQLコードで動的バインディングをバイパスする方法

動的バインディングは強力なメカニズムであり、場合によっては動的クエリの実行（EXECUTE sql）を置き換えることができます。 コインの裏側は、デザインの脆弱性、コンパイル中のチェックの欠如です。 コンパイラは、特定のシンボルがデータベースオブジェクトを参照しているかどうかを静的にチェックできません。



関数がテーブルや関数などのシンボルを参照する場合、特定のオブジェクトは関数の実行中にのみ名前で検索されます。 さらに、変数「search_path」の内容はこの検索に影響します。つまり、現在のセッションの設定に応じて、任意のスキームでシンボルを見つけることができます。

これは通常そうではありません。



動的スナップを無効にするには、2つの簡単なルールに従います。

• 行「set search_path to（current schema name）」をすべての関数定義に追加し、
• 現在のスキーマ外のすべてのテーブルを、スキーマの名前で修飾します。

これはバインディングを静的にしません（PostgreSQLはまだ文字の有効性をチェックしません）が、意図せずに文字を他の何かにバインドする可能性を無効にします。



動的バインディングの影響を受けなくなったPL / PgSQL関数のソースコードの例を次に示します。

-- current search_path = my_schema create or replace function my_func(my_arg text) returns void as $$ declare v_id bigint; begin perform another_func(my_arg); -- same as perform my_schema.another_func(my_arg); select id into v_id from kernel.users -- table name is qualified with kernel schema name where login = my_arg; -- the rest is skipped... end $$ language plpgsql set search_path to my_schema;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

関数に適用される許可をオーバーライドする

デフォルトでは、PostgreSQLの関数は、Oracleオプション「AUTHID CURRENT_USER」と同様に、現在のDBMSユーザーの権限セットで呼び出されます（デフォルトでは、Oracleは異なるモード「AUTHID DEFINER」を使用します）。



Oracleの動作をエミュレートするには、次のように関数で「セキュリティオプション」をオーバーライドする必要があります。

 create or replace function my_secure_func() returns void as $$ begin  -- call here any functions available to the superuser end $$ language plpgsql security definer; -- default is security invoker
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Oracleスタイルのグローバル一時テーブルエミュレーション

PostgreSQLの一時テーブルのセマンティクスは、Oracleとは大きく異なります。 違いの概要は次のとおりです。

• Oracleの一時テーブルは定数です。つまり、その構造は固定され、すべてのユーザーに表示され、内容は一時的です。
• PostgreSQLでは、使用する前に一時テーブルが作成されます。 一時テーブルの構造と内容の両方は、このテーブルを作成した現在のDBMSプロセスにのみ表示されます。 PostgreSQLの一時テーブルは、セッションの終了時またはトランザクションの終了時に常に削除されます。
• Oracleでは、一時テーブルは常に作成中に指定された特定のスキーマ内に配置されます。
• PostgreSQLでは、一時テーブルを任意のスキームに配置することはできません;それらは常に特別な暗黙の一時スキームで作成されます。

pack_tempスキーマには、Oracleスタイルの一時テーブルをエミュレートするライブラリが含まれています。 関心のある機能は2つだけです。

 create_permanent_temp_table(table_name [, schema_name]); drop_permanent_temp_table(table_name [, schema_name]);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

永続的な一時テーブルの作成は、次の2つの手順で行われます。

1. 通常のPostgreSQL一時テーブル（トランザクションの終了時に削除されるテーブル）を作成します。
2. create_permanent_temp_table関数を呼び出して、この一時テーブルを永続的なテーブルに変換します。
   
   

    create temporary table if not exists another_temp_table (   first_name varchar,   last_name varchar,   date timestamp(0) with time zone,   primary key(first_name, last_name) ) on commit drop; -- create my_schema.another_temp_table select pack_temp.create_permanent_temp_table('another_temp_table', 'my_schema'); -- or create another_temp_table in the current schema -- select create_permanent_temp_table('another_temp_table'); -- don't forget to commit: PostgreSQL DDL is transactional commit;
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   

これにより、グローバルOracle一時テーブルとまったく同じように動作するビューが作成されます。 drop_permanent_temp_table関数で削除できます。

同じデータベース接続上の複数のアクティブなDataReader

これはPostgreSQLの最も厄介な制限です。各データベース接続では、一度に1つのDataReaderしか開くことができません。



新しいリクエストは、前のリクエストが実行および処理されるまで実行できません。



この問題は、さまざまな形式のアプリケーションサービス、LINQクエリ、およびSQLクエリで定期的に発生します。 以下に典型的な例をいくつか示します。

1. LINQクエリは定数を使用します（またはサービスを呼び出します）。 要求は最初のDataReaderを開き、定数サービスは2番目のDataReaderを開こうとし、エラーを受け取ります。 エラーを取り除くには、クエリを実行する前にローカル変数の定数を読み取る必要があります（またはクエリ結果を読み取った後にサービスを呼び出します）。 例：
   
   
   
   

    //  var query = from a in DataContext.GetTable<Agent>() where a.ID = Constants.TestAgentID select a; //  var testAgentId = Constants.TestAgentID; var query = from a in DataContext.GetTable<Agent>() where a.ID = testAgentId select a;
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   
2. LINQクエリの結果はループで処理されますが、LINQかSQLかに関係なく、ループの本体は2番目のクエリを実行します。 制限を回避する方法：クエリの結果をリストまたは配列に具体化し、最初のクエリが完了した後にリストを実行します。 例：
   
   
   
   

    //  foreach (var langId in DataContext.GetTable<Language>().Select(x => x.ID)) { using (LanguageService.UseLanguage(langId)) { // do something language-specific } } //  foreach (var langId in DataContext.GetTable<Language>().Select(x => x.ID).ToIDList()) { using (LanguageService.UseLanguage(langId)) { // do something language-specific } }
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   
3. LINQクエリ内でToArray / ToList / ToIDListを呼び出します。 修正するには、リクエストを部分に分割する必要があります。
   
   
   
   

    //  var dictionary = DataContext.GetTable<CalendarDayStatus>().Where(d => dates.Contains(d.DT)) .GroupBy(g => g.DT, e => e.StatusID) .ToDictionary(k => k.Key, e => e.ToIDList()); //  var dictionary = DataContext.GetTable<CalendarDayStatus>() .Where(d => dates.Contains(d.DT)) .GroupBy(g => g.DT, e => e.StatusID) .ToDictionary(p => p.Key); var dict = dictionary.ToDictionary(p => p.Key, p => p.Value.ToIDList());
         
         
   
           
           
           
         
   
       
           
           
           
         
         
   
           
           
           
         
   
       
        

   
   
   残念ながら、このタイプのエラーは静的に検出するのが非常に困難です。 したがって、それぞれの重要でないLINQクエリを徹底的にテストして、同時に複数のDataReaderを開こうとしていないことを確認する必要があります。
   
   

次は？

PostgreSQLチームとの相互作用を集中的に開発する予定です。 実際、ほとんどの制限は乗り越えられないように見えません。おそらく、PostgreSQLコードに適切な変更を加えるためのリソースを見つけることができます。



たとえば、ジオデータ処理のサポートなど、PostgreSQLが既に備えている機能の一部は使用しませんでしたが、将来のバージョンで使用できることを願っています。



いずれにせよ、両方のバージョン-軽量Solid12およびエンタープライズUltimate Solid-が並行して開発され、すべての重要な機能がプラットフォームの両方のバージョンでサポートされます。