rubytomato's “Getting Started”

Webアプリケーション開発の入門的な記事を投稿していきます。

PostgreSQLのプロシージャで大量のテストデータを作成する

はじめに

PostgreSQLPL/pgSQLという言語で作成するプロシージャで大量のテストデータを作成する方法を簡単に説明します。 テストや検証で大量データが必要なときに、この記事のソースコードを改修して利用することを想定しています。

環境

この記事の内容はWindows 10で作成、動作確認しています。

参考

大量データを格納するテーブル

random_tblというテーブルを作成します。id以外のカラムはランダムな値で更新します。そのためカラム名にも意味は持たせていません。

CREATE TABLE random_tbl (
  id BIGSERIAL,
  fld_int1 INTEGER NULL,
  fld_var2 VARCHAR(1000) NULL,
  fld_boo3 BOOLEAN NULL,
  fld_dat4 DATE NULL,
  fld_big5 BIGINT NULL
  PRIMARY KEY (id)
);

テストデータを作成するプロシージャ

SET plpgsql.extra_warnings TO 'all';
SET plpgsql.extra_errors TO 'all';

DROP FUNCTION IF EXISTS gen_random_integer();

/**
 * 0から2147483647までの範囲でランダムな数値を返す
 */
CREATE OR REPLACE FUNCTION gen_random_integer() RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN (RANDOM() * 2147483647)::INTEGER;
END
$$;

DROP FUNCTION IF EXISTS gen_random_bigint();

/**
 * 0から9223372036854775807までの範囲でランダムな数値を返す
 */
CREATE OR REPLACE FUNCTION gen_random_bigint() RETURNS BIGINT
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN (RANDOM() * 9223372036854775807)::BIGINT;
END
$$;

DROP FUNCTION IF EXISTS gen_random_varchar();

/**
 * 200文字から1000文字のランダムな文字列を返す
 */
CREATE OR REPLACE FUNCTION gen_random_varchar() RETURNS VARCHAR
LANGUAGE plpgsql
AS $$
DECLARE
  v_rnd INTEGER := 0;
  v_tmp VARCHAR := '';
  v_str VARCHAR := '';
BEGIN
  --1から5の乱数
  v_rnd := (RANDOM() * 4)::INTEGER + 1;

  --1ループあたり200文字のランダムな文字列を生成する
  --ループ回数はランダム、1ループで200文字、5ループで1000文字
  FOR i IN 1..v_rnd LOOP
    --code point 12353(ぁ) から 12435(ん)までのランダムな文字を100文字連結する
    SELECT STRING_AGG(CHR(12353 + (RANDOM() * 82)::INTEGER), '') INTO v_tmp FROM GENERATE_SERIES(1, 100);
    v_str := v_str || v_tmp;
    --code point 12449(ァ) から 12533(ヵ)までのランダムな文字を100文字連結する
    SELECT STRING_AGG(CHR(12449 + (RANDOM() * 84)::INTEGER), '') INTO v_tmp FROM GENERATE_SERIES(1, 100);
    v_str := v_str || v_tmp;
  END LOOP;

  RETURN v_str;
END
$$;

DROP FUNCTION IF EXISTS gen_random_date(DATE, DATE);

/**
 * パラメータv_date_from から v_date_toの範囲でランダムな日付を返す
 */
CREATE OR REPLACE FUNCTION gen_random_date(v_date_from DATE, v_date_to DATE) RETURNS DATE
LANGUAGE plpgsql
AS $$
DECLARE
  v_rnd INTEGER := 0;
  v_numOfDays INTEGER := v_date_to - v_date_from;
BEGIN
  v_rnd := (RANDOM() * v_numOfDays)::INTEGER;
  RETURN v_date_from + v_rnd;
END
$$;

DROP FUNCTION IF EXISTS gen_random_boolean();

/**
 * ランダムにtrue/falseを返す
 */
CREATE OR REPLACE FUNCTION gen_random_boolean() RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
  v_rnd INTEGER := 0;
BEGIN
  v_rnd := (RANDOM() * 1)::INTEGER;
  IF v_rnd = 0 THEN
    RETURN FALSE;
  ELSE
    RETURN TRUE;
  END IF;
END
$$;

DROP PROCEDURE IF EXISTS generate_random_data(INTEGER, INTEGER, DATE, DATE, BOOLEAN);

/**
 * ランダムなデータを生成するメイン処理
 */
CREATE OR REPLACE PROCEDURE generate_random_data(
  v_generate_num INTEGER,                -- 生成件数
  v_commit_num INTEGER DEFAULT 1000,     -- コミットする件数
  v_date_from DATE DEFAULT '1901-01-01', -- 生成する日付の範囲(開始)
  v_date_to DATE DEFAULT '2099-12-31',   -- 生成する日付の範囲(終了)
  v_truncate BOOLEAN DEFAULT FALSE       -- truncateするか
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_seed DOUBLE PRECISION := TO_CHAR(CURRENT_TIMESTAMP, 'US')::INTEGER * 0.000001;
  v_row random_tbl%ROWTYPE;
BEGIN
  RAISE NOTICE 'calling ''generate_random_data'' at %.', now();
  RAISE NOTICE 'args generate_num:(%) commit_num:(%) date_from:(%) date_to:(%) truncate:(%)', v_generate_num, v_commit_num, v_date_from, v_date_to, v_truncate;

  IF v_date_from > v_date_to THEN
    RAISE EXCEPTION 'invalid date range from:(%) to:(%)', v_date_from, v_date_to USING HINT = 'check v_date_from or v_date_to parameter';
  END IF;

  IF v_truncate = TRUE THEN
    RAISE NOTICE 'truncate table random_tbl';
    TRUNCATE TABLE random_tbl;
  END IF;

  --RANDOM()のseedを設定
  PERFORM SETSEED(v_seed);

  FOR i IN 1..v_generate_num LOOP
    v_row.fld_int1 := gen_random_integer();
    v_row.fld_var2 := gen_random_varchar();
    v_row.fld_boo3 := gen_random_boolean();
    v_row.fld_dat4 := gen_random_date(v_date_from, v_date_to);
    v_row.fld_big5 := gen_random_bigint();
    INSERT INTO random_tbl (fld_int1, fld_var2, fld_boo3, fld_dat4, fld_big5) VALUES (v_row.fld_int1, v_row.fld_var2, v_row.fld_boo3, v_row.fld_dat4, v_row.fld_big5);
    IF i % v_commit_num = 0 THEN
      RAISE NOTICE 'commit (%)', i;
      COMMIT;
    END IF;
  END LOOP;

END
$$;

プログラムの説明

ランダムな値を返すファンクション

PL/pgSQLではプロシージャ内にサブファンクションを定義することができません(それらしいことは可能です)。 なのでプロシージャとは別にファンクションを作成し、それらをプロシージャ内から呼ぶようにしています。

v_row.fld_int1 := gen_random_integer();
v_row.fld_var2 := gen_random_varchar();
v_row.fld_boo3 := gen_random_boolean();
v_row.fld_dat4 := gen_random_date(v_date_from, v_date_to);
v_row.fld_big5 := gen_random_bigint();

RANDOM関数のシード

RANDOM()関数のシードを設定するにはSETSEED(dp)関数を実行しますが、PL/pgSQL内から実行するにはPERFORMを使用します。

PERFORM SETSEED(v_seed);

プロシージャの作成

上記のソースコードをgenerate_random_data.sqlという名前のファイル保存した場合、コンパイルは下記のようになります。 PSQLでログインし\iメタコマンドでソースファイルを指定します。

初回実行時はファンクションもプロシージャも存在しないので下記のようなメッセージが表示されますが、NOTICEレベルなので問題ありません。

> \i generate_random_data.sql
SET
SET
psql:generate_random_data.sql:4: NOTICE:  function gen_random_integer() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
psql:generate_random_data.sql:17: NOTICE:  function gen_random_bigint() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
psql:generate_random_data.sql:30: NOTICE:  function gen_random_varchar() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
psql:generate_random_data.sql:61: NOTICE:  function gen_random_date(date,date) does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
psql:generate_random_data.sql:78: NOTICE:  function gen_random_boolean() does not exist, skipping
DROP FUNCTION
CREATE FUNCTION
psql:generate_random_data.sql:98: NOTICE:  procedure generate_random_data(pg_catalog.int4,pg_catalog.int4,date,date,pg_catalog.bool) does not exist, skipping
DROP PROCEDURE
CREATE PROCEDURE

2回目以降は

> \i generate_random_data.sql
SET
SET
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP PROCEDURE
CREATE PROCEDURE

のようになります。

プロシージャの実行

CALLコマンドで実行します。

> call generate_random_data(1000000, 1000, '2020-01-01', '2020-12-31', TRUE);

引数

全部で5つの引数を取ります。

引数の位置 説明 デフォルト値
1 生成するレコード件数を指定 なし
2 コミットする単位 1000
3 ランダムに生成する日付の範囲(from) '1901-01-01'
4 ランダムに生成する日付の範囲(to) '2099-12-31'
5 実行時にrandom_tblをtruncateするか FALSE

第1引数の生成するレコード件数以外はデフォルト値があるので、下記のようにも実行できます。

> call generate_random_data(100000);

プロシージャの実行時間を計測する

\timingメタコマンドで実行時間を計測できます。

> \timing
タイミングは on です。
> call generate_random_data(100000);

// 省略

CALL
時間: 47202.196 ミリ秒(00:47.202)

Tips

ランダムな時刻を持つ日付を生成する

現在の日付にランダムな時刻を付加します。

DO $$
DECLARE
  v_interval INTERVAL := '0';
  v_rnd_ts TIMESTAMP;
BEGIN
  v_interval := (((RANDOM() * 86398)::INTEGER + 1)::VARCHAR)::INTERVAL;
  v_rnd_ts := CURRENT_DATE::TIMESTAMP + v_interval;
  RAISE NOTICE 'random timestamp=%', v_rnd_ts;
END
$$;

配列のインデックスを0から始める

PostgreSQLはデフォルトでは配列のインデックスは1から始まります。 たとえば、下記のように配列を宣言した場合

DO $$
DECLARE
  v_bool BOOLEAN[] := '{f, t}';
BEGIN
  RAISE NOTICE '1=%, 2=%', v_bool[1], v_bool[2];
END
$$;

結果はこのようになります。

NOTICE:  1=f, 2=t

インデックスを0から始めるには下記のように宣言します。

DO $$
DECLARE
  v_bool BOOLEAN[] := '[0:1]={f, t}';
BEGIN
  RAISE NOTICE '0=%, 1=%', v_bool[0], v_bool[1];
END
$$;
NOTICE:  0=f, 1=t

となります。

無名ブロック

PostgreSQLにはDOという無名ブロックを実行するコマンドがあります。(標準SQLDOはありません。) DOコマンドで試したいコードを無名ブロックとして実行することが可能です。 以下のコードを実行するには、コピーしてPSQLのプロンプトへペーストするだけです。プロシージャやファンクションの作成は行われないので簡単にコードを試すことができます。

DO $$
DECLARE
  v_rnd INTEGER := 0;
BEGIN
  PERFORM setseed(0.053);
  v_rnd := (RANDOM() * 10)::INTEGER;
  RAISE NOTICE '0=%', v_rnd;
END
$$;