PostgreSQL Bitemporal Solution: Package Usage Guide

main - readme

This document outlines the two primary approaches for integrating and managing your data with the PostgreSQL bitemporal solution: a detailed manual setup, which offers granular control, and an automated method leveraging the vrsn.admin__bitemporal_entity_register function for simplified and flexible entity registration.

Introduction to Package Usage

The vrsn package provides a robust framework for bitemporal data management in PostgreSQL, allowing you to track both valid time (user_ts_range) and transaction time (db_ts_range). Whether you prefer a hands-on approach or a more automated setup, the package supports your needs by standardizing table structures, naming conventions, and trigger management.

Manual Setup Approach

The manual approach provides full control over the creation of your database objects. This method requires strict adherence to naming conventions and proper trigger setup.

Step 1: Manual Table and View Creation

You are responsible for creating your entity's tables and its corresponding view.

Current Table: This table stores the active, valid records. It must include a bt_info column of type vrsn.bitemporal_record NOT NULL.
- Naming Convention: It is mandatory to name this table using the format [entity_name]_current.
- Example: For an entity named customer, the current table would be customer_current.
History Table: This table stores all historical versions of the records from the current table. It should have an identical structure to the current table, including the bt_info field.
- Naming Convention: It is mandatory to name this table using the format [entity_name]_history.
- Inheritance (Optional): You may choose to create the history table by inheriting from the current table for structural consistency, although this is not strictly required by the bitemporal framework itself.
- Example: For an entity named customer, the history table would be customer_history.
Current View: This view serves as the primary interface for all Data Manipulation Language (DML) operations (INSERT, UPDATE, DELETE). Users should interact only with this view, never directly with the underlying _current or _history tables.
- Naming Convention: It is mandatory to name this view simply [entity_name].
- Example: For an entity named customer, the view would be customer.

Step 2: Trigger Deployment

After creating your tables and view, you must deploy the INSTEAD OF trigger on the current view. This trigger intercepts all DML operations and routes them through the bitemporal logic handled by vrsn.trigger_handler().

The vrsn package provides private functions like vrsn.__bitemporal_entity__get_ddl_view(jsonb) (or its overload vrsn.__bitemporal_entity__get_ddl_view(text, text, text)) and vrsn.__bitemporal_entity__get_ddl_history_table(jsonb) that can help you generate the necessary DDL for tables, views, and particularly the trigger. While these are typically used internally by the automated registration, you can inspect them to understand the required trigger definition.
The trigger on your [entity_name] view will call vrsn.trigger_handler().

Step 3: Index Creation (Manual)

For optimal performance with bitemporal queries, specific GIST indexes on the tstzrange columns are essential. You can manually create these indexes using DDL generated by the vrsn.__bitemporal_entity__get_ddl_tsrange_idx function.

Function: vrsn.__bitemporal_entity__get_ddl_tsrange_idx(p_conf jsonb, p_entity text DEFAULT NULL::text)
This function generates DDL for GIST indexes on the user_ts_range and db_ts_range fields for either the current_table or history_table, specified by p_entity. These indexes significantly speed up temporal queries.

Deductive Management Activation

When performing a manual setup, the bitemporal solution activates its "deductive management" capabilities upon the first DML call to your manually configured view. At this point, the vrsn.trigger_handler() will:

Identify the view and its underlying _current and _history tables based on the mandatory naming conventions.
Internally configure the vrsn.def_entity_behavior_current table with default settings for your new entity.
Begin applying bitemporal logic to all subsequent DML operations.

Automated Setup with `vrsn.admin__bitemporal_entity_register`

For a more streamlined and flexible setup, the vrsn.admin__bitemporal_entity_register function provides a powerful automated registration mechanism. This is the recommended approach for integrating new entities into the bitemporal framework.

SELECT vrsn.admin__bitemporal_entity_register(
    p_conf := '{
        "current_view": {"schema_name": "your_app_schema", "table_name": "your_entity_view_name"},
        -- Or use "current_table" to derive the view name (e.g., "customer_current"):
        -- "current_table": {"schema_name": "your_app_schema", "table_name": "your_entity_table_current"},
        "historice_entity": "always",
        "enable_history_attributes": true,
        "main_fields_list": "field1,field2",
        "cached_fields_list": "cached_attr_column",
        "mitigate_conflicts": true,
        "ignore_unchanged_values": true,
        "enable_attribute_to_fields_replacement": false
    }'::jsonb,
    p_execute := TRUE -- Set to TRUE to execute the DDL immediately
);

Configuration Parameter Example (`vrsn.admin__get_bitemporal_entity_conf_param`)

To understand the expected p_conf jsonb structure for vrsn.admin__bitemporal_entity_register, you can query the helper function vrsn.admin__get_bitemporal_entity_conf_param():

SELECT * FROM vrsn.admin__get_bitemporal_entity_conf_param();

This function returns a table with two columns: input_example (a jsonb representing the default configuration structure) and json_schema (the JSON schema for validation). The input_example will show a structure similar to this:

{
  "entity": {
    "table_name": null,
    "schema_name": null
  },
  "current_view": {
    "table_name": null,
    "schema_name": null
  },
  "current_table": {
    "table_name": null,
    "schema_name": null
  },
  "history_table": {
    "table_name": null,
    "schema_name": null
  },
  "attribute_entity": {
    "table_name": null,
    "schema_name": null
  },
  "historice_entity": "always",
  "main_fields_list": null,
  "cached_fields_list": null,
  "mitigate_conflicts": true,
  "ignore_unchanged_values": true,
  "enable_history_attributes": false,
  "enable_attribute_to_fields_replacement": false
}

When calling vrsn.admin__bitemporal_entity_register, you should provide a jsonb that merges with this default structure, specifying only the fields you wish to override or populate.

Flexibility in Naming and Schemas

Unlike the manual approach, vrsn.admin__bitemporal_entity_register offers significant flexibility:

Custom Naming: You can provide custom names for your current table, history table, and attribute tables. The function will use these explicit names.
Schema Separation: It supports placing tables and views in different schemas, allowing for finer-grained access control and organization of your database objects. You would specify the schema_name within each current_view, current_table, history_table, and attribute_entity JSON sub-object.
Derivation (Default Behavior): If specific table names (e.g., history_table.table_name) are not provided in the p_conf JSONB, the function will automatically derive them based on the [entity_name] derived from current_view or current_table, using the standard suffixes (_history, _current, _attribute).

Advanced Features: Attribute Management

vrsn.admin__bitemporal_entity_register provides direct control over advanced bitemporal features:

Attribute Replacement (enable_attribute_to_fields_replacement): Set this flag to true in p_conf to enable the dynamic overwriting of scalar entity fields using values from vrsn.cached_attribute columns. This allows for a more document-like approach to updating data.
Attribute Archiving with Lineage (enable_history_attributes): Set this flag to true in p_conf to activate attribute-level historicization. This includes the automatic creation of dedicated tables for attribute history and the utilization of vrsn.attribute_lineage to track individual attribute identities and their evolution over time. This is ideal for entities with frequently changing or complex sets of attributes.

Summary of Usage Recommendations

Feature / Aspect	Manual Setup	Automated Setup (`vrsn.admin__bitemporal_entity_register`)	Recommendation
Object Creation	Manual DDL statements	Automated DDL generation and execution	Automated for speed and consistency.
Naming Conventions	Strictly enforced (`_current`, `_history`, etc.)	Flexible (derives if not specified, but conventions are advised).	Automated for flexibility; respect derived names or specify explicitly.
Schema Flexibility	Possible with careful manual DDL	Full support for different schemas	Automated for multi-schema deployments.
Trigger Management	Manual trigger creation	Automated trigger deployment	Automated to prevent errors.
Index Creation	Manual index DDL	Automated index deployment	Automated for performance and correctness.
Attribute Management	Not directly supported by manual setup; requires custom logic.	Integrated (`enable_history_attributes`, `enable_attribute_to_fields_replacement`).	Automated for complex attribute scenarios.
Setup Complexity	High (DDL, triggers, conventions)	Low (single `jsonb` function call)	Automated for ease of use and reduced error surface.
Best For	Very specific, non-standard needs; deep understanding of internals.	Most use cases, especially for evolving data models and attribute management.	`vrsn.admin__bitemporal_entity_register` is the recommended path for new entities.

Example of full script returned/executed by register function

-- indice gist composto (user_ts_range, db_ts_range)

create index if not exists test_table_current_user_db_tsr_ix on vrsn.test_table_current using gist (((bt_info).user_ts_range), ((bt_info).db_ts_range));

-- indice gist solo su db_ts_range

create index if not exists test_table_current_db_tsr_ix on vrsn.test_table_current using gist (((bt_info).db_ts_range));

-- Create history table

CREATE TABLE if not exists vrsn.test_table_history (
	CONSTRAINT test_table_history_pk primary key (id, bt_info)
) INHERITS (vrsn.test_table_current);


-- indice gist composto (user_ts_range, db_ts_range)

create index if not exists test_table_history_user_db_tsr_ix on vrsn.test_table_history using gist (((bt_info).user_ts_range), ((bt_info).db_ts_range));

-- indice gist solo su db_ts_range

create index if not exists test_table_history_db_tsr_ix on vrsn.test_table_history using gist (((bt_info).db_ts_range));





-- Create view	

--drop view vrsn.test_table;

create or replace view vrsn.test_table as
select s.id
	,	s.sometext
	,	s.main_ts
	,	s.somevalue
	,	s.many_fields
	,	false::boolean AS is_closed
	,	NULL::text AS modify_user_id
	,	NULL::timestamp with time zone AS modify_ts
	,	NULL::jsonb AS action_hints
from only vrsn.test_table_current as s;
				
create or replace trigger trg_test_table
    instead of insert or delete or update 
    on vrsn.test_table
    for each row
    execute function vrsn.trigger_handler();

-- Register data into vrsn.def_entity_behavior
		
INSERT INTO vrsn.def_entity_behavior(
		entity_full_name.schema_name
	,	entity_full_name.table_name
	,	current_view_full_name.schema_name
	,	current_view_full_name.table_name
	,	current_table_full_name.schema_name
	,	current_table_full_name.table_name
	,	history_table_full_name.schema_name
	,	history_table_full_name.table_name
	,	attribute_entity_full_name.schema_name
	,	attribute_entity_full_name.table_name

	,	historice_entity, enable_history_attributes
	,	main_fields_list, cached_fields_list
	,	enable_attribute_to_fields_replacement, modify_user_id
	,	action_hints)
VALUES(	'vrsn', 'test_table'
	,	'vrsn', 'test_table'
	,	'vrsn', 'test_table_current'
	,	'vrsn', 'test_table_history'
	,	'vrsn', 'test_table_attribute'
	,	'always', true
	,	NULL, NULL
	,	false, 'process:vrsn.register'
	,	'{"onDupKey":"update"}'::jsonb);


-- Create Attribute Table
create table if not exists vrsn.test_table_attribute_current (
	bt_info        vrsn.bitemporal_record NOT NULL
 ,	id integer not null
		,	attribute_id bigint not null
		,	idx text default ''::text not null
		,	attribute_value text
 ,   constraint test_table_attribute_current_pk primary key (id, attribute_id, idx)
);
	


-- indice gist composto (user_ts_range, db_ts_range)

create index if not exists test_table_attribute_current_user_db_tsr_ix on vrsn.test_table_attribute_current using gist (((bt_info).user_ts_range), ((bt_info).db_ts_range));

-- indice gist solo su db_ts_range

create index if not exists test_table_attribute_current_db_tsr_ix on vrsn.test_table_attribute_current using gist (((bt_info).db_ts_range));




-- Create history table

CREATE TABLE if not exists vrsn.test_table_attribute_history (
	CONSTRAINT test_table_attribute_history_pk primary key (id, attribute_id, idx, bt_info)
) INHERITS (vrsn.test_table_attribute_current);


-- indice gist composto (user_ts_range, db_ts_range)

create index if not exists test_table_attribute_history_user_db_tsr_ix on vrsn.test_table_attribute_history using gist (((bt_info).user_ts_range), ((bt_info).db_ts_range));

-- indice gist solo su db_ts_range

create index if not exists test_table_attribute_history_db_tsr_ix on vrsn.test_table_attribute_history using gist (((bt_info).db_ts_range));


-- Create view	

--drop view vrsn.test_table_attribute;

create or replace view vrsn.test_table_attribute as
select s.id
	,	s.attribute_id
	,	s.idx
	,	s.attribute_value
	,	false::boolean AS is_closed
	,	NULL::text AS modify_user_id
	,	NULL::timestamp with time zone AS modify_ts
	,	NULL::jsonb AS action_hints
from only vrsn.test_table_attribute_current as s;
				
create or replace trigger trg_test_table_attribute
    instead of insert or delete or update 
    on vrsn.test_table_attribute
    for each row
    execute function vrsn.trigger_handler();

-- Register data into vrsn.def_entity_behavior
		
INSERT INTO vrsn.def_entity_behavior(
		entity_full_name.schema_name
	,	entity_full_name.table_name
	,	current_view_full_name.schema_name
	,	current_view_full_name.table_name
	,	current_table_full_name.schema_name
	,	current_table_full_name.table_name
	,	history_table_full_name.schema_name
	,	history_table_full_name.table_name
	,	attribute_entity_full_name.schema_name
	,	attribute_entity_full_name.table_name

	,	historice_entity, enable_history_attributes
	,	main_fields_list, cached_fields_list
	,	enable_attribute_to_fields_replacement, modify_user_id
	,	action_hints)
VALUES(	'vrsn', 'test_table_attribute'
	,	'vrsn', 'test_table_attribute'
	,	'vrsn', 'test_table_attribute_current'
	,	'vrsn', 'test_table_attribute_history'
	,	NULL, NULL
	,	'always', false
	,	NULL, NULL
	,	false, 'process:vrsn.register'
	,	'{"onDupKey":"update"}'::jsonb);

main - readme

FilesExpand file tree

package_usage.md

Latest commit

History