PostgreSQL 16 上での履歴テーブルの実装(not null バージョン)

0_prepare.sql

-- Extension: btree_gist

-- DROP EXTENSION btree_gist;

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

-----------------------------------------------------------------------------------

-- 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()` を使用する場合は `now()` の呼び出し毎に時刻を取得してしまうことを避けるため、トランザクション中で使用する事。
可能であれば `''-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,
	history_valid_until lib_history_table_example.record_timestamp)
    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 はドメイン型で定義する。';

1_table.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 (history_valid_from, item_id, item_name, item_price)
VALUES 
(clock_timestamp(), ''4acffdaa-b5c5-4e95-ac35-083a9b993bb3'', '' view insert 1'', 100)',
'view insert');

SELECT pg_sleep(0.001);

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

SELECT pg_sleep(0.001);

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

SELECT * FROM finish();

ROLLBACK;

END;

2_test_cases.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', 'infinity', '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);

-- fail case: ERROR:  conflicting key value violates exclusion constraint "example_history_id_valid_range_excl"
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', '2024-05-25 13:26:12.696822+00', '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-083a9b993bb3', ' view insert 1', 100)
RETURNING *;

INSERT INTO lib_history_table_example.items (item_id, item_name, item_price)
VALUES 
('4acffdaa-b5c5-4e95-ac35-083a9b993bb3', 'view insert 1', 110)
RETURNING *;

INSERT INTO lib_history_table_example.items (item_id, item_name, item_price)
VALUES 
('4acffdaa-b5c5-4e95-ac35-083a9b993bb3', 'view insert 1', 120)
RETURNING *;