Hologres でのストアドプロシージャの作成と実行 - Hologres

ストアドプロシージャは、データベースに格納され、繰り返し呼び出すことができるプリコンパイルされた SQL ステートメントのコレクションです。このトピックでは、Hologres でストアドプロシージャを使用する方法について説明します。

制限事項

Hologres V3.0 以降、Hologres は PL/pgSQL 構文を使用するストアドプロシージャをサポートしています。PL/pgSQL 構文の詳細については、「SQL Procedural Language」をご参照ください。
Hologres のストアドプロシージャでは、複数のデータ定義言語 (DDL) ステートメントを含むトランザクションと、複数のデータ操作言語 (DML) ステートメントを含むトランザクションがサポートされています。DDL ステートメントと DML ステートメントを混在させるトランザクションはサポートされていません。詳細については、「トランザクション」をご参照ください。
ストアドプロシージャは戻り値をサポートしておらず、ユーザー定義関数 (UDF) として使用することはできません。

権限

CREATE PROCEDURE を使用するには、データベースに対する作成権限が必要です。これは、テーブルを作成するために必要な権限と同じです。詳細については、「SQL-CREATE PROCEDURE」をご参照ください。
CREATE OR REPLACE を使用するには、データベースに対する作成権限と、ターゲットのストアドプロシージャに対する OWNER 権限の両方が必要です。詳細については、「SQL-CREATE PROCEDURE」をご参照ください。
ストアドプロシージャを実行するには、ストアドプロシージャに対する実行権限が必要です。詳細については、「SQL-CALL」をご参照ください。

コマンドリファレンス

Hologres がサポートするストアドプロシージャの構文は、PostgreSQL と互換性があります。構文は次のとおりです。

ストアドプロシージャの作成

CREATE [ OR REPLACE ] PROCEDURE
    <procedure_name> ([<argname> <argtype>])
LANGUAGE 'plpgsql'
AS <definition>;

パラメーター	説明
procedure_name	ストアドプロシージャの名前。
argname	パラメーターの名前。このパラメーターはオプションであり、ストアドプロシージャの設計によって異なります。
argtype	パラメーターのデータの型。
definition	ストアドプロシージャの定義。SQL ステートメントまたはコードブロックを指定できます。

パラメーターの詳細については、「SQL-CREATE PROCEDURE」をご参照ください。

ストアドプロシージャの変更

ALTER PROCEDURE <procedure_name> ([<argname> <argtype>])
    OWNER TO <new_owner> | CURRENT_USER | SESSION_USER;

パラメーター	説明
new_owner	新しいユーザー名。
CURRENT_USER	現在のユーザー。
SESSION_USER	セッションユーザー。

パラメーターの詳細については、「SQL-ALTER PROCEDURE」をご参照ください。

ストアドプロシージャの削除

DROP PROCEDURE [ IF EXISTS ] <procedure_name> ([<argname> <argtype>]);

パラメーターの詳細については、「SQL-DROP PROCEDURE」をご参照ください。

ストアドプロシージャの実行

CALL <procedure_name> ([<argument>]);

パラメーター	説明
argument	ストアドプロシージャに必要な引数。これらの引数はオプションであり、ストアドプロシージャの設計によって異なります。

パラメーターの詳細については、「SQL-CALL」をご参照ください。

例

例 1: 複数の DDL ステートメントを含むトランザクションを持つストアドプロシージャ。
1. ストアドプロシージャを作成します。
```
CREATE OR REPLACE PROCEDURE procedure_1()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    --- TXN1 --- 
    CREATE TABLE a1(key int);
    CREATE TABLE a2(key int);
    COMMIT; 

    --- TXN2 ---
    CREATE TABLE a3(key int);
    CREATE TABLE a4(key int);
    ROLLBACK;
END; 
$$;
```
2. ストアドプロシージャを呼び出します。テーブル a1 と a2 が作成されます。テーブル a3 と a4 は作成されません。
```
CALL procedure_1();
```

例 2: 複数の DML ステートメントを含むトランザクションを持つストアドプロシージャ。

ストアドプロシージャを作成します。

CREATE OR REPLACE PROCEDURE procedure_2()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
    ROLLBACK;
END;
$$;

CREATE OR REPLACE PROCEDURE procedure_3()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    INSERT INTO a2 VALUES(2);
END;
$$;

ストアドプロシージャを実行します。

procedure_2 を実行します。ROLLBACK がサポートされているため、データは書き込まれません。

-- DML トランザクション機能を有効化します。
SET hg_experimental_enable_transaction = ON;

-- ストアドプロシージャを実行します。
CALL procedure_2();

procedure_3 を実行します。データは正常に書き込まれます。

-- DML トランザクション機能を有効化します。
SET hg_experimental_enable_transaction = ON;

-- ストアドプロシージャを実行します。
CALL procedure_3();

例 3: DDL ステートメントと DML ステートメントの両方を含むストアドプロシージャ。
1. ストアドプロシージャを作成します。Hologres は DDL ステートメントと DML ステートメントを混在させるトランザクションをサポートしていないため、ストアドプロシージャ内で DDL ステートメントと DML ステートメントを個別に COMMIT する必要があります。
```
CREATE OR REPLACE PROCEDURE procedure_4()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    INSERT INTO a1 VALUES(1);
    COMMIT;	
    CREATE TABLE bb(key int);
    COMMIT;	
    INSERT INTO a1 VALUES(2);
    INSERT INTO bb VALUES(1);    
    COMMIT;	
END;
$$;
```
2. ストアドプロシージャを実行します。テーブルが作成され、データは正常に書き込まれます。
```
-- DML トランザクション機能を有効化します。
SET hg_experimental_enable_transaction = ON;

-- ストアドプロシージャを実行します。
CALL procedure_4();
```

例 4: 入力パラメーター、中間変数、ループ、IF 条件、および EXCEPTION の定義など、一般的な操作を示すストアドプロシージャ。

ストアドプロシージャを作成します。

CREATE OR REPLACE PROCEDURE procedure_5(input text)
LANGUAGE 'plpgsql'
AS $$
-- 中間変数を定義します。
DECLARE
sql1 text;
BEGIN
    -- 入力パラメーターで指定されたテーブルに 1 行のデータを書き込みます。
    EXECUTE 'insert into ' || input || ' values(1);';
    COMMIT;

    -- テーブル a3 を作成します。
    CREATE TABLE a3(key int);
    COMMIT;

    -- 中間変数を使用してテーブル a3 に 1 行のデータを書き込みます。
    sql1 = 'insert into a3 values(1);';
    EXECUTE sql1;

    -- FOR ループを定義します。
    FOR i IN 1..10 LOOP
        BEGIN
            -- i=1 はすでにテーブルに存在するため、1 つのログエントリのみが出力されます。
            IF i IN (SELECT KEY FROM a3) THEN
                RAISE NOTICE 'Data already exists.';
            -- その他の数値はテーブルに存在しません。プロシージャはそれらを書き込もうとし、EXCEPTION を発生させてから COMMIT します。
            ELSE
                INSERT INTO a3 VALUES(i);
                RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                COMMIT; 
            END IF;
        -- 発生した EXCEPTION について、ログエントリを出力します。
        EXCEPTION 
            WHEN OTHERS THEN
                RAISE NOTICE 'Catch error.';
        END;
    END LOOP;

END;
$$;

ストアドプロシージャを実行します。値 1 がテーブル a3 に書き込まれます。その他のデータは書き込まれません。関連するすべてのログが出力されます。
```
-- DML トランザクション機能を有効化します。
SET hg_experimental_enable_transaction = ON;

-- ストアドプロシージャを実行します。
CALL procedure_5('a1');
```

ストアドプロシージャの管理

作成されたストアドプロシージャの表示。

SELECT
    p.proname AS procedure_name,
    pg_get_function_identity_arguments(p.oid) AS argument_types,
    REPLACE(pg_get_functiondef(p.oid),'$procedure$','$$') AS procedure_detail,
    n.nspname AS schema_name,
    r.rolname AS owner_name,
    d.description AS description
FROM
    pg_proc p
    INNER JOIN pg_namespace n ON p.pronamespace = n.oid
    INNER JOIN pg_roles r ON p.proowner = r.oid
    LEFT JOIN pg_description d ON p.oid = d.objoid
WHERE
    r.rolname != 'holo_admin'
    AND p.prokind = 'p'
ORDER BY
    n.nspname,
    p.proname;

ストアドプロシージャ定義の表示。

SELECT pg_get_functiondef('<procedure_name>'::regproc);

よくある質問

Hologres は分散システムであり、そのフロントエンド (FE) ノードも分散されています。テーブルで DDL 変更が発生すると、メタデータは異なる FE ノード間でリアルタイムに同期される必要があります。メタデータの同期が完了していない場合、DDL 変更が失敗する可能性があります。ほとんどの場合、Hologres は操作を自動的にリトライします。DDL 変更を手動で再送信する必要はありません。ただし、ストアドプロシージャ内では自動リトライはサポートされていません。このシナリオでは、エラーメッセージ HG_PLPGSQL_NEED_RETRY が返されます。

DDL 変更が頻繁に発生するテーブルの場合、頻繁なエラーを防ぐために、ストアドプロシージャで手動リトライロジックを定義します。リトライロジックは次のとおりです。

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- DDL ステートメントの実行を試行します。成功した場合は、ループを終了します。
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- HG_PLPGSQL_NEED_RETRY エラーが発生した場合、ログを出力して自動的にリトライします。
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL need retry';
        END;
    END LOOP;
END;
$$;