Hologres:Prosedur tersimpan

Batasan

• Hologres mendukung prosedur tersimpan yang menggunakan sintaks PL/pgSQL mulai dari versi Hologres V3.0. Untuk informasi lebih lanjut mengenai sintaks PL/pgSQL, lihat SQL Procedural Language.

• Hologres mendukung transaksi yang terdiri dari beberapa pernyataan Data Definition Language (DDL) atau beberapa pernyataan Data Manipulation Language (DML) dalam prosedur tersimpan. Namun, transaksi yang mencampurkan pernyataan DDL dan DML tidak didukung. Untuk informasi lebih lanjut, lihat Transaksi.

• Prosedur tersimpan tidak mendukung nilai kembali dan tidak dapat digunakan sebagai user-defined function (UDF).

Izin

• Untuk menggunakan CREATE PROCEDURE, Anda harus memiliki izin Create pada database—izin yang sama dengan yang diperlukan untuk membuat tabel. Untuk informasi lebih lanjut, lihat SQL-CREATE PROCEDURE.

• Untuk menggunakan CREATE OR REPLACE, Anda harus memiliki izin Create pada database dan izin OWNER pada prosedur tersimpan target. Untuk informasi lebih lanjut, lihat SQL-CREATE PROCEDURE.

• Untuk mengeksekusi prosedur tersimpan, Anda harus memiliki izin EXECUTE pada prosedur tersebut. Untuk informasi lebih lanjut, lihat SQL-CALL.

Referensi perintah

Sintaks prosedur tersimpan yang didukung oleh Hologres kompatibel dengan PostgreSQL, sebagai berikut:

Buat prosedur tersimpan

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

Untuk informasi lebih lanjut mengenai parameter, lihat SQL-CREATE PROCEDURE.

Ubah prosedur tersimpan

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

Untuk informasi lebih lanjut mengenai parameter, lihat SQL-ALTER PROCEDURE.

Hapus prosedur tersimpan

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

Untuk informasi lebih lanjut mengenai parameter, lihat SQL-DROP PROCEDURE.

Jalankan prosedur tersimpan

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

Untuk informasi lebih lanjut mengenai parameter, lihat SQL-CALL.

Contoh

• Contoh 1: Prosedur tersimpan dengan transaksi yang berisi beberapa pernyataan DDL.

  1. Buat prosedur tersimpan.

     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. Panggil prosedur tersimpan. Tabel a1 dan a2 dibuat, sedangkan tabel a3 dan a4 tidak dibuat.

     CALL procedure_1();

• Contoh 2: Prosedur tersimpan dengan transaksi yang berisi beberapa pernyataan DML.

  1. Buat prosedur tersimpan.

     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;
     $$;

  2. Jalankan prosedur tersimpan.

     • Jalankan procedure_2. ROLLBACK didukung, sehingga data tidak ditulis.

       -- Aktifkan fitur transaksi DML.
       SET hg_experimental_enable_transaction = ON;
       
       -- Jalankan prosedur tersimpan.
       CALL procedure_2();

     • Jalankan procedure_3. Data berhasil ditulis.

       -- Aktifkan fitur transaksi DML.
       SET hg_experimental_enable_transaction = ON;
       
       -- Jalankan prosedur tersimpan.
       CALL procedure_3();

• Contoh 3: Prosedur tersimpan dengan pernyataan DDL dan DML.

  1. Buat prosedur tersimpan. Karena Hologres tidak mendukung transaksi yang mencampurkan pernyataan DDL dan DML, Anda harus melakukan commit terpisah untuk pernyataan DDL dan DML dalam prosedur tersimpan.

     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. Jalankan prosedur tersimpan. Tabel berhasil dibuat dan data berhasil ditulis.

     -- Aktifkan fitur transaksi DML.
     SET hg_experimental_enable_transaction = ON;
     
     -- Jalankan prosedur tersimpan.
     CALL procedure_4();

