PostgreSQL 16 上での履歴テーブルの実装

0_install_extensions.sql

-- Extension: btree_gist

-- DROP EXTENSION btree_gist;

CREATE EXTENSION IF NOT EXISTS btree_gist
    SCHEMA public
    VERSION "1.7";

1_prepare.sql

-- SCHEMA: lib_history_table_example

-- DROP SCHEMA IF EXISTS lib_history_table_example ;

CREATE SCHEMA IF NOT EXISTS lib_history_table_example
    AUTHORIZATION pg_database_owner;

-- DOMAIN: lib_history_table_example.history_id

-- DROP DOMAIN IF EXISTS lib_history_table_example.history_id;

CREATE DOMAIN lib_history_table_example.history_id
    AS uuid
    DEFAULT gen_random_uuid();

ALTER DOMAIN lib_history_table_example.history_id OWNER TO pg_database_owner;

COMMENT ON DOMAIN lib_history_table_example.history_id
    IS '無意味な JOIN を避けるため、 ID はドメイン型で定義する。
現状 uuidv4 を使用する。
将来的に uuidv7 に切り替えて created_at と統合する事になりそう。';


-- DOMAIN: lib_history_table_example.record_timestamp

-- DROP DOMAIN IF EXISTS lib_history_table_example.record_timestamp;

CREATE DOMAIN lib_history_table_example.record_timestamp
    AS timestamp with time zone;

ALTER DOMAIN lib_history_table_example.record_timestamp OWNER TO pg_database_owner;

COMMENT ON DOMAIN lib_history_table_example.record_timestamp
    IS 'タイムスタンプの扱いを統合するため、ドメイン型を定義する。
複数の `now()` もしくは `current_timestamp` を使用する場合は `now()` もしくは `current_timestamp` の呼び出し毎に時刻を取得してしまうことを避けるため、トランザクション中で使用する事。
可能であれば `''-infinity''::timestamptz` 、 `''infinity''::timestamptz` を使用する事。';

-- DOMAIN: lib_history_table_example.record_valid_range

-- DROP DOMAIN IF EXISTS lib_history_table_example.record_valid_range;

CREATE DOMAIN lib_history_table_example.record_valid_range
    AS tstzrange;

ALTER DOMAIN lib_history_table_example.record_valid_range OWNER TO pg_database_owner;

ALTER DOMAIN lib_history_table_example.record_valid_range
    ADD CONSTRAINT record_valid_range CHECK (lower_inc(VALUE) = true AND upper_inc(VALUE) = false);

COMMENT ON DOMAIN lib_history_table_example.record_valid_range
    IS 'レコードの有効期間を定義する型。';

-- FUNCTION: lib_history_table_example.history_valid_range(lib_history_table_example.record_timestamp, lib_history_table_example.record_timestamp)

-- DROP FUNCTION IF EXISTS lib_history_table_example.history_valid_range(lib_history_table_example.record_timestamp, lib_history_table_example.record_timestamp);

CREATE OR REPLACE FUNCTION lib_history_table_example.history_valid_range(
	history_valid_from lib_history_table_example.record_timestamp DEFAULT '-infinity'::timestamp with time zone,
	history_valid_until lib_history_table_example.record_timestamp DEFAULT 'infinity'::timestamp with time zone)
    RETURNS lib_history_table_example.record_valid_range
    LANGUAGE 'sql'
    COST 100
    IMMUTABLE PARALLEL UNSAFE

RETURN tstzrange((history_valid_from)::timestamp with time zone, (history_valid_until)::timestamp with time zone, '[)'::text);

ALTER FUNCTION lib_history_table_example.history_valid_range(lib_history_table_example.record_timestamp, lib_history_table_example.record_timestamp)
    OWNER TO pg_database_owner;

COMMENT ON FUNCTION lib_history_table_example.history_valid_range(lib_history_table_example.record_timestamp, lib_history_table_example.record_timestamp)
    IS 'Generated Column はトリガ関数内では参照できないということもあり、生成処理は関数にしておいた方が良いのではないか。';

-- DOMAIN: lib_history_table_example.item_id

-- DROP DOMAIN IF EXISTS lib_history_table_example.item_id;

CREATE DOMAIN lib_history_table_example.item_id
    AS uuid
    DEFAULT gen_random_uuid();

ALTER DOMAIN lib_history_table_example.item_id OWNER TO pg_database_owner;

COMMENT ON DOMAIN lib_history_table_example.item_id
    IS '無意味な JOIN を避けるため、 ID はドメイン型で定義する。';

2_table.sql

-- Table: lib_history_table_example.items_history

-- DROP TABLE IF EXISTS lib_history_table_example.items_history;

CREATE TABLE IF NOT EXISTS lib_history_table_example.items_history
(
    history_id lib_history_table_example.history_id NOT NULL,
    history_created_at lib_history_table_example.record_timestamp NOT NULL DEFAULT current_timestamp,
    history_valid_from lib_history_table_example.record_timestamp DEFAULT current_timestamp,
    history_valid_until lib_history_table_example.record_timestamp DEFAULT NULL::timestamp with time zone,
    history_valid_range lib_history_table_example.record_valid_range GENERATED ALWAYS AS (lib_history_table_example.history_valid_range(history_valid_from, history_valid_until)) STORED,
    item_id lib_history_table_example.item_id NOT NULL,
    item_name text COLLATE pg_catalog."default" NOT NULL,
    item_price numeric NOT NULL,
    CONSTRAINT history_table_pkey PRIMARY KEY (history_id),
    CONSTRAINT history_table_check CHECK (history_valid_from IS NULL OR history_valid_until IS NULL OR history_valid_from::timestamp with time zone < history_valid_until::timestamp with time zone),
    CONSTRAINT example_history_id_valid_range_excl EXCLUDE USING gist (
        history_valid_range WITH &&,
        item_id WITH =)

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS lib_history_table_example.items_history
    OWNER to pg_database_owner;

COMMENT ON TABLE lib_history_table_example.items_history
    IS 'デフォルト値に `now()` もしくは `current_timestamp` を使用している。
レコード情報を一貫させるため、必ずトランザクション内で更新する事。';

-- Index: items_history_item_id_idx

-- DROP INDEX IF EXISTS lib_history_table_example.items_history_item_id_idx;

CREATE INDEX IF NOT EXISTS items_history_item_id_idx
    ON lib_history_table_example.items_history USING gist
    (item_id)
    WITH (buffering=auto)
    TABLESPACE pg_default;

-- FUNCTION: lib_history_table_example.items_history_insert_trigger()

-- DROP FUNCTION IF EXISTS lib_history_table_example.items_history_insert_trigger();

CREATE OR REPLACE FUNCTION lib_history_table_example.items_history_insert_trigger()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    row_count_until INTEGER := 0;
    row_count_from INTEGER := 0;
    total_count INTEGER;
BEGIN
	/*
	total_count が 1 より大きくなることは、他の制約が正しく動いている限り理論上あり得ない。
	必要なら total_count の検証は削除してもよい。
	*/
	IF NEW.history_valid_from is not NULL THEN
		WITH updated_rows_until AS (
			UPDATE lib_history_table_example.items_history
			SET history_valid_until = NEW.history_valid_from
			WHERE item_id = NEW.item_id
			  AND history_valid_until IS NULL
			  AND history_valid_from < NEW.history_valid_from
			RETURNING *
		)
		SELECT COUNT(*) INTO row_count_until FROM updated_rows_until;
        IF row_count_until = 1 THEN
            RAISE NOTICE 'update history_valid_until of oldest record for item_id: %.', NEW.item_id;
        END IF;
	END IF;

	IF NEW.history_valid_until is not NULL THEN
		WITH updated_rows_from AS (
			UPDATE lib_history_table_example.items_history
			SET history_valid_from = NEW.history_valid_until
			WHERE item_id = NEW.item_id
			  AND history_valid_from IS NULL
			  AND history_valid_until > NEW.history_valid_until
			RETURNING *
		)
		SELECT COUNT(*) from updated_rows_from INTO row_count_from;
        IF row_count_from = 1 THEN
            RAISE NOTICE 'update history_valid_from of newest record for item_id: %.', NEW.item_id;
        END IF;
	END IF;

	total_count := COALESCE(row_count_until, 0) + COALESCE(row_count_from, 0);

    IF total_count <= 1 THEN
        RETURN NEW;
    ELSE
	-- 23000 integrity_constraint_violation
        RAISE EXCEPTION 'Inconsistent history records detected for item_id: %, num records: %', NEW.item_id, total_count USING ERRCODE = '23000';
    END IF;

END;
$BODY$;

ALTER FUNCTION lib_history_table_example.items_history_insert_trigger()
    OWNER TO pg_database_owner;

COMMENT ON FUNCTION lib_history_table_example.items_history_insert_trigger()
    IS '既存レコードの履歴の先頭、もしくは最後尾のレコードの有効期間の境界値に NULL が入っている場合に、挿入するレコードの対応する境界値の値を代入し期間を確定します。
具体的には、挿入されるレコードの history_valid_from または history_valid_until の値を基に、既存の履歴レコードの history_valid_until または history_valid_from を更新します。
これにより履歴データの一貫性を保ったまま期間を逐次追記することができます。';

-- Trigger: before_insert_items_history

-- DROP TRIGGER IF EXISTS before_insert_items_history ON lib_history_table_example.items_history;

CREATE OR REPLACE TRIGGER before_insert_items_history
    BEFORE INSERT
    ON lib_history_table_example.items_history
    FOR EACH ROW
    EXECUTE FUNCTION lib_history_table_example.items_history_insert_trigger();

3_view.sql

-- FUNCTION: lib_history_table_example.table_items(timestamp with time zone)

-- DROP FUNCTION IF EXISTS lib_history_table_example.table_items(timestamp with time zone);

CREATE OR REPLACE FUNCTION lib_history_table_example.table_items(
	view_time timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
    RETURNS TABLE(item_id lib_history_table_example.item_id, item_name text, item_price numeric) 
    LANGUAGE 'sql'
    COST 100
    STABLE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
 SELECT item_id,
    item_name,
    item_price
FROM lib_history_table_example.items_history
WHERE history_valid_range::tstzrange @> view_time;
$BODY$;

ALTER FUNCTION lib_history_table_example.table_items(timestamp with time zone)
    OWNER TO app_owner;

-- View: lib_history_table_example.items

-- DROP VIEW lib_history_table_example.items;

CREATE OR REPLACE VIEW lib_history_table_example.items
 AS
 SELECT item_id,
    item_name,
    item_price
   FROM lib_history_table_example.table_items() items(item_id, item_name, item_price);

ALTER TABLE lib_history_table_example.items
    OWNER TO pg_database_owner;
COMMENT ON VIEW lib_history_table_example.items
    IS '`now()/current_timestamp` 時点でのテーブル。';

-- FUNCTION: lib_history_table_example.items_insert_trigger()

-- DROP FUNCTION IF EXISTS lib_history_table_example.items_insert_trigger();

CREATE OR REPLACE FUNCTION lib_history_table_example.items_insert_trigger()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
    IF EXISTS (SELECT 1 FROM lib_history_table_example.items WHERE item_id = NEW.item_id) THEN
	-- unique_violation
        RAISE EXCEPTION 'item_id % already exists', NEW.item_id USING ERRCODE = '23505';
    END IF;

	INSERT INTO lib_history_table_example.items_history (
		item_id,
		item_name,
		item_price)
	VALUES (
		NEW.item_id,
		NEW.item_name,
		NEW.item_price);

	RETURN NEW;
END;
$BODY$;

ALTER FUNCTION lib_history_table_example.items_insert_trigger()
    OWNER TO pg_database_owner;

-- Trigger: items_insert_trigger

-- DROP TRIGGER IF EXISTS items_insert_trigger ON lib_history_table_example.items;

CREATE OR REPLACE TRIGGER items_insert_trigger
    INSTEAD OF INSERT
    ON lib_history_table_example.items
    FOR EACH ROW
    EXECUTE FUNCTION lib_history_table_example.items_insert_trigger();


-- FUNCTION: lib_history_table_example.items_update_trigger()

-- DROP FUNCTION IF EXISTS lib_history_table_example.items_update_trigger();

CREATE OR REPLACE FUNCTION lib_history_table_example.items_update_trigger()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
	INSERT INTO lib_history_table_example.items_history (
		item_id,
		item_name,
		item_price)
	VALUES (
		NEW.item_id,
		NEW.item_name,
		NEW.item_price);
	RETURN NEW;
END;
$BODY$;

ALTER FUNCTION lib_history_table_example.items_update_trigger()
    OWNER TO pg_database_owner;

-- Trigger: items_update_trigger

-- DROP TRIGGER IF EXISTS items_update_trigger ON lib_history_table_example.items;

CREATE OR REPLACE TRIGGER items_update_trigger
    INSTEAD OF UPDATE 
    ON lib_history_table_example.items
    FOR EACH ROW
    EXECUTE FUNCTION lib_history_table_example.items_update_trigger();

4_test_raw.sql

INSERT INTO lib_history_table_example.items_history (history_id, history_created_at, history_valid_from, history_valid_until, item_id, item_name, item_price)
VALUES 
('cd9f8585-7037-4d54-bb10-50200a9b1f38', '2024-05-24 13:23:05.931379+00', '2024-05-24 13:23:05.931379+00', '2024-05-24 13:23:17.65513+00', 'db2d7521-7f77-4da0-8206-7e626a2c1615', 'good 1', 100),
('eda9714e-9248-4020-8c71-d7b6001b7aa3', '2024-05-24 13:23:17.65513+00', '2024-05-24 13:23:17.65513+00', '2024-05-24 13:24:45.136609+00', 'db2d7521-7f77-4da0-8206-7e626a2c1615', 'good 2', 110),
('16cf81ad-fa85-471f-b655-7a555dd7c0ea', '2024-05-24 13:24:45.136609+00', '2024-05-24 13:24:45.136609+00', null, 'db2d7521-7f77-4da0-8206-7e626a2c1615', 'good 3', 100);

INSERT INTO lib_history_table_example.items_history (item_id, item_name, item_price)
VALUES 
('db2d7521-7f77-4da0-8206-7e626a2c1616', 'good 1', 100);

INSERT INTO lib_history_table_example.items_history (item_id, item_name, item_price)
VALUES 
('db2d7521-7f77-4da0-8206-7e626a2c1616', 'good 2', 110);

INSERT INTO lib_history_table_example.items_history (item_id, item_name, item_price)
VALUES 
('db2d7521-7f77-4da0-8206-7e626a2c1616', 'good 3', 100);

INSERT INTO lib_history_table_example.items_history (history_id, history_created_at, history_valid_from, history_valid_until, item_id, item_name, item_price)
VALUES 
('f6c0df85-7cca-4b79-bffa-590c46da882e', '2024-05-24 13:25:47.934759+00', '2024-05-24 13:25:47.934759+00', 'infinity', '2cb6c894-9900-46c0-a290-183046d69bc1', 'bad 1', 100),
('c3622129-5bf2-4b5a-97fa-f49f3211ae8d', '2024-05-24 13:26:12.696822+00', '2024-05-24 13:26:12.696822+00', 'infinity', '2cb6c894-9900-46c0-a290-183046d69bc1', 'bad 2', 100);

INSERT INTO lib_history_table_example.items (item_id, item_name, item_price)
VALUES 
('4acffdaa-b5c5-4e95-ac35-083a9b993bb4', ' view insert 1', 100)

-- ERROR:  item_id 4acffdaa-b5c5-4e95-ac35-083a9b993bb4 already exists
INSERT INTO lib_history_table_example.items (item_id, item_name, item_price)
VALUES 
('4acffdaa-b5c5-4e95-ac35-083a9b993bb4', 'view insert 1', 110)

UPDATE lib_history_table_example.items
SET	item_price = 110
WHERE item_id = '4acffdaa-b5c5-4e95-ac35-083a9b993bb4'

5_test_pgtap.sql

BEGIN;

CREATE EXTENSION IF NOT EXISTS pgtap
SCHEMA public
VERSION "1.3.2";

SELECT plan(5);

SELECT lives_ok(
'INSERT INTO lib_history_table_example.items_history (history_id, history_created_at, history_valid_from, history_valid_until, item_id, item_name, item_price)
VALUES 
(''cd9f8585-7037-4d54-bb10-50200a9b1f38'', ''2024-05-24 13:23:05.931379+00'', ''2024-05-24 13:23:05.931379+00'', ''2024-05-24 13:23:17.65513+00'', ''db2d7521-7f77-4da0-8206-7e626a2c1615'', ''good batch 1'', 100),
(''eda9714e-9248-4020-8c71-d7b6001b7aa3'', ''2024-05-24 13:23:17.65513+00'', ''2024-05-24 13:23:17.65513+00'', ''2024-05-24 13:24:45.136609+00'', ''db2d7521-7f77-4da0-8206-7e626a2c1615'', ''good batch 1'', 110),
(''16cf81ad-fa85-471f-b655-7a555dd7c0ea'', ''2024-05-24 13:24:45.136609+00'', ''2024-05-24 13:24:45.136609+00'', null, ''db2d7521-7f77-4da0-8206-7e626a2c1615'', ''good batch 1'', 100);',
'good item history'	
);

SELECT lives_ok(
'INSERT INTO lib_history_table_example.items_history (history_valid_from, item_id, item_name, item_price)
VALUES 
(clock_timestamp(), ''db2d7521-7f77-4da0-8206-7e626a2c1616'', ''good incremental 1'', 100);',
'single record insert'
);

SELECT lives_ok(
'INSERT INTO lib_history_table_example.items_history (history_valid_from, item_id, item_name, item_price)
VALUES 
(clock_timestamp(), ''db2d7521-7f77-4da0-8206-7e626a2c1616'', ''good incremental 1'', 110);',
'single record update'
);

SELECT pg_sleep(0.001);

SELECT lives_ok(
'INSERT INTO lib_history_table_example.items_history (history_valid_from, item_id, item_name, item_price)
VALUES 
(clock_timestamp(), ''db2d7521-7f77-4da0-8206-7e626a2c1616'', ''good incremental 1'', 100);',
'single record update again'
);

SELECT pg_sleep(0.001);

SELECT throws_ok(
'INSERT INTO lib_history_table_example.items_history (history_id, history_created_at, history_valid_from, history_valid_until, item_id, item_name, item_price)
VALUES 
(''f6c0df85-7cca-4b79-bffa-590c46da882e'', ''2024-05-24 13:25:47.934759+00'', ''2024-05-24 13:25:47.934759+00'', ''infinity'', ''2cb6c894-9900-46c0-a290-183046d69bc1'', ''bad batch 1'', 100),
(''c3622129-5bf2-4b5a-97fa-f49f3211ae8d'', ''2024-05-24 13:26:12.696822+00'', ''2024-05-24 13:26:12.696822+00'', ''infinity'', ''2cb6c894-9900-46c0-a290-183046d69bc1'', ''bad batch 1'', 100);',
'conflicting key value violates exclusion constraint "example_history_id_valid_range_excl"',
'bad item history'
);

SELECT lives_ok(
'INSERT INTO lib_history_table_example.items (item_id, item_name, item_price)
VALUES 
(''4acffdaa-b5c5-4e95-ac35-083a9b993bb4'', '' view insert 1'', 100)',
'view insert');

SELECT pg_sleep(0.001);

SELECT throws_ok(
'INSERT INTO lib_history_table_example.items (item_id, item_name, item_price)
VALUES 
(''4acffdaa-b5c5-4e95-ac35-083a9b993bb4'', ''view insert 1'', 110)',
'item_id 4acffdaa-b5c5-4e95-ac35-083a9b993bb4 already exists',
'view insert to already exists');

SELECT pg_sleep(0.001);

SELECT lives_ok(
'UPDATE lib_history_table_example.items
SET	item_price = 110
WHERE item_id = ''4acffdaa-b5c5-4e95-ac35-083a9b993bb4''',
'view update');

SELECT * FROM finish();

ROLLBACK;

END;

history_valid_from, history_valid_until が nullable だと、 view をトリガ関数を利用して insert, update 可能にしたときに、引数のデフォルト値の制御が困難になる。

と思ったが、どのみちビューにはトリガ関数内でデフォルト値を設定しないといけないので、 null バージョンでも良さそう。

エラーコードを返すように変更

https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html
https://www.postgresql.org/docs/current/errcodes-appendix.html