PostgreSQLのCストアド関数

こんにちは、habracheloveki！ 皆さんの多くは、ビジネスロジックをストアドファンクション/プロシージャの形式でDBMSに配置することに直面しており、クライアントにとって使いやすくなっています。 これには長所と短所の両方があります。 今日は、Cで書かれたPostgreSQLでストアド関数を作成する方法をお伝えしたいと思います。この記事には、それらを使い始めるために知っておく必要のある基本事項が含まれています。

ユーザー機能の説明

PostgreSQLでは現在、次のタイプのユーザー定義関数を定義できます。

• SQL関数
• C関数
• 手続き言語の関数（ plpgsql 、 pltcl 、 plperlなど）

SQL関数は、1つ以上のSQLクエリが存在する本体の関数であり、最後のクエリの結果が返された結果です。 さらに、返された結果がvoidでない場合、 INSERT 、 UPDATE 、またはDELETEをRETURNING構造とともに使用できます。



C関数は静的および動的にロードされます。 静的にロードされた（内部関数とも呼ばれます）は、データベースクラスターが初期化されるときにサーバー上に作成され、動的にロードされたものはサーバーの要求に応じてロードされます。



手続き型言語の関数では、適切な拡張機能を作成する必要があり、一部の言語には、信頼できるものと信頼できないものの2つのタイプがあります（後者の場合、ユーザーアクションを制限する方法はありません）。 基本的なPostgreSQLパッケージにはplpgsql 、 pltcl 、 plperl 、およびplpythonが含まれています 。他の言語のリストはここにあります 。 手続き型言語の拡張機能は、 SQLを介して作成されます 。

CREATE EXTENSION pltcl; --    pltcl CREATE EXTENSION pltclu; --     pltcl
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

または、コンソールから（plpythonは信頼できない形式でのみ利用可能です）：

 createlang plpythonu
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Cで動的にロードされる関数

Cで動的に読み込まれる関数は、動的に読み込まれる（または共有される）ライブラリに含まれ、関数が最初に呼び出されたときに読み込まれます。 そのような関数を作成する例：

 CREATE OR REPLACE FUNCTION grayscale ( r double precision, g double precision, b double precision ) RETURNS double precision AS 'utils', 'grayscale' LANGUAGE C STRICT;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この例では、 グレースケール関数（3つのfloatパラメーターを持ち、 floatを返す）を作成します。これは、 utilsダイナミックライブラリにあります。 STRICTキーワードは、少なくとも1つの引数がNULLの場合に関数がNULLを返すように使用されます。絶対パスが指定されていない場合、 dynamic_library_path変数で指定されたディレクトリを意味し、その値は次のように表示できます。

 SELECT current_setting ( 'dynamic_library_path' );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

変数の値または動的ライブラリへのパスが$ libdirで始まる場合、 $ libdirはPostgreSQLライブラリを含むディレクトリへのパスに置き換えられます。これは、コンソールコマンドを使用して確認できます。

 pg_config --pkglibdir
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ライブラリのロードは、 PostgreSQLデーモンが実行されているユーザー権限（通常はpostgres ）で実行されるため、このユーザーはライブラリへのアクセス権限を持っている必要があります。



関数には、 バージョン0 （非推奨）とバージョン1の 2種類の規則があります。 バージョン0の機能は移植性がなく、機能が制限されているため、 バージョン1の機能がさらに暗示されています。 バージョン1を使用していることを示すには、関数を定義する前に特別なマクロでマークする必要があります。

 PG_FUNCTION_INFO_V1(grayscale);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

動的ライブラリ構造

各ライブラリには、特定のマジックブロック（ファイルの数に関係なく1つ）が必要です。そのため、たとえば、古いバージョンのPostgreSQLサーバーと、ライブラリが構築されるPostgreSQLのバージョンなどの不一致を検出できます。 このブロックは次のように宣言されます。

 #include <fmgr.h> #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

必要に応じて、ライブラリをロードした後に呼び出される_PG_init初期化関数 （パラメーターを持たずvoidを返す）、およびライブラリをアンロードする前に呼び出される_PG_fini初期化関数（ _PG_initと同じシグネチャを持つ ）を定義できます。 ドキュメントには、ライブラリが現在アンロードされていないことが記載されているため、 _PG_fini関数が呼び出されることはありません。 関数の例：

 void _PG_init() { createLog(); } void _PG_fini() { destroyLog(); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ライブラリの関数には特定の種類があります。マクロ、引数の受け取り、結果の返送、およびその他の操作のために、特別なマクロが提供されます（以下で詳しく説明します）。

 Datum grayscale(PG_FUNCTION_ARGS) { float8 r = PG_GETARG_FLOAT8(0); float8 g = PG_GETARG_FLOAT8(1); float8 b = PG_GETARG_FLOAT8(2); PG_RETURN_FLOAT8(0.299 * r + 0.587 * g + 0.114 * b); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データ型

関数で使用される基本的なデータ型は、次の3つの型に分類されます。

• 値によって送信される固定長
• 固定長、標識付き
• ポインターによって渡される可変長

最初のタイプのタイプのサイズは、1、2、または4バイトです（プラットフォームのsizeof（データム）が 8の場合は8）。 独自のタイプを定義するとき（たとえばtypedefを使用 ）、すべてのアーキテクチャでサイズが同じであることを確認する必要があります。



ポインターによって渡される固定長型は構造体です。 pallocを使用して 、それら（および第3種の型）にメモリを割り当てる必要があります。次に例を示します。

 typedef struct { float r, g, b, a; } Color; Color *color = (Color*)palloc(sizeof(Color));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

3番目のタイプのタイプの場合、タイプ全体のサイズ（データサイズ+フィールドサイズ）を格納するためのフィールド（4バイト）と、実際、このフィールドの後ろに連続して配置されるデータ自体を定義する必要があります。 これは、次の形式の構造を使用して実行できます。

 typedef struct { int32 length; char data[1]; } text;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

タイプサイズのフィールドの値は、 SET_VARSIZEマクロを使用して暗黙的に設定されます。 ポインターによって渡される可変長タイプを操作するための他のマクロ：

 char data[10]; ... text *string = (text*)palloc(VARHDRSZ + 20); // VARHDRSZ -      SET_VARSIZE(string, VARHDRSZ + 20); // SET_VARSIZE -    memcpy(VARDATA(string), data, 10); // VARDATA -    
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

CとSQLの関数のタイプ間の対応は、 この表に示されています。



ポインタによって転送されるタイプに関しては、このポインタが指すデータは変更できないことに注意してください。これは、ディスク上に直接配置されたデータである可能性があり、損傷につながる可能性があるためです。 結果は完全に楽しいものではありません。

関数構造

関数の署名は次のようになります。

 Datum grayscale(PG_FUNCTION_ARGS);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

データは、基本的にポインタのtypedefである関数の戻り値用の特別な型です。 マクロPG_FUNCTION_ARGSは 、関数のパラメーターに関するメタ情報（呼び出しのコンテキスト、値がNULLかどうかなど）を含む構造へのポインターに展開されます。 関数の引数には、 PG_GETARG_ *マクロを使用してアクセスします。

 float8 r = PG_GETARG_FLOAT8(0); //     float8 int32 x = PG_GETARG_INT32(1); //     int32 text *msg = PG_GETARG_TEXT_P(2); //     text*
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

SQL関数がSTRICTなしで宣言されている場合、PG_ARGISNULLを使用して引数の値がNULLかどうかを確認できます 。 PG_RETURN_NULLを介してNULLとして関数の結果を返すことができます 。 例として、 STRICTを使用しない関数実装がどのように見えるかを見てみましょう。

 Datum grayscale(PG_FUNCTION_ARGS) { if (PG_ARGISNULL(0) || PG_ARGISNULL(1) || PG_ARGISNULL(2)) { PG_RETURN_NULL(); } float8 r = PG_GETARG_FLOAT8(0); float8 g = PG_GETARG_FLOAT8(1); float8 b = PG_GETARG_FLOAT8(2); PG_RETURN_FLOAT8(0.299 * r + 0.587 * g + 0.114 * b); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

機能例

さて、Cでストアド関数を書く方法を知ったので、例をまとめて見てみましょう。 環境は次のとおりです。

• オペレーティングシステム：Ubuntu 12.10
• PostgreSQLバージョン：9.3
• コンパイラ：gcc 4.7.2

次の内容でutils.cファイルを作成します。

 #include <postgres.h> #include <fmgr.h> #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif PG_FUNCTION_INFO_V1(grayscale); Datum grayscale(PG_FUNCTION_ARGS) { float8 r = PG_GETARG_FLOAT8(0); float8 g = PG_GETARG_FLOAT8(1); float8 b = PG_GETARG_FLOAT8(2); PG_RETURN_FLOAT8(0.299 * r + 0.587 * g + 0.114 * b); }
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次に、utils.cをオブジェクトファイルにコンパイルします。 位置に依存しないコードを使用する必要があります（ gccの場合、これはfpicオプションです）。

 cc -I/usr/local/pgsql/include/server -fpic -c utils.c
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

pg_config --includedir-serverコマンドは、ヘッダーファイルがあるディレクトリの場所を示します。 オブジェクトファイルを動的ライブラリとしてリンクします（すべてが正常な場合は、 utils.so動的ライブラリが必要です。これを/ usr / local / pgsql / libにコピーします）。

 cc -shared -L/usr/local/pgsql/lib -lpq -o utils.so utils.o
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次に、データベースに接続して、その中にgrayscale_c関数を作成し、いくつかのオプションを示します。

 CREATE OR REPLACE FUNCTION grayscale_c ( r double precision, g double precision, b double precision ) RETURNS double precision AS 'utils', 'grayscale' LANGUAGE C STRICT VOLATILE COST 100;;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そのパフォーマンスを確認します。

 SELECT grayscale_c ( 0.6, 0.5, 0.5 ); -- : 0.5299
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

しかし、それだけではありません。 この関数を同様の関数と比較しますが、plpgsqlで実行します。 grayscale_plpgsqlと呼びましょう ：

 CREATE OR REPLACE FUNCTION grayscale_plpgsql ( r double precision, g double precision, b double precision ) RETURNS double precision AS $BODY$ BEGIN RETURN 0.299 * r + 0.587 * g + 0.114 * b; END $BODY$ LANGUAGE plpgsql STRICT VOLATILE COST 100;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

そして、いくつかのテストをしましょう：

 CREATE TABLE color AS SELECT random () AS r, random () AS g, random () AS b FROM generate_series ( 1, 1000000 ); SELECT grayscale_c ( r, g, b ) FROM color; --   : 926  SELECT grayscale_plpgsql ( r, g, b ) FROM color; --   : 3679 
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ちょっとしたチェック：

 SELECT * FROM color WHERE grayscale_c ( r, g, b ) != grayscale_plpgsql ( r, g, b ); -- 0 
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

非常に良い結果です。



これまで見てきたように、動的にロードされる関数をCで作成することはそれほど難しくありません。 原則として、実装には困難が潜んでいます。



PSご清聴ありがとうございました。



参照：

• 公式のPostgreSQLドキュメント