• Contoh 4: Prosedur tersimpan yang menunjukkan operasi umum, seperti mendefinisikan parameter input, variabel antara, loop, kondisi IF, dan EXCEPTION.

  1. Buat prosedur tersimpan.

     CREATE OR REPLACE PROCEDURE procedure_5(input text)
     LANGUAGE 'plpgsql'
     AS $$
     -- Definisikan variabel antara.
     DECLARE
     sql1 text;
     BEGIN
         -- Tulis satu baris data ke tabel yang ditentukan oleh parameter input.
         EXECUTE 'insert into ' || input || ' values(1);';
         COMMIT;
     
         -- Buat tabel a3.
         CREATE TABLE a3(key int);
         COMMIT;
     
         -- Gunakan variabel antara untuk menulis satu baris data ke tabel a3.
         sql1 = 'insert into a3 values(1);';
         EXECUTE sql1;
     
         -- Definisikan loop FOR.
         FOR i IN 1..10 LOOP
             BEGIN
                 -- i=1 sudah ada di tabel, sehingga hanya satu entri log yang dicetak.
                 IF i IN (SELECT KEY FROM a3) THEN
                     RAISE NOTICE 'Data already exists.';
                 -- Angka lain tidak ada di tabel. Prosedur mencoba menulisnya, memunculkan EXCEPTION, lalu melakukan commit.
                 ELSE
                     INSERT INTO a3 VALUES(i);
                     RAISE EXCEPTION 'HG_PLPGSQL_NEED_RETRY';
                     COMMIT; 
                 END IF;
             -- Untuk EXCEPTION yang muncul, cetak entri log.
             EXCEPTION 
                 WHEN OTHERS THEN
                     RAISE NOTICE 'Catch error.';
             END;
         END LOOP;
     
     END;
     $$;

  2. Jalankan prosedur tersimpan. Nilai 1 ditulis ke tabel a3, sedangkan data lain tidak ditulis. Semua log terkait dicetak.

     -- Aktifkan fitur transaksi DML.
     SET hg_experimental_enable_transaction = ON;
     
     -- Jalankan prosedur tersimpan.
     CALL procedure_5('a1');

Kelola prosedur tersimpan

• Lihat prosedur tersimpan yang telah dibuat.

  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;

• Lihat definisi prosedur tersimpan.

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

FAQ

Hologres merupakan sistem terdistribusi dengan node front-end (FE) yang juga terdistribusi. Ketika terjadi perubahan DDL pada sebuah tabel, metadata harus disinkronkan secara real time di seluruh node FE. Jika sinkronisasi metadata belum lengkap, perubahan DDL tersebut dapat gagal. Dalam kebanyakan kasus, Hologres secara otomatis mencoba ulang operasi tersebut, sehingga Anda tidak perlu mengirim ulang perubahan DDL secara manual. Namun, percobaan ulang otomatis tidak didukung di dalam prosedur tersimpan. Dalam skenario ini, pesan error HG_PLPGSQL_NEED_RETRY akan dikembalikan.

Untuk tabel yang sering mengalami perubahan DDL, definisikan logika retry manual dalam prosedur tersimpan guna mencegah error yang sering terjadi. Logika retry-nya adalah sebagai berikut:

CREATE OR REPLACE PROCEDURE procedure_6()
LANGUAGE 'plpgsql'
AS $$
BEGIN
    WHILE TRUE LOOP
        BEGIN
            -- Coba eksekusi pernyataan DDL. Jika berhasil, keluar dari loop.
            CREATE TABLE a3(key int);
            COMMIT;
            EXIT;
        EXCEPTION
            -- Jika terjadi error HG_PLPGSQL_NEED_RETRY, cetak log dan coba ulang secara otomatis.
            WHEN HG_PLPGSQL_NEED_RETRY THEN 
                RAISE NOTICE 'DDL need retry';
        END;
    END LOOP;
END;
$$